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.AS400JDBCDriver").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 ---------------