DB2 jdbc driver throws errors at end of query

[slords]

https://www.ibm.com/support/pages/invalid-operation-result-set-closed-error-data-server-driver-jdbc

When issuing a query against db2 I'm getting the following error:

0: db2> select count(*) from wera.v_debt;
+-----+
|  1  |
+-----+
| 970 |
Error: [jcc][t4][10120][10898][4.26.14] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (state=,code=-4470)

Driver being used is:

0: db2> !scan
scan complete in 1ms
1 driver classes found
Compliant Version Driver Class
yes       4.26    com.ibm.db2.jcc.DB2Driver

[julianhyde]

Can you do !set verbose true and then re-run? I'd like to see the error stack.

[julianhyde]

It looks similar to #291. If the cause is the same, we should fix them both.

[slords]

0: db2> select count(*) from sysstat.tables;
+-----+
|  1  |
+-----+
| 431 |
Error: [jcc][t4][10120][10898][4.26.14] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (state=,code=-4470)
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.26.14] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
	at com.ibm.db2.jcc.am.b7.a(b7.java:794)
	at com.ibm.db2.jcc.am.b7.a(b7.java:66)
	at com.ibm.db2.jcc.am.b7.a(b7.java:116)
	at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4773)
	at com.ibm.db2.jcc.am.ResultSet.nextX(ResultSet.java:399)
	at com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:378)
	at sqlline.BufferedRows.nextList(BufferedRows.java:109)
	at sqlline.BufferedRows.hasNext(BufferedRows.java:61)
	at sqlline.TableOutputFormat.print(TableOutputFormat.java:39)
	at sqlline.SqlLine.print(SqlLine.java:1675)
	at sqlline.Commands.executeSingleQuery(Commands.java:1063)
	at sqlline.Commands.execute(Commands.java:1003)
	at sqlline.Commands.sql(Commands.java:967)
	at sqlline.SqlLine.dispatch(SqlLine.java:734)
	at sqlline.SqlLine.begin(SqlLine.java:541)
	at sqlline.SqlLine.start(SqlLine.java:267)
	at sqlline.SqlLine.main(SqlLine.java:206)

[slords]

I have found if I connect using the following format it works fine:

!connect jdbc:db2://host:port/db:allowNextOnExhaustedResultSet=1;

I got that custom parameter from the linked page in the original report.

[julianhyde]

Thanks, @slords. Glad you found a workaround. Maybe others can use it.

The stack is really helpful. I'll work on a fix. I don't have access to DB2 so I may ask you to help test it.

[julianhyde]

@slords, Please try the latest master (3fadb13) and let me know whether it fixes the problem. I used PR #294 by @madeye-matt.

[slords]

Nope. Still getting the same error:

0: db2> select count(*) from sysstat.tables;
+-----+
|  1  |
+-----+
| 431 |
Error: [jcc][t4][10120][10898][4.26.14] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null (state=,code=-4470)
com.ibm.db2.jcc.am.SqlException: [jcc][t4][10120][10898][4.26.14] Invalid operation: result set is closed. ERRORCODE=-4470, SQLSTATE=null
	at com.ibm.db2.jcc.am.b7.a(b7.java:794)
	at com.ibm.db2.jcc.am.b7.a(b7.java:66)
	at com.ibm.db2.jcc.am.b7.a(b7.java:116)
	at com.ibm.db2.jcc.am.ResultSet.checkForClosedResultSet(ResultSet.java:4773)
	at com.ibm.db2.jcc.am.ResultSet.nextX(ResultSet.java:399)
	at com.ibm.db2.jcc.am.ResultSet.next(ResultSet.java:378)
	at sqlline.BufferedRows.nextList(BufferedRows.java:109)
	at sqlline.BufferedRows.hasNext(BufferedRows.java:61)
	at sqlline.TableOutputFormat.print(TableOutputFormat.java:39)
	at sqlline.SqlLine.print(SqlLine.java:1678)
	at sqlline.Commands.executeSingleQuery(Commands.java:1063)
	at sqlline.Commands.execute(Commands.java:1003)
	at sqlline.Commands.sql(Commands.java:967)
	at sqlline.SqlLine.dispatch(SqlLine.java:737)
	at sqlline.SqlLine.begin(SqlLine.java:541)
	at sqlline.SqlLine.start(SqlLine.java:267)
	at sqlline.SqlLine.main(SqlLine.java:206)

[slords]

Would it be possible to have the parameter I'm setting manually in the connect string added to the project as one of the dbinfo parameters? DB2 doesn't support the type 2 jdbc driver anymore and their type 4 will always behave this way. The program is already so smart to know how to quote columns/tables/etc depending on the database type. This would just be one additional "smart" configuration.

[julianhyde]

Would it be possible to have the parameter I'm setting manually in the connect string added to the project as one of the dbinfo parameters?

I don't think that's a good idea. The problem occurs in other drivers too (e.g. #291 is SQLite) so let's solve it once and for all.

Can you please build and test against https://github.com/julianhyde/sqlline/tree/379-closed? I have made another attempt at a fix.

[slords]

I don't see that this branch is any different then the last test I did (3fadb13). I've already tested this and it failed with the same error and verbose output as shown. Was there more you wanted tested?

[julianhyde]

Oops, sorry, I forgot to push 8a01ffc. Can you please try now.

[slords]

That appears to have fixed it.

[julianhyde]

Fixed in e0f9e9a.

Many thanks for your assistance and patience, @slords.

[greyfairer]

@julianhyde I discovered the exact same issue while trying to upgrade sql-phoenix to use sqlline 1.9. It's also caused by SQL-Phoenix closing the resultset as soon as it reaches the end, so this patch should fix it. Any idea for a release date?

0: jdbc:phoenix:> select count(*) from customer;
+----------+
| COUNT(1) |
+----------+
| 1000000  |
Error: ERROR 1101 (XCL01): ResultSet is closed. (state=XCL01,code=1101)
java.sql.SQLException: ERROR 1101 (XCL01): ResultSet is closed.
	at org.apache.phoenix.exception.SQLExceptionCode$Factory$1.newException(SQLExceptionCode.java:497)
	at org.apache.phoenix.exception.SQLExceptionInfo.buildException(SQLExceptionInfo.java:150)
	at org.apache.phoenix.jdbc.PhoenixResultSet.checkOpen(PhoenixResultSet.java:242)
	at org.apache.phoenix.jdbc.PhoenixResultSet.next(PhoenixResultSet.java:799)
	at sqlline.BufferedRows.nextList(BufferedRows.java:109)
	at sqlline.BufferedRows.hasNext(BufferedRows.java:61)
	at sqlline.TableOutputFormat.print(TableOutputFormat.java:39)
	at sqlline.SqlLine.print(SqlLine.java:1675)
	at sqlline.Commands.executeSingleQuery(Commands.java:1063)

[julianhyde]

I don't know when the next release will be.