By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,851 Members | 1,022 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,851 IT Pros & Developers. It's quick & easy.

Strange inconsistences when repeating a query more than twice in a row

P: n/a
Here is a small sample program I wrote in PHP (running off Apache
1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data
base is using DB2 V5R3M0. The client is WinXP machine using the iSeries
Client Access Driver ver 10.00.04.00 to connect to the database.

The problem is that executing the exact same SQL select statement more
than twice int a row stops produces results. The first two instances
will always produce the correct results but after that it will simply
return with no results found. I've also wrote the exact same test case
with other various ODBC APIs with the same results.

I've examining the ODBC driver trace log and I am unable to find any
noticable differences between a SQL statement producing the correct
results and one that produces a no results found message. I've tried
three different ODBC driver versions and have the same results.

NOTE: This doesn't occur for EVERY select statement but it does occur
for different files with various WHERE clauses but I've been unable to
determine any relationship between working and non working repeating
select statements.

PHP SOURCE CODE:

<html> <body> <?php
$connect = odbc_connect ("AS400", "","",SQL_CUR_USE_ODBC);
if ($connect == false)
printf("Problem connecting to the database");
/* Already created
$query = "CREATE TABLE EJWLIB.BHEADW ( ";
$query .="BATCHNUM NUMERIC(5) NOT NULL, ";
$query .="COMPID CHAR(2) NOT NULL, ";
$query .="AMOUNT NUMERIC(9,2) NOT NULL, ";
$query .="BDATE DATE NOT NULL, ";
$query .="EDIREF CHAR(15), ";
$query .="FLAG CHAR(1) NOT NULL)";
$result=odbc_exec($connect,$qu**ery); */

$query = "DELETE FROM EJWLIB.BHEADW";
$result=odbc_exec($connect,$qu**ery);

$query = "INSERT INTO EJWLIB.BHEADW VALUES('1', 'FX', '361.94',
'04/22/2005', '305855545', 'O')";
$result=odbc_exec($connect,$qu**ery);

$i=0; $query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT =
'361.94' AND COMPID = 'FX'";
while ($i < 5) {
$result=odbc_exec($connect,$qu**ery);
if (!$result)
echo("failed <br>");
$rows=odbc_result_all($result,**"border=1");
$i++;
}

odbc_close($connect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<h2>No rows found</h2>
<h2>No rows found</h2>
<h2>No rows found</h2>
</body>
</html>

Jul 17 '05 #1
Share this Question
Share on Google+
13 Replies


P: n/a
Wescotte wrote:
Here is a small sample program I wrote in PHP (running off Apache
1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data
base is using DB2 V5R3M0. The client is WinXP machine using the iSeries
Client Access Driver ver 10.00.04.00 to connect to the database.

The problem is that executing the exact same SQL select statement more
than twice int a row stops produces results. The first two instances
will always produce the correct results but after that it will simply
return with no results found. I've also wrote the exact same test case
with other various ODBC APIs with the same results.

I've examining the ODBC driver trace log and I am unable to find any
noticable differences between a SQL statement producing the correct
results and one that produces a no results found message. I've tried
three different ODBC driver versions and have the same results.

NOTE: This doesn't occur for EVERY select statement but it does occur
for different files with various WHERE clauses but I've been unable to
determine any relationship between working and non working repeating
select statements.

PHP SOURCE CODE:

<html> <body> <?php
$connect = odbc_connect ("AS400", "","",SQL_CUR_USE_ODBC);
if ($connect == false)
printf("Problem connecting to the database");
/* Already created
$query = "CREATE TABLE EJWLIB.BHEADW ( ";
$query .="BATCHNUM NUMERIC(5) NOT NULL, ";
$query .="COMPID CHAR(2) NOT NULL, ";
$query .="AMOUNT NUMERIC(9,2) NOT NULL, ";
$query .="BDATE DATE NOT NULL, ";
$query .="EDIREF CHAR(15), ";
$query .="FLAG CHAR(1) NOT NULL)";
$result=odbc_exec($connect,$qu**ery); */

$query = "DELETE FROM EJWLIB.BHEADW";
$result=odbc_exec($connect,$qu**ery);

$query = "INSERT INTO EJWLIB.BHEADW VALUES('1', 'FX', '361.94',
'04/22/2005', '305855545', 'O')";
$result=odbc_exec($connect,$qu**ery);

$i=0; $query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT =
'361.94' AND COMPID = 'FX'";
while ($i < 5) {
$result=odbc_exec($connect,$qu**ery);
if (!$result)
echo("failed <br>");
$rows=odbc_result_all($result,**"border=1");
$i++;
}

odbc_close($connect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<h2>No rows found</h2>
<h2>No rows found</h2>
<h2>No rows found</h2>
</body>
</html>

Your problem is:

WHERE AMOUNT = '361.94'

This is a floating point value, and cannot be rendered exactly in binary
(similar to 1/3 = .3333333 ad nauseum). The value in the database is
probably something like 361.93999999987 or such. And even though MySQL
is doing both conversions (when you insert and when you compare), the
results are probably not exactly the same, so the WHERE clause fails.

You can never reliably compare floating point numbers like this. If
you're using dollars and cents, your best bet is to store as cents (i.e.
36194) in an integer type and divide by 100 after you get the value from
the database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #2

P: n/a
Wescotte wrote:
Here is a small sample program I wrote in PHP (running off Apache
1.3.31 w/ PHP 5.0.1) to help illustrates problem I'm having. The data
base is using DB2 V5R3M0. The client is WinXP machine using the iSeries
Client Access Driver ver 10.00.04.00 to connect to the database.

The problem is that executing the exact same SQL select statement more
than twice int a row stops produces results. The first two instances
will always produce the correct results but after that it will simply
return with no results found. I've also wrote the exact same test case
with other various ODBC APIs with the same results.

I've examining the ODBC driver trace log and I am unable to find any
noticable differences between a SQL statement producing the correct
results and one that produces a no results found message. I've tried
three different ODBC driver versions and have the same results.

NOTE: This doesn't occur for EVERY select statement but it does occur
for different files with various WHERE clauses but I've been unable to
determine any relationship between working and non working repeating
select statements.

PHP SOURCE CODE:

<html> <body> <?php
$connect = odbc_connect ("AS400", "","",SQL_CUR_USE_ODBC);
if ($connect == false)
printf("Problem connecting to the database");
/* Already created
$query = "CREATE TABLE EJWLIB.BHEADW ( ";
$query .="BATCHNUM NUMERIC(5) NOT NULL, ";
$query .="COMPID CHAR(2) NOT NULL, ";
$query .="AMOUNT NUMERIC(9,2) NOT NULL, ";
$query .="BDATE DATE NOT NULL, ";
$query .="EDIREF CHAR(15), ";
$query .="FLAG CHAR(1) NOT NULL)";
$result=odbc_exec($connect,$qu**ery); */

$query = "DELETE FROM EJWLIB.BHEADW";
$result=odbc_exec($connect,$qu**ery);

$query = "INSERT INTO EJWLIB.BHEADW VALUES('1', 'FX', '361.94',
'04/22/2005', '305855545', 'O')";
$result=odbc_exec($connect,$qu**ery);

