473,586 Members | 2,490 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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

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_U SE_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_ex ec($connect,$qu **ery); */

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

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

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

odbc_close($con nect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHN UM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHN UM</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
13 2724
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_U SE_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_ex ec($connect,$qu **ery); */

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

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

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

odbc_close($con nect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHN UM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHN UM</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*******@attgl obal.net
=============== ===
Jul 17 '05 #2
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_U SE_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_ex ec($connect,$qu **ery); */

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

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

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

odbc_close($con nect);
?> </body> </html>

PROGRAM OUTPUT

<html>
<body>
<table border=1 ><tr><th>BATCHN UM</th></tr>
<tr><td>1</td></tr>
</table>
<table border=1 ><tr><th>BATCHN UM</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*******@attgl obal.net
=============== ===
Jul 17 '05 #3
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
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
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*******@attgl obal.net
=============== ===
Jul 17 '05 #6
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*******@attgl obal.net
=============== ===
Jul 17 '05 #7
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
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
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*******@attgl obal.net
=============== ===
Jul 17 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1372
by: chotiwallah | last post by:
i'm trying to count pageviews on my website. this is part of the code included in each page: $query = mysql_query("SELECT * FROM pageviews WHERE datei = '$datei'"); if(mysql_num_rows($query) != 0) { $query = mysql_query("UPDATE pageviews SET hits = hits + 1 WHERE
9
2195
by: Wescotte | last post by:
Here is a small sample program I wrote in PHP 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...
6
4542
by: Ryan | last post by:
I came across a situation that I've been unable to explain and was hoping somebody had an answer: I had written an update query which was taking about 8 seconds to run and considered it too slow. I copied the SQL statement from the query and tried executing it from code which then ran in 1 second. To make sure that I didn't miss anything,...
2
1411
by: Buddy Ackerman | last post by:
I have a web app that I have setup on numerous web servers. I've set one up for a new client at their hosting facility and cannot get it to connect to their database. I get a "SQL Server does not exist or access denied." error. Well, the strangeness is that I have a SQL Query tool installed on this server and can connect to the database fine...
4
3851
by: Nate Murray | last post by:
Hey all, I'm having a strange PHP (4.3.10) problem that seems to have something to do with javascript. This is a bit complex, so hold on to your hats. My issue is that a single function is wrongly being called twice. The weird part is that no "print" functions are being repeated, but any "lower" functions, such as fwrite or...
11
2578
by: Martin Joergensen | last post by:
Hi, I've encountered a really, *really*, REALLY strange error :-) I have a for-loop and after 8 runs I get strange results...... I mean: A really strange result.... I'm calculating temperatures. T = 20 degrees at all times.... The 2D T-array looks like this:
5
2274
by: BillCo | last post by:
I'm having a problem with a union query, two simple queries joined with a union statement. It's created in code based on parameters. Users were noticing some inconsistant data and when I analysed the query produced and opened it from a MS Query it started giving strange results. The first query when run alone returns 22 records, some of...
20
1658
by: SpreadTooThin | last post by:
I have a list and I need to do a custom sort on it... for example: a = #Although not necessarily in order def cmp(i,j): #to be defined in this thread. a.sort(cmp) print a
2
1816
by: Paul Furman | last post by:
I don't know, maybe this isn't strange but someone else set up the shopping card coding I'm working with, the way it works is to get the time() in seconds like 1172693735 and that's the shopper_ID number and the shopping_cart_last_modified number is generated the same way once they check out... then an invoice number is generated by...
1
3449
by: CAM123 | last post by:
I have added: <br><xsl:value-of select="Line" /></br> to my XSLT stylesheet to get a line per repeating block. When I view the output as XML it looks perfect - one line per block. However when I output the file to a text file, all the data is wrapping and at the end of each block I am getting the text part of the header included but not...
0
7915
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
0
7841
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
8204
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8339
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7965
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
8220
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
6617
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
1452
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
1184
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.