Problem: java ResultSet cursor from SQL/PL stored-procedure is
FORWARD_ONLY.
Is it possible to have ResultSet cursors from SQL/PL procedures to
scroll forward and backwards? Perhaps I am missing something.
Environment: client UDB 8.1 FP9a on Win32, server UDB 8.1 FP9a on
Solaris 8 (64BIT).
The JDK version on the WinXP client is
java version "1.4.2_07"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.2_07-b05)
Java HotSpot(TM) Client VM (build 1.4.2_07-b05, mixed mode)
The ResultSet.previous() and the ResultSet.relative() throw exceptions
saying
"This method should only be called on ResultSet objects that are
scrollable
(type TYPE_SCROLL_SENSITIVE, or TYPE_SCROLL_INSENSITIVE).
If I replace the stored-procedure call by a direct query, then the
ResultSet is scrollable both forward and backwards.
Results Sets from SQL/PL procedures are forward-only (i.e. the
Resultset.Next works fine,
but the ResultSet.relative and Resultset.previous throw exceptions.
The SQL/PL stored-procedure declares the cursor as
"WITH RETURN TO CLIENT" and also uses "FOR READ ONLY" on the query
and it makes no difference whether it is static or dynamic SQL.
Some more details below:
/* Universal Driver */
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection conn = DriverManager.getConnection("jdbc:db2:ods",
"u450099", PWD);
/**/
Class.forName("com.ibm.db2.jcc.DB2Driver");
Connection conn = DriverManager.getConnection("jdbc:db2:ods",
"my_userid", PWD);
public void doCursor(Connection conn) throws Exception {
Statement stmt =
conn.createStatement(ResultSet.TYPE_SCROLL_INSENSI TIVE,
ResultSet.CONCUR_READ_ONLY);
ResultSet rs = stmt.executeQuery("call
p_query_msg_exceptions('ownership_rule = ''M055C'' ')");
rs.next(); ***** WORKS OK
rs.previous(); ******* THIS THROWS EXCEPTION
System.out.println(rs.getString(1));
rs.close();
stmt.close();
}
Exception Details:
com.ibm.db2.jcc.b.SqlException: This method should only be called on
ResultSet
objects that are scrollable(type TYPE_SCROLL_SENSITIVE or
TYPE_SCROLL_INSENSITIVE)
at com.ibm.db2.jcc.b.ad.nb(ad.java:3463)
at com.ibm.db2.jcc.b.ad.g(ad.java:1896)
at com.ibm.db2.jcc.b.ad.relative(ad.java:1883)
at ProcTest.doCursor(ProcTest.java:34)
at ProcTest.main(ProcTest.java:104)
