Client Access ODBC DB2 driver problem

[Wescotte]

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

[Jan M. Nelken]

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

[Wescotte]

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

[Jan M. Nelken]

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