JDBC Type 4 Universal Driver SQL Error Code = -99999

[sbanil]

We have a JAVA application which uses following
Database product name : DB2
Database product version : DSN07012
JDBC driver name : IBM DB2 JDBC Universal Driver Architecture
JDBC driver version : 2.3.63
APplication Server:Websphere

When the prepared statement cache for the JDBC driver is set to > 0 following query fails on DB2 OS/390 version but doesn't fail on the UDB version on Windows. Any ideas.

Error:
: SQL Error Code = -99999 : MESSAGE = PreparedStatement: setNull method setting a non-nullable input parameter 1 to null.

Query is a : SELECT COUNTY FROM SYS_COUNTY where STATE_PROV_ID = ?

the parameter meter "?" is a state Id which happens to be passed in as "null" at runtime and which is being set using setBigDecimal() call to the prepared statement. STATE_PROV_ID in SYS_COUNTY table is defined as a NUMERIC(15), NOT NULL column. The error message makes perfectly sense but the problem is that we have no way to find all the places where the parameters values are nulls at runtime and fix the code.

Is this bug in driver or bug in our code? It appears that IBM remove same limitation from SQLJ from JDBC 2.3.72 version but they didn't specify anything for JDBC , Type 4 access.

Any Ideas?

- Anil

[sbanil]

If anybody cares==> Here is the reply from IBM on the behaviour. They confirmed that this validation is removed from Unversal Driver version 2.3.73.

"The problem here is the describe information returned from the server for the input parameters. The describe information returned from different servers is different that's why you see different results. Here is what I am seeing with regard to different servers.

For example sql statement "select * from table where col1 = ?" where col1 is defined as char(10) not null.

The describe information returned regarding the nullability of the input column (i.e col1) for

v7ZOs : Nullability of col1 is returned as false that is correct since the col1 is defined as not null.
v8 zOs : Nullability of col1 is returned as true that is incorrect.
v8 UDB: Nullability of col1 is returned as true that is incorrect.

Universal JDBC driver prior to 2.3.73 version were comparing the nullability returned in the describe information to the value passed in setXXX() method. So in the case of v7 zOS the nullability returned from the server says that the column can't be null whereas the application was passing it as null so you get the error customer is getting.

I also want to make a comment on the prepared statement cache behavior that you are seeing with zOs server. The reason is that when prepared statement cache is set to zero the prepared statement does not request the describe information so it does not check the nullability of the value set in the setXXX() method.

Now let me answer your questions based on the above information

1. The fix for setNull is also applicable for both Type and Type 4.

3. It works for zOS databases because of the above reason.

Hope this will help understand why you are seeing weird behavior."

I then responded and asked:

"So, what you are saying is that if they switch to the version 2.3.73 driver, they will not see the error with v7 ZOS anymore? Is v8 ZOS and UDB behavior going to be "fixed" with regards to:
v7ZOs : Nullability of col1 is returned as false that is correct since the col1 is defined as not null.
v8 zOs : Nullability of col1 is returned as true that is incorrect.
v8 UDB: Nullability of col1 is returned as true that is incorrect."

And they responded with:

"Yes the problem should go away if they switch to 2.3.73 against v7 zOs. The fix was to remove the nullability check in the driver and let the server decide whether it liked the null value for that column. So with this fix the driver will behave the same against all the servers and will not depend on faulty describe information sent by the server. Its up to the server team to decide if they are ever going to fix this problem. But for now the driver side fix will take care of the server side problem."

Please let me know if you have any additional questions, or if I can close this PMR.