473,856 Members | 1,745 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 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>

Nov 12 '05 #1
9 2212
Unforteuneltly, there are not too many iSeries DB2 people on this forum. You
might want to also post on:

comp.sys.ibm.as 400.misc
comp.sys.ibm.as 400
ibm.software.db 2.os400

also, please post in text, not rtf or html.
Nov 12 '05 #2
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
>>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
<gi************ *******@yahoo.c om> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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
You're refering to the Content Type, Right? Looks like plain text to
me.

From: "Wescotte" <wesco...@earth link.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************ **********@f14g 2000cwb.googleg roups.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**********@go ogle.com
NNTP-Posting-Date: Tue, 10 May 2005 20:41:54 +0000 (UTC)
User-Agent: G2/0.2
Complaints-To: gr**********@go ogle.com
Injection-Info: f14g2000cwb.goo glegroups.com; posting

Nov 12 '05 #6
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
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
<gi************ *******@yahoo.c om> wrote in message
news:11******** *************@f 14g2000cwb.goog legroups.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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

0
1382
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
13
2747
by: Wescotte | last post by:
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...
6
4575
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, I copied the SQL statement back into a query and tried running it again. It now also only took 1...
2
1422
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 using the exact same connection parameters that I have specified in my web app. Even more strange...
4
3867
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 Pear::Mail::send are repeated twice. It is the strangest
11
2604
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
2283
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 which have identical values in all fields. This is 100% correct. The second query returns nothing....
20
1688
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
1832
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 subtracting the two numbers (shopper_ID is older from when they registered). Is this strange or bad or...
0
9760
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 synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
11052
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. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10777
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 most users, this new feature is actually very convenient. If you want to control the update process,...
0
10379
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 choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7930
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5956
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4573
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
4172
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3198
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 effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.