Bug #11793 DECLARE CURSOR for REUSE does not work with JDBC Driver 7.6.00.11
Submitted: 7 Jul 2005 12:39 Modified: 7 Jan 2008 11:13
Reporter: DIRK SCHLENZIG Email Updates:
Status: Closed Impact on me:
None 
Category:MaxDB Severity:S1 (Critical)
Version:7.5.0.24 OS:Linux (Suse Linux 9.3)
Assigned to: CPU Architecture:Any

[7 Jul 2005 12:39] DIRK SCHLENZIG
Description:
Testcase:

CREATE TABLE TEST (A FIXED (2,0), B VARCHAR(10));
DECLARE tmp_result CURSOR FOR ( SELECT * FROM TEST ) FOR REUSE ;
SELECT * FROM tmp_result ;

works fine with JDBC Driver 7.5.xx .
but does NOT work with JDBC Driver 7.6.00.11 .
Throws an error "unknown tabel tmp_result"

JRE 1.4.x

Connection settings (equals in both test cases):

URL: jdbc:sapdb://192.x.x.x/DB_NAME
Driver: com.sap.dbtech.jdbc.DriverSapDB
Properties: user=xxx password=xxx

How to repeat:
just execute the upper code example.

Suggested fix:
ASAP :-)
[12 Nov 2005 2:59] C.J. Adams-Collier
Dirk,

Please provide me with the jdbc jar file that you used to successfully complete the request.

I'm tracking down the exact date of breakage so I can find what code was modified.

Cheers,

C.J.
[17 Nov 2005 10:54] DIRK SCHLENZIG
Hi C.J.

Thanks !
We cannot upload the file to this system because the file limit is 200 K.
Please download the JDBC driver that we used here:

http://my.siteforum.com/4mysql/jdbc_sapdb-7_5_0_5.jar

this is the version that we used to successfully complete the request.
The exact version / build number is 7.5.0 Build 005-000-002-407

thank you.

Dirk

Dirk,

Please provide me with the jdbc jar file that you used to successfully
complete the request.

I'm tracking down the exact date of breakage so I can find what code
was modified.

Cheers,

C.J.
[18 Nov 2005 17:24] C.J. Adams-Collier
Strange.  I'm getting the same exception with the jdbc driver you provided as with all of the others.

Please take a look at my regression test and let me know if it should be sufficient for reproducing the problem.

http://colliertech.org/~cjcollier/mysql/maxdb/bugs/11793.tgz

If you need to create the 'test' database, you can get the createDB program here: http://colliertech.org/svn/mysql/maxdb/createDB/

It is conceivable that the version of MaxDB I am on is not compatable with that portion of the jdbc driver.  I am running v7.6.00.16.
[21 Nov 2005 10:43] DIRK SCHLENZIG
regression test java file

Attachment: regress.java (text/x-java), 3.24 KiB.

[21 Nov 2005 10:46] DIRK SCHLENZIG
Hi C.J.,

We customized the java file in a way that you can reproduce the errors.
It now works with the old driver but with the new driver ... declare cursors doesnt work.

Please let me know if this helps.

thank you,

Dirk
[22 Nov 2005 5:26] C.J. Adams-Collier
Thank you Dirk,

I will report this to SAP.

Cheers,

C.J.
[22 Nov 2005 22:04] C.J. Adams-Collier
Working regression test - altered syntax slightly

Attachment: regress.working.java (text/x-java), 3.32 KiB.

[22 Nov 2005 22:06] C.J. Adams-Collier
Dirk,

Here is the word from SAP.  I have modified the regression code to resolve the issue in my tests.  Let me know if this addresses your issue.

C.J.

---

Hi C.J.,
this is not really a bug. It's more a change in our cursor behaviour with version 7.6. In earlier versions of our JDBC driver the default resultset type was scrollable, so the cursor was open also when you have fetched all rows of the resultset. We changed this in version 7.6, the default resultset type is now forward only and the cursor is closed after fetching all rows. This is closer to the JDBC standard and allows us to implement some performance optimizations. 

How does it influence your problem?
DECLARE tmp_result CURSOR FOR ( SELECT * FROM TEST ) FOR REUSE ;
This executes the statement and creates a cursor tmp_result. It also fetches the first n rows and delivers them to the client. The number of rows delivered depends on the size of the order packet used for client/kernel communication. If the whole resultset fits to the order packet then the cursor will be closed, because the resultset type is forward only and you have fetched all row. 

SELECT * FROM tmp_result;
Since all rows has been already delivered to the JDBC client the cursor is closed. If you try to select this cursor you will get the error message "unknown table tmp_result".

Workaround:
Use a scrollable cursor for the DECLARE statement. This will keep open the cursor and everything will work like in 7.5.

java.sql.Statement st = Connection.createStatement (ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet rs = st.execute ("DECLARE tmp_result CURSOR FOR ( SELECT * FROM TEST ) FOR REUSE");
...

Best regards,
Marco
[8 Dec 2005 12:18] DIRK SCHLENZIG
C.J.,

I'm sorry for the delay.
We did extend our testcase with the recommended resultSetType but it didnt change anything: Driver v7.5.x works. Driver: 7.6.x does NOT work.

See our test case attached.
[8 Dec 2005 12:19] DIRK SCHLENZIG
regression test java file

Attachment: regress.java (text/x-java), 3.31 KiB.

[8 Dec 2005 18:24] C.J. Adams-Collier
I'm sorry.  I thought I had attached this.  Please let me know if this regression program works for you.
[8 Dec 2005 18:25] C.J. Adams-Collier
Regression program that works for me.

Attachment: regress.java (text/x-java), 3.32 KiB.

[7 Jan 2008 11:13] Ulf Wendel
Thank you for a problem report. According to http://www.mysql.com/news-and-events/press-release/release_2007_40.html MaxDB has been given back to SAP.

If you have purchased MaxDB support from MySQL, please report the issue at the MySQL Customer Support Center on http://support.mysql.com.

If you are an open source community member, please report your issue at SAP on http://www.sdn.sap.com/irj/sdn/maxdb .