#686 Strange results/updateCount processing

[Andrej Fink]

Support of complex query with a mix of DDL, DML and result sets, and possible errors looks broken in JTDS (1.2.5,1.3.0)

I made simple example (attached; there is a switch MS JDBC/JTDS). With MS JDBC it works as expected, with JTDS - results are very buggy...

SQL (more details in attached test):
create table #temp (id int not null primary key)
insert #temp values (1)
select * from #temp
insert #temp values (2)
update #notexists set bar=1
insert #temp select * from #temp
drop table #temp

output
Result 1 is an update count: 1 null
Result 2 is a ResultSet: net.sourceforge.jtds.jdbc.JtdsResultSet@765291
get resultset failed java.sql.SQLException: Invalid object name '#notexists'.
java.sql.SQLException: Invalid object name '#notexists'.
at net.sourceforge.jtds.jdbc.SQLDiagnostic.addDiagnostic(SQLDiagnostic.java:372)
at net.sourceforge.jtds.jdbc.TdsCore.tdsErrorToken(TdsCore.java:2893)
at net.sourceforge.jtds.jdbc.TdsCore.nextToken(TdsCore.java:2335)
at net.sourceforge.jtds.jdbc.TdsCore.getMoreResults(TdsCore.java:638)
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:613)
at net.sourceforge.jtds.jdbc.JtdsStatement.cacheResults(JtdsStatement.java:703)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:612)
at TmpTest.main(TmpTest.java:113)
Exception in thread "main" java.lang.IllegalStateException: There should be no queued results.
at net.sourceforge.jtds.jdbc.JtdsStatement.processResults(JtdsStatement.java:609)
at net.sourceforge.jtds.jdbc.JtdsStatement.cacheResults(JtdsStatement.java:703)
at net.sourceforge.jtds.jdbc.JtdsResultSet.next(JtdsResultSet.java:612)
at net.sourceforge.jtds.jdbc.JtdsResultSet.close(JtdsResultSet.java:503)
at TmpTest.main(TmpTest.java:118)

output for MS JDBC
Result 1 is an update count: 0 null //create
Result 2 is an update count: 1 null //insert 1
Result 3 is a ResultSet: SQLServerResultSet:1 //select
Result 4 is an update count: 1 null //insert 2
Result 5 is an error: com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#notexists'.
com.microsoft.sqlserver.jdbc.SQLServerException: Invalid object name '#notexists'.
at com.microsoft.sqlserver.jdbc.SQLServerException.makeFromDatabaseError(SQLServerException.java:216)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getNextResult(SQLServerStatement.java:1515)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:1213)
at com.microsoft.sqlserver.jdbc.SQLServerStatement.getMoreResults(SQLServerStatement.java:2068)
at TmpTest.main(TmpTest.java:45)

[Andrej Fink]

ps: tested with MSSQL2000 and MSSQL2012 express

[momo]

Thanks for reporting that issue. I don't see what's going on at a first glance. Please give me some (or some more, Christmas and such ;-) days for having a closer look.

But in general, executing multiple statements in a singe JDBC call may become relatively "fragile" (and sometimes it's simply not possible) to get correct. That being said, I'll do what I can to iron out problems of any kind whenever possible.

Cheers,
momo

[Andrej Fink]

I did better test - it shows now full range of exceptions ;-)

How to run it:
- change db name ~ line 80 (change JTDS=false if you want to test MS JDBC driver)
- javac TmpTest.java
- java -cp .;/where/jdbc/*; -Dlogin=YOUR_LOGIN -Dpwd=YOUR_PWD TmpTest

The worst part of situation is that SQLException is thrown in JtdsResultSet.next(JtdsResultSet.java:612) and/or JtdsResultSet.close(JtdsResultSet.java:503)
BUT actually ResultSet is fine, error is going later - in next statement or much later...

PS: Interesting reading
http://blogs.msdn.com/b/jdbcteam/archive/2008/08/04/why-doesn-t-executeupdate-give-me-the-exception-i-expected.aspx

http://blogs.msdn.com/b/jdbcteam/archive/2008/08/01/use-execute-and-getmoreresults-methods-for-those-pesky-complex-sql-queries.aspx

[momo]

Okay, I checked your test and can acknowledge the bug in jTDS, you reported. There is definitely something wrong with the driver. I'm currently trying to find out what exactly is going wrong. I fear the problem might be caused by changes done to support computed resultsets, but I'll have to do some more testing to be sure.

Cheers,
momo

[Andrej Fink]

For your convenience, I did stack traces for jtds 1.2.5, 1.3.0 and MS JDBC driver.

Don't get me wrong: I don't need these MS JDBC's "update count = 0" ;-), but:
- "java.lang.IllegalStateException: There should be no queued results." at JtdsResultSet.close
and
- SQLException of the next statement in JtdsResultSet.close/JtdsResultSet.next (when the resultSet has no problem) instead of Statement.getMoreResults() or Statemnet.getUpdateCount() are sad bugs...

[momo]

No no, I think I got your point. I don't really care too much for the first update count (this is something highly dependent on the server's response, so maybe we will never get that - at least I didn't see it in the server's response stream), but of course the SQLException is a problem. It simply is thrown at an inappropriate point.

Cheers and Merry Christmas,
momo