ESQL bug documentation
I have run into a bug in the handling of INSERT from ESQL which is
demonstrated by the program that follows. Informix technical support
is aware of this and has asked me for more documentation to help locate
the bug. I would be grateful to those of you on the net if you could
run the following program and let me know what results you get given the
hardware and software environment at your sites.
I have been running the code using ESQL 4.00.UD2, Standard engine 4.00.UD2,
on HP-UX 7.05 using both standard and ANSI C to compile the program. My
results follow:
Results of 3 sequential runs:
> hpsdid 291: float
> Creating test table.
> Indexing test table.
> Adding initial data to test table.
> Searching for data to update. . .
> 1) Key: <1> SQLCODE: 0 SFloat: -0.NaN(FF (-1) Float: -0.NaN(FF (-1)
> 2) Key: <2> SQLCODE: 0 SFloat: -0.NaN(FF (-1) Float: -0.NaN(FF (-1)
> 3) Key: <3> SQLCODE: 0 SFloat: -0.NaN(FF (-1) Float: -0.NaN(FF (-1)
> Error updating row in test table: -408
> Invalid message type received from the sqlexec process.
>
> hpsdid 292: float
> Table already exists, continuing...
> Searching for data to update. . .
> 1) Key: <1> SQLCODE: 0 SFloat: 1.2 (0) Float: -10.4 (0)
> 2) Key: <2> SQLCODE: 0 SFloat: 0 (0) Float: 2.22507e-308 (0)
> 3) Key: <3> SQLCODE: 0 SFloat: -0.NaN(FF (-1) Float: -0.NaN(FF (-1)
> Error updating row in test table: -408
> Invalid message type received from the sqlexec process.
>
> hpsdid 293: !!
> float
> Table already exists, continuing...
> Searching for data to update. . .
> 1) Key: <1> SQLCODE: 0 SFloat: 1.2 (0) Float: -10.4 (0)
> 2) Key: <2> SQLCODE: 0 SFloat: 0 (0) Float: 2.22507e-308 (0)
> 3) Key: <3> SQLCODE: 0 SFloat: -0.NaN(FF (-1) Float: -0.NaN(FF (-1)
> Error updating row in test table: -408
> Invalid message type received from the sqlexec process.
Interestingly, if I try to look at the table from isql using "select * from
test_tbl order by keyval" I get yet another error message:
1201: Number is too small for a DECIMAL data type after it displays the first row.
Also, if I use FLT_MAX and FLT_MIN in the smallfloat field, error 408 does not
occur, but the 1201 (above) does.
I hope that this helps you. I will look forward to your response(s).
Kurt Andersen
--
kurt@hpsdid.sdd.hp.com
619/592-4373 (voice); 619/592-8191 (FAX)
16399 W. Bernardo Dr. MS 61L253/San Diego, CA 92127
-----Standard disclaimer stuff applies for all of the standard reasons-----
------------------------Cut here for program----------------------------
/****************************************************************************
FLOAT.EC includes the esql code for demonstrating error 408 when
updating with an out-of-range float value.
Prerequisites:
This program assumes that a database by the name of "calvin"
exists in the current dbpath on a remote machine. Use of a
database on the current machine will return a "pipe broken"
result.
I am using the standard engine on HP-UX 7.05, standard (non-ANSI) C
compilation and I-NET to the database machine.
Table structure (will be created if not already in place):
keyval int not null,
smfl smallfloat,
flval float;
Kurt Andersen
kurt@hpsdid.sdd.hp.com
619/592-4373
*****************************************************************************/
#include <stdio.h>
#include <math.h>
#define FLT_MAX 3.40282347e+38
#define FLT_MIN 1.17549435e-38
#define DBL_MAX 1.7976931348623157e+308
#define DBL_MIN 2.2250738585072014e-308
$include sqlca;
#define _ck_stat(a) if (SQLCODE < 0) {rpt_error(SQLCODE, (a));return(-1);} else
#ifdef ANSI
void rpt_error (short err_num, char *explanation);
int set_up_table(void), update_table(void);
#else
rpt_error();
int set_up_table(), update_table();
#endif
int main()
{
$database calvin;
set_up_table();
update_table();
exit(0);
}
#ifdef ANSI
void rpt_error (short err_num, char *explanation)
#else
rpt_error (err_num,explanation)
int err_num;
char *explanation;
#endif
{
char msg[132];
int status;
fprintf (stdout, "%s: %d\\r\\n", explanation, err_num);
status = rgetmsg (err_num, msg, sizeof(msg));
if (!status) fprintf (stdout, " %s\\r\\n", msg);
}
int set_up_table()
{
$short nrows;
$select count(*) into $nrows from systables
where tabname = "test_tbl";
if (!nrows) {
printf ("Creating test table.\\n");
$create table test_tbl (
keyval int not null,
smfl smallfloat,
flval float);
_ck_stat("Error creating table") ;
printf ("Indexing test table.\\n");
$create unique index tst_inx1 on test_tbl (keyval);
_ck_stat("Error creating index") ;
printf ("Adding initial data to test table.\\n");
$insert into test_tbl (keyval) values (1);
_ck_stat("Error inserting row 1") ;
$insert into test_tbl (keyval) values (2);
_ck_stat("Error inserting row 2") ;
$insert into test_tbl (keyval) values (3);
_ck_stat("Error inserting row 3") ;
}
else printf ("Table already exists, continuing...\\n");
return 1;
}
int update_table()
{
int nrows, prows=0, i, j, prow_cnt;
$double fltval, sfloat;
$short keyvl, ism, ifl;
/* status message */
fprintf (stdout, "Searching for data to update. . .\\n");
/* create query string */
$declare p_cursor cursor for
select keyval, smfl, flval from test_tbl
for update of smfl, flval; _ck_stat("Error declaring selection cursor") ;
$open p_cursor;
_ck_stat("Error opening selection cursor") ;
for (prows=0;;) {
$fetch p_cursor into $keyvl, $sfloat:ism, $fltval:ifl;
fprintf (stdout, "%2d) Key: <%d>\\tSQLCODE: %d\\tSFloat: %8g (%d)\\tFloat: %8g (%d)\\n",
prows+1, keyvl, SQLCODE, sfloat, ism, fltval, ifl);
if (SQLCODE < 0) {
_ck_stat("Error fetching data") ;
if (SQLCODE == -400) exit(2);
continue;
}
else if (SQLCODE == SQLNOTFOUND) break;
if (!prows) {
sfloat = 1.2;
fltval = -10.4;
}
else {
sfloat = (keyvl%2)? DBL_MAX:DBL_MIN; /* FLT_MAX:FLT_MIN; */
fltval = (keyvl%2)? DBL_MAX:DBL_MIN;
}
$update test_tbl set (smfl, flval) = ($sfloat, $fltval)
where current of p_cursor;
_ck_stat("Error updating row in test table");
if (! ((++prows) % 100)) fprintf (stdout, "%d rows updated.\\n", prows);
}
$close p_cursor;
_ck_stat("Error closing cursor") ;
fprintf