$i=0; $query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT =
'361.94' AND COMPID = 'FX'";
while ($i < 5) {
$result=odbc_exec($connect,$qu**ery);
if (!$result)
echo("failed <br>");
$rows=odbc_result_all($result,**"border=1");
$i++;
}

odbc_close($connect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHNUM</th></tr>
<tr><td>1</td></tr>
</table>
<h2>No rows found</h2>
<h2>No rows found</h2>
<h2>No rows found</h2>
</body>
</html>

Your problem is:

WHERE AMOUNT = '361.94'

This is a floating point value, and cannot be rendered exactly in binary
(similar to 1/3 = .3333333 ad nauseum). The value in the database is
probably something like 361.93999999987 or such. And even though MySQL
is doing both conversions (when you insert and when you compare), the
results are probably not exactly the same, so the WHERE clause fails.

You can never reliably compare floating point numbers like this. If
you're using dollars and cents, your best bet is to store as cents (i.e.
36194) in an integer type and divide by 100 after you get the value from
the database.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #3

P: n/a
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ. If I comment out the line

$query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND
COMPID = 'FX'";

my program will loop inserting 5 times. It correctly inserts 361.94
into the AMOUNT column every time. I assume whatever is doing the
conversion from my text SQL statement '361.94' into a double (or
whatever data type is used by DB2 for NUMERIC(9,2)) is being called for
both select AND insert statements.

I'm new to ODBC and SQL but I assume PHP simply passes the SQL
statement the ODBC driver and any conversions would take place at the
driver level. Since DB2 has support for real numbers of various
precision I fail to see how your explination is correct.

Eric

Jul 17 '05 #4

P: n/a
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ. If I comment out the line

$query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND
COMPID = 'FX'";

my program will loop inserting 5 times. It correctly inserts 361.94
into the AMOUNT column every time. I assume whatever is doing the
conversion from my text SQL statement '361.94' into a double (or
whatever data type is used by DB2 for NUMERIC(9,2)) is being called for
both select AND insert statements.

I'm new to ODBC and SQL but I assume PHP simply passes the SQL
statement the ODBC driver and any conversions would take place at the
driver level. Since DB2 has support for real numbers of various
precision I fail to see how your explination is correct.

Eric

Jul 17 '05 #5

P: n/a
Wescotte wrote:
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ. If I comment out the line

$query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND
COMPID = 'FX'";

my program will loop inserting 5 times. It correctly inserts 361.94
into the AMOUNT column every time. I assume whatever is doing the
conversion from my text SQL statement '361.94' into a double (or
whatever data type is used by DB2 for NUMERIC(9,2)) is being called for
both select AND insert statements.

