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.AS400JDBCDriver"
but on the server it is of course "com.ibm.db2.jdbc.app.DB2Driver".
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 preparedstatement 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.access.AS400JDBCDrive r").newInstance();
String AS400_v5r3_SQL = "SELECT poid, module, editable, oid, server,
created, datatype, effectiveto, parameter, value, upoid, actorgroup,
effectivefrom FROM REF.parametervalue_data WHERE module = ? AND
effectiveto = ? AND actorgroup = ? AND parameter = (SELECT poid FROM
REF.parameter_data WHERE parameterid = ? AND effectiveto = ?) AND
server = ?";
Timestamp FAR_DISTANT_FUTURE = new Timestamp((new
GregorianCalendar(2999, 11, 31, 23, 59, 59)).getTime().getTime());
Integer AS400_v5r3_SERVERPOID = new Integer(5);
Integer int1 = new Integer(17);
Integer int2 = new Integer(18);
Vector v = new Vector();
v.add("ModuleType");
v.add("RunCond");
v.add("DownSP");
PreparedStatement statement = (PreparedStatement)
connection.prepareStatement(AS400_v5r3_SQL);
for (Iterator iter = v.iterator(); iter.hasNext();) {
String element = (String) iter.next();
statement.setObject(1, int1);
statement.setObject(2, FAR_DISTANT_FUTURE);
statement.setObject(3, int2);
statement.setObject(4, element);
statement.setObject(5, FAR_DISTANT_FUTURE);
statement.setObject(6, AS400_v5r3_SERVERPOID);
ResultSet rs = (ResultSet) statement.executeQuery();
if (rs.next()) {
System.err.println(element + ": " + rs.getObject("value"));
} else {
System.err.println(element + ": no value");
}
}
....
------------- code end ---------------