iSeries: JDBC problem v5r3

[tom.eeraerts]

Hello,

I have a problem migrating an application from v5r2 to v5r3. The
problem is with the prepared statements.

To see what the problem is, i extracted a small piece of code and
debugged the application while running it on my local window machine
(IBM WSAD) but still connecting to the AS400.

For the local debugging I use "com.ibm.as400. access.AS400JDB CDriver"
but on the server it is of course "com.ibm.db2.jd bc.app.DB2Drive r".

When I use our old jdbc driver jar (an older toolbox driver - info:
JDBC driver major version = 3). It is ok.
When I use the the toolbox driver from the new server (Toolbox for Java
- Open Source Software, JTOpen 4.7, codebase 5722-JC1 V5R3M0.7) I have
a big problem with the prepared statements (this is the first problem I
have noticed, maybe there are still other problems, but I would like to
see this problem solved first).

I have a preparedstateme nt that is called 3 times (code fragment
follows at the end). There are 6 parameters in the statement. 5 of
which are always the same and one that is changed every time.

This is the output of the old driver - you see it finds one value for
each time the statement is executed:
ModuleType: S
RunCond: 2
DownSP: 1

When I run the same program with the newer toolbox driver in the
classpath, it sometimes does not find a result for the select. Output:
ModuleType: no value
RunCond: 2
DownSP: 1

I tried it once again and the the result was:
ModuleType: no value
RunCond: 2
DownSP: no value

You see it is not always the same that returns no result.

This is really puzzeling me. I cannot begin to wonder where the problem
lies. Maybe there are some (default) JDBC parameters that have been
changed??? Maybe something changed from v5r2 to v5r3 (I already looked
at a couple of documents on the web but nothing relevant to my
problem).

If someone has any idea, please let me know. I am going nuts :)

Tom
----------- code start ------------
....
Class.forName(" com.ibm.as400.a ccess.AS400JDBC Driver").newIns tance();

String AS400_v5r3_SQL = "SELECT poid, module, editable, oid, server,
created, datatype, effectiveto, parameter, value, upoid, actorgroup,
effectivefrom FROM REF.parameterva lue_data WHERE module = ? AND
effectiveto = ? AND actorgroup = ? AND parameter = (SELECT poid FROM
REF.parameter_d ata WHERE parameterid = ? AND effectiveto = ?) AND
server = ?";

Timestamp FAR_DISTANT_FUT URE = new Timestamp((new
GregorianCalend ar(2999, 11, 31, 23, 59, 59)).getTime(). getTime());
Integer AS400_v5r3_SERV ERPOID = new Integer(5);
Integer int1 = new Integer(17);
Integer int2 = new Integer(18);

Vector v = new Vector();
v.add("ModuleTy pe");
v.add("RunCond" );
v.add("DownSP") ;

PreparedStateme nt statement = (PreparedStatem ent)
connection.prep areStatement(AS 400_v5r3_SQL);

for (Iterator iter = v.iterator(); iter.hasNext(); ) {

String element = (String) iter.next();

statement.setOb ject(1, int1);
statement.setOb ject(2, FAR_DISTANT_FUT URE);
statement.setOb ject(3, int2);
statement.setOb ject(4, element);
statement.setOb ject(5, FAR_DISTANT_FUT URE);
statement.setOb ject(6, AS400_v5r3_SERV ERPOID);

ResultSet rs = (ResultSet) statement.execu teQuery();

if (rs.next()) {
System.err.prin tln(element + ": " + rs.getObject("v alue"));
} else {
System.err.prin tln(element + ": no value");
}
}
....

------------- code end ---------------

[Bernd Hohmann]

to**********@gm ail.com wrote:

This is really puzzeling me. I cannot begin to wonder where the problem
lies.

I have no real idea because I've not so much experience on AS400 to give
a qualified statement.

But the usual problems with DB2 and JDBC (especially PreparedStateme nts)
is a version mismatch between DB2JAVA.ZIP used on the client and the DB2
version used.

So you may copy the db2java.zip file from the AS400 to your workstation
and use this zip file for your projects.

You have to check your classpath, maybe a different (wrong) db2java.zip
is found first. Debugging classpath is sometimes strange to horrible so
take care!

Bernd