I'm new to ODBC and SQL but I assume PHP simply passes the SQL
statement the ODBC driver and any conversions would take place at the
driver level. Since DB2 has support for real numbers of various
precision I fail to see how your explination is correct.

Eric


Well, as I said - results are unpredictable. If you're using
NUMERIC(9,2) as the datatype, DB2 will use packed decimal for the
representation. The problem is that PHP has no internal representation
for packed decimal (neither does C or a lot of other languages).
Additionally, Intel (and compatible) chipsets do not have an equivalent
to packed decimal - it's strictly an IBM mainframe datatype.

As a result, I don't know what happens internally with the ODBC driver
from PHP. But I do know that the C interface uses a float (or double)
on the PC side to represent these numbers, and DB2 has to convert the
value to packed decimal. And it doesn't perform the conversion
accurately except in cases like .50 or .25, which can be represented
exactly.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #6

P: n/a
Wescotte wrote:
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ. If I comment out the line

$query="SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND
COMPID = 'FX'";

my program will loop inserting 5 times. It correctly inserts 361.94
into the AMOUNT column every time. I assume whatever is doing the
conversion from my text SQL statement '361.94' into a double (or
whatever data type is used by DB2 for NUMERIC(9,2)) is being called for
both select AND insert statements.

I'm new to ODBC and SQL but I assume PHP simply passes the SQL
statement the ODBC driver and any conversions would take place at the
driver level. Since DB2 has support for real numbers of various
precision I fail to see how your explination is correct.

Eric


Well, as I said - results are unpredictable. If you're using
NUMERIC(9,2) as the datatype, DB2 will use packed decimal for the
representation. The problem is that PHP has no internal representation
for packed decimal (neither does C or a lot of other languages).
Additionally, Intel (and compatible) chipsets do not have an equivalent
to packed decimal - it's strictly an IBM mainframe datatype.

As a result, I don't know what happens internally with the ODBC driver
from PHP. But I do know that the C interface uses a float (or double)
on the PC side to represent these numbers, and DB2 has to convert the
value to packed decimal. And it doesn't perform the conversion
accurately except in cases like .50 or .25, which can be represented
exactly.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #7

P: n/a
So why can't it directly send the text of the SQL to the ODBC driver
which is passed to the DB2 SQL server and parsed there? That would
ensure it's correct represenation? I dunno but I think that's too big
of an issue to force people to do a work around like you stated above.
I'll do some more research but I have my doubts this is the solution.

Jul 17 '05 #8

P: n/a
So why can't it directly send the text of the SQL to the ODBC driver
which is passed to the DB2 SQL server and parsed there? That would
ensure it's correct represenation? I dunno but I think that's too big
of an issue to force people to do a work around like you stated above.
I'll do some more research but I have my doubts this is the solution.

Jul 17 '05 #9

P: n/a
Wescotte wrote:
So why can't it directly send the text of the SQL to the ODBC driver
which is passed to the DB2 SQL server and parsed there? That would
ensure it's correct represenation? I dunno but I think that's too big
of an issue to force people to do a work around like you stated above.
I'll do some more research but I have my doubts this is the solution.


Because text is not numeric data, that's why. The C interface to DB2
doesn't send text, either - and suffers the same problem.

Check the DB2 newsgroups - they'll tell you the same thing.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #10

P: n/a
Wescotte wrote:
So why can't it directly send the text of the SQL to the ODBC driver
which is passed to the DB2 SQL server and parsed there? That would
ensure it's correct represenation? I dunno but I think that's too big
of an issue to force people to do a work around like you stated above.
I'll do some more research but I have my doubts this is the solution.


Because text is not numeric data, that's why. The C interface to DB2
doesn't send text, either - and suffers the same problem.

Check the DB2 newsgroups - they'll tell you the same thing.

--
==================
Remove the "x" from my email address
Jerry Stuckle
JDS Computer Training Corp.
js*******@attglobal.net
==================
Jul 17 '05 #11

P: n/a
Wescotte wrote:
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ.


The floating point control word of the CPU may be changed by code in one
part of your database server, then never reset. This means that the
rounding mode may change unpredictably, causing different results on
different runs. This is a bug, IMO.

Cheers,
Nicholas Sherlock
Jul 17 '05 #12

P: n/a
Wescotte wrote:
So why does it correct find results the first two times? I understand
the concept of being unable to exactly represent a real number in
binary do to limited bits but I fail to see how after two attempts
conversion would suddenly differ.


The floating point control word of the CPU may be changed by code in one
part of your database server, then never reset. This means that the
rounding mode may change unpredictably, causing different results on
different runs. This is a bug, IMO.

Cheers,
Nicholas Sherlock
Jul 17 '05 #13

P: n/a
>The floating point control word of the CPU may be changed by code in one
part of your database server, then never reset. This means that the
rounding mode may change unpredictably, causing different results on
different runs. This is a bug, IMO.


Just wondering but do you know of any way I can prove that is in fact
the case?

Jul 17 '05 #14

This discussion thread is closed

Replies have been disabled for this discussion.