By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
440,905 Members | 894 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 440,905 IT Pros & Developers. It's quick & easy.

ResultSet cursor is forward_only from remote SQL/PL procedure ?

P: n/a

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)

Dec 7 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
The symptom is identical if the SQL/PL stored procedure runs locally on
Windows,
identical error message when a Java program attempts to use
ResultSet.relative()
on the resultset from an SQL/PL procedure.

Does anyone know why there should be a scrolling difference between the
result-set from a stored-procedure and the identical result-set from a
query ?

Dec 8 '05 #2

P: n/a
_l*****@yahoo.com wrote:
The symptom is identical if the SQL/PL stored procedure runs locally on
Windows,
identical error message when a Java program attempts to use
ResultSet.relative()
on the resultset from an SQL/PL procedure.

Does anyone know why there should be a scrolling difference between the
result-set from a stored-procedure and the identical result-set from a
query ?

That's the way it is defined and designed.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #3

P: n/a
Damn! I cannot see that in the docs (yet).

Dec 8 '05 #4

P: n/a
_l*****@yahoo.com wrote:
Damn! I cannot see that in the docs (yet).

Where do you find scrollable cursor information. A quick serach in
information center points towards CLI, JDBC, ....
can't see SQL Procedures anywhere...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab
Dec 8 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.