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

Client Access ODBC DB2 driver problem

P: n/a
I'm working with IBM's Client Access ODBC Driver version 8.00.08.00
5/8/2001 installed with Client Access Express V5R1M0.

The issue is if I execute the exact same select statement more than
twice in a row I will no longer get results back. For example using the
following table

CREATE TABLE EJWLIB.BHEADW (
BATCHNUM NUMERIC(5) NOT NULL,
COMPID CHAR(2) NOT NULL,
AMOUNT NUMERIC(9,2) NOT NULL,
BDATE DATE NOT NULL,
EDIREF CHAR(15),
FLAG CHAR(1) NOT NULL )

And I insert the following values (the table is EMPTY except for single
row below)
INSERT INTO EJWLIB.BHEADW VALUES ('1', 'FX', '361.94', '20050422',
'305855545', 'O')

and peform
SELECT * FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND COMPID = 'FX'
I get the correct result the first two times I execute the statement
and then NO results for every additional time I call it UNLESS I close
the connection
and reestablish it.

I've tried using PHP, wxWidgets's wxDB (odbc class) and MS Excel's
query tools all with the same results. Since all 3 apps have the same
results I assume it's an issue with the ODBC driver. I examined a trace
log from the ODBC driver and was unable to find any differences with
the data in the log after the 2nd select statement.

If I change the select statement to
SELECT * FROM EJWLIB.BHEADW
OR
SELECT * FROM EJWLIB.BHEADW WHERE COMPID = 'FX'
it works correctly. However using
SELECT * FROM EJWLIB.BHEAD WHERE AMOUNT = '361.94'
it returns to the same issue stated above where it will work correctly
for two calls and then say there are no
results.

If I perform a
SELECT * FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND COMPID = 'FX'
get my results and perform a
SELECT * FROM EJWLIB.BHEADW
and get the results I can loop the two as many times as I want.

I can't seem to find any freeware alternative DB2 ODBC drivers out
there to play with. Anyone have any ideas?

Thanks

Eric Wescott

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


P: n/a
Wescotte wrote:
I'm working with IBM's Client Access ODBC Driver version 8.00.08.00
5/8/2001 installed with Client Access Express V5R1M0.

The issue is if I execute the exact same select statement more than
twice in a row I will no longer get results back. For example using the
following table

CREATE TABLE EJWLIB.BHEADW (
BATCHNUM NUMERIC(5) NOT NULL,
COMPID CHAR(2) NOT NULL,
AMOUNT NUMERIC(9,2) NOT NULL,
BDATE DATE NOT NULL,
EDIREF CHAR(15),
FLAG CHAR(1) NOT NULL )

And I insert the following values (the table is EMPTY except for single
row below)
INSERT INTO EJWLIB.BHEADW VALUES ('1', 'FX', '361.94', '20050422',
'305855545', 'O')

and peform
SELECT * FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND COMPID = 'FX'
I get the correct result the first two times I execute the statement
and then NO results for every additional time I call it UNLESS I close
the connection
and reestablish it.

I've tried using PHP, wxWidgets's wxDB (odbc class) and MS Excel's
query tools all with the same results. Since all 3 apps have the same
results I assume it's an issue with the ODBC driver. I examined a trace
log from the ODBC driver and was unable to find any differences with
the data in the log after the 2nd select statement.

If I change the select statement to
SELECT * FROM EJWLIB.BHEADW
OR
SELECT * FROM EJWLIB.BHEADW WHERE COMPID = 'FX'
it works correctly. However using
SELECT * FROM EJWLIB.BHEAD WHERE AMOUNT = '361.94'
it returns to the same issue stated above where it will work correctly
for two calls and then say there are no
results.

If I perform a
SELECT * FROM EJWLIB.BHEADW WHERE AMOUNT = '361.94' AND COMPID = 'FX'
get my results and perform a
SELECT * FROM EJWLIB.BHEADW
and get the results I can loop the two as many times as I want.

I can't seem to find any freeware alternative DB2 ODBC drivers out
there to play with. Anyone have any ideas?

Thanks

Eric Wescott


Try changing AMOUNT definition to string - since your predicate is on equality
of string compared to numerical value - which may be rendering undefined results.

Jan M. Nelken
Nov 12 '05 #2

P: n/a
It always returns the correct result on the first and second attempt.
It's the 3rd + try where it doesn't return any results. The SQL
statement is identical every time and looking at the trace log appears
to be identical for every statement even though there are no results
returned. If I perform the statement and then perform any other
statement and then repeat the original it will always display teh
correct result. To me it seems to me more a problem with the driver
itself but I can't find an alternate driver to use.
Try changing AMOUNT definition to string - since your predicate is on equality of string compared to numerical value - which may be rendering undefined results.
Jan M. Nelken


Nov 12 '05 #3

P: n/a
Wescotte wrote:
It always returns the correct result on the first and second attempt.
It's the 3rd + try where it doesn't return any results. The SQL
statement is identical every time and looking at the trace log appears
to be identical for every statement even though there are no results
returned. If I perform the statement and then perform any other
statement and then repeat the original it will always display teh
correct result. To me it seems to me more a problem with the driver
itself but I can't find an alternate driver to use.

Try changing AMOUNT definition to string - since your predicate is on


equality
of string compared to numerical value - which may be rendering


undefined results.
Jan M. Nelken



If you can take trace of that on client and on AS/400 - it would be helpful.
Unfortunately I don't know anything about AS/400 and/or Client Access (I am DB2
UDB person).

BUt you can always try to download DB2 UDB Connect Personal Edition (trail) and
configure your machine to connect to AS/400. With DB2 COnnect you will have Db2
Universal JDBC driver (JCC) which you can use to try your query.

Jan M. Nelken
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.