Question & Answer
Question
Cause
Answer
1.0) ResultSet.next() returns Invalid operation: result set is closed. ERRORCODE=-4470
-
- 1.1) Nested ResultSets
By default auto-commit is enabled and -4470 is returned once the inner resultset Resultset2 has completed fetching all the rows from Resultset2 because this causes a COMMIT to be sent to the server. The COMMIT also closes any other open ResultSets such as ResultSet1. This behaviour conforms to JDBC specifications.
One alternative is to disable autocommit before any nested Resultset code and then re-enable auto-commit after the nested resultset code.
Connection.setAutoCommit(false); /* Disable autocommit */
Resultset1 = getResultSet(sqlQuery1);
While(Resultset1.next()) /* Returns exception here */
{
/* Inner resultset */
Resultset2 = getResultSet(sqlQuery2);
While(Resultset2.next())
{
/* Code */
}
}
Connection.commit(); /* Perform manual COMMIT */ Connection.setAutoCommit(true); /* Enable autocommit */
- 1.1) Nested ResultSets
-
- 1.2) Calling ResultSet.next() after it previously returned false
while(Resultset.next())
{
/* Code */
}
Resultset.next(); /* Returns an exception here */Once the while loop returns false the ResultSet object is automatically closed. Thus any subsequent calls to resultset.next() will fail.
Another workaround for 1.1 and 1.2 is to set downgradeHoldCursorsUnderXA=true and resultSetHoldability=1.
- 1.2) Calling ResultSet.next() after it previously returned false
2.0) An unexpected token "" was found following "". Expected tokens may include: "Y OPTIMIZE FOR 1 ROW"
The SQL statement contains a semi-colon (;). To resolve this error, the semi-colon should be removed.
For example change
"SELECT C1 FROM T1;"
to
"SELECT C1 FROM T1"
3.0) Invalid operation: wasNull() called with no data retrieved or SQL0301
The column PRODNUM on the database is defined as INTEGER. The legacy driver would convert the data type String to Integer before sending it to the DB2 for z/OS database. Although this situation applies only to DB2 for z/OS database it is good coding practice to match the data type used in the JDBC call and the database.
The Java setXXXX or getXXXX methods must match the column datatype defined in the DB2 for z/OS database.
Example:
SELECT * FROM PRODUCTS WHERE PRODUCTNUMBER=?
The legacy JDBC driver accepted setString.
CallableStatement.setString(1, productNumber)
With the Universal JDBC driver the application must use setInt() since PRODNUM is defined as INTEGER on the database.
CallableStatement.setInt(1, productNumber);
CREATE TABLE PRODUCTS(PRODNUM INTEGER, ...)
4.0) Calling a stored procedure using executeQuery() returns ERRORCODE=-4476 no result set was returned.
[Jan 01, 2010 17:23:34] ERROR T4TestProgram: sql error -4476 null com.ibm.db2.jcc.am.SqlException: [jcc][10100][10910][4.9.78]
java.sql.CallableStatement.executeQuery() was called but no result set
was returned. Use java.sql.CallableStatement.executeUpdate() for non-queries. ERRORCODE=-4476, SQLSTATE=null
When calling a stored procedure the method PreparedStatement.executeUpdate() must be used if the stored procedure does not return a ResultSet. However there may be situations in which the stored procedure does return a ResultSet and thus -4476 is returned. One alternative is to use the PreparedStatement.execute() method and then check the return value to determine if a ResultSet was returned before attempting to retrieve a row from the ResultSet.
5.0) Calling a Java stored procedure returns Invalid SQL statement length in the server's db2diag.log
2010-01-01-09.00.00.339060-240 E3075A777 LEVEL: Error
PID : 790594 TID : 2829 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID:XX.XX.XX.XX.100324154203
AUTHID : DB2INST1
EDUID : 2829 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, DRDA Application Server, sqljsParseSqlSttGrpNOC, probe:419
MESSAGE : DIA5000C A DRDA AS token "RECOVERABLE" was detected. The diagnostic data returned is (SRVDGN): "FUNCTION ID = 0050 , PROBE POINT = 0419 , TRACE POINT = 0015 , SUBCODE1 = FFFFFFFF804B0095, SUBCODE2 =
00000000002FFF8F, SUBCODE3 = 0000000000000000, ERROR MSG = Parser:
Invalid SQL statement length".
2010-01-01-09.00.00.331662-240 I3853A626 LEVEL: Error
PID : 790594 TID : 2829 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-7 APPID: .100324154203
AUTHID : DB2INST1
EDUID : 2829 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, DRDA Application Server, sqljsParseSqlSttGrpNOC, probe:15
MESSAGE : ZRC=0x804B0095=-2142568299=SQLJS_VALNSP "VALUE ERROR"
DATA #1 : DDM Object, PD_TYPE_DDM_OBJECT, 16 bytes
data length : 3145621
codepoint : 0x2414 ( SQLSTT )
length : 8 ( EXTENDED )
An invalid SQL statement was sent to the Java stored procedure. The legacy JDBC driver may have accepted queries containing invalid characters such as carriage returns.
Further notes regarding Java stored procedures
For Java routines (stored procedures and user defined functions) starting with DB2 v9.5 by default the DB2 Universal JDBC driver is used to run Java routines instead of the legacy JDBC driver. Thus a routine which worked in DB2 v8.x may fail to run on DB2 v9.5 or higher due to non-compliant JDBC coding. The DB2 registry variable DB2_USE_DB2JCCT2_JROUTINE can be set to revert to DB2 v8.x behaviour. For more details please see the link Specification of a driver for Java routines.
As of v10.1 DB2_USE_DB2JCCT2_JROUTINE has been removed since Java stored procedure no longer support the use of legacy driver db2java.zip
Related Information
Was this topic helpful?
Document Information
Modified date:
07 December 2022
UID
swg21420760