By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,756 Members | 1,745 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,756 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 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>

Nov 12 '05 #1
Share this Question
Share on Google+
9 Replies


P: n/a
Unforteuneltly, there are not too many iSeries DB2 people on this forum. You
might want to also post on:

comp.sys.ibm.as400.misc
comp.sys.ibm.as400
ibm.software.db2.os400

also, please post in text, not rtf or html.
Nov 12 '05 #2

P: n/a
My first thought .. just for laughs and giggles ...

What happens if you add

for(j=0; j<100000;j++){}

right after "$++;" ?

The other thing I notice is that you aren't capturing exception
information - so that if the DB was telling you there was a problem
you won't know what it was.

PS to Mark whose help is often not that helpful ... The post wasn't
in HTML. (Read the code.)

Nov 12 '05 #3

P: n/a
>>My first thought .. just for laughs and giggles ...
What happens if you add
for(j=0; j<100000;j++){}


I've already tried adding a signifcant delay between each call (up to 5
seconds) and the results are the same.

I'm a sorta new to PHP but it seems it auto displays errors as they
occur in the output. I've updated the line

if (!$result) echo odbc_errormsg($connect);

but $result is never false so I'm not sure as to what functions I
should use to capture any exceptions.

Nov 12 '05 #4

P: n/a
<gi*******************@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...
My first thought .. just for laughs and giggles ...

What happens if you add

for(j=0; j<100000;j++){}

right after "$++;" ?

The other thing I notice is that you aren't capturing exception
information - so that if the DB was telling you there was a problem
you won't know what it was.

PS to Mark whose help is often not that helpful ... The post wasn't
in HTML. (Read the code.)

I didn't say it was html. I said it was not plain text.
Nov 12 '05 #5

P: n/a
You're refering to the Content Type, Right? Looks like plain text to
me.

From: "Wescotte" <wesco...@earthlink.net>
Newsgroups: comp.databases.ibm-db2
Subject: Strange inconsistences when repeating a query more than twice
in a row
Date: 10 May 2005 13:41:49 -0700
Organization: http://groups.google.com
Lines: 72
Message-ID: <11**********************@f14g2000cwb.googlegroups .com>
NNTP-Posting-Host: 204.213.128.95
Mime-Version: 1.0
Content-Type: text/plain; charset="iso-8859-1"
Content-Transfer-Encoding: quoted-printable
X-Trace: posting.google.com 1115757714 28255 127.0.0.1 (10 May 2005
20:41:54 GMT)
X-Complaints-To: gr**********@google.com
NNTP-Posting-Date: Tue, 10 May 2005 20:41:54 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: gr**********@google.com
Injection-Info: f14g2000cwb.googlegroups.com; posting

Nov 12 '05 #6

P: n/a
See if there is a generic exception catching mechanism in Php and wrap
it in that.

Also review the doc on all odbc_* methods and look for anything that
has to do with flushing,
rewinding or caching.

Another thing... What happens if you create a new connection on each
iteration?

Nov 12 '05 #7

P: n/a
PHP does seem to hvae it's own exception catching mechanism. If I give
it an invalid SQL statement it informs me of the error code and
description.

I've examined the PHP docs and there there is a commit/rollback
function. When simply selecting I don't see any relevance to using
commit/rollback. I've also tried various ODBC driver settings like auto
commit.

I did not see any details on caching but that was my first guess as to
possible explinations to the problem.

I've tried opening/closing the connection after each SQL statement was
executed and that did not work. However I also
tried using the alternative function

resource odbc_pconnect ( string dsn, string user, string password [,
int cursor_type])

which with PHP allows you to basically keep a connection open after a
page has been rendered (which I assume is simply for performance
reasons so you can allow other pages to use the same cursors easier)

The strange thing is if I use pconnect and close after every statement
it appears display the correct results every time.

However, I implimented this method into my application where after
every statement and table is examined it closed the connection. It
appeared to work slightly better but now instead of failing after 2
attempts it was completely random. The only way I was able to get it to
work 100% of the time was to simply close the connection after
executing the SQL statements SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE
AMOUNT = 'XXX.XX' AND COMPID = 'XX'

Since closing it every time resulted in it randomly showing results or
not I don't really like this "fix" because other statements may still
have strange issues.

Right now I'm leaning towards some sort of issue with ODBC itself and
caching but I can't find any bug reports on it or patches for ODBC
itself.

I did get a responce from another group stating....
>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>> 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.>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>>


However I fail to see the logic in this explination

Nov 12 '05 #8

P: n/a
<gi*******************@yahoo.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
You're refering to the Content Type, Right? Looks like plain text to
me.

It looks like text to some. It was probably rtf format, which causes a
problem for some, especially when replying group.
Nov 12 '05 #9

P: n/a
Just letting you know I found the problem. It turned out to be a syntax
related error. The line

SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND COMPID =
'FX'

is supose to be

SELECT BATCHNUM FROM EJWLIB.BHEADW WHERE AMOUNT = 361.94 AND COMPID =
'FX'

Apparently it's an issue with converting the string to a numeric value.
Why it works sometimes and not all the time is beyond me but I'm just
glad to have figured it out.

Thanks for your help

Eric

Nov 12 '05 #10

This discussion thread is closed

Replies have been disabled for this discussion.