File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
http://aspose.com/file-tools
The moose likes JDBC and the fly likes [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login


Win a copy of Android Security Essentials Live Lessons this week in the Android forum!
JavaRanch » Java Forums » Databases » JDBC
Bookmark "[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt" Watch "[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt" New topic
Author

[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

Mark Henryson
Ranch Hand

Joined: Jul 11, 2005
Posts: 200
Hi,
I am using MicroSoft SQL Server 2000 with Java. I am encountering one error:
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt

I am using globally one connection, statement and resultset and I am closing the connection, resultset and statement properly (to my knowledge!!)

What are the cases in which these type of errorw will come? How to solve this?

Actually I am using the select query, based upon the resultset , i'm executing some other query. Like this, it will goes to three to four queries. Based upon the select queris, i am inserting a record and updating a record.
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
I am closing the connection, resultset and statement properly (to my knowledge!!)


If you are seeing this error, one of your ResultSets is not closed.

You may have calls to Statement.close() and ResultSet.close(), but are they executing? Review your code looking for places where returning from a method or exceptions may result in skipping the calls to close(). Putting the calls to close() in a finally block will ensure they are executed even if an exception is thrown.
Mark Henryson
Ranch Hand

Joined: Jul 11, 2005
Posts: 200
I am given the following codes, which I am working on Microsoft SQL Server with Java, where I am facing this error:
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Please go through the sample coding:


And another issue, which I want to discuss is: I want a unique Id, which I should generate manually. For 1 second, ten threads will hit at the same time and each should have a uinque id. I am using one method which is static and synchronized from which I am getting the unique id which will be inserted into the db. The field which i am inserting the ata is primary.
Even though I am using static and synchronized method and also I am able to get the unique id, but when inserting into db, duplication comes.
How to generate the unique id for this type of issue?
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
Put logging just after each Statement.close() or Recordset.close() and just before you create a RecordSet or Statement.

Run the program and match up each constructor log entry with each close log entry. You will find that you do not have a close() for every RS/Stmt.

The code is difficult to follow as you are reusing the connection and statements. They are opened in one method, then another method closes the statement and reopens it. I recommend refactoring your code so that resources are closed in the same method that opens them. (Create a statement, use it and close it all in the same method.) That way it'll be much easier to keep track of what is happening with your resources.

As for generating the unique id, use an IDENTITY column to have SQL Server generate the id. For more information, search the SQL Server Books Online for "IDENTITY Property".
Mark Henryson
Ranch Hand

Joined: Jul 11, 2005
Posts: 200
Hi,
I have given the coding below, where I am getting the stmt in one method and closed it in the same method(doInsertUpdateDelete).
What I am trying to do is: I am generating the unique serial number, where 10 threads are accessing simultaeously and store it in the DB. I want to store the serial number (10001 - 100010), without any duplication, since it is primary.

1. Why I am getting this error:
"[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt"

2. Why I am not able to generate the unique serial, though serial number is generated in the synchorinsed method.

3. How to do this? Give some idea on it:


EXCEPTION:
==========
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerExceptionSerialNum :10002
SerialNum :10010

at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.sql.SQLException: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmtSerialNum :10010

at sun.jdbc.odbc.JdbcOdbc.createSQLException(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.standardError(Unknown Source)
at sun.jdbc.odbc.JdbcOdbc.SQLExecDirect(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.execute(Unknown Source)
at sun.jdbc.odbc.JdbcOdbcStatement.executeUpdate(Unknown Source)
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:124)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerExceptionSerialNum :10010

at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
java.lang.NullPointerException
at com.java.thread.TranRunnable.doInsertUpdateDelete(TranThread.java:137)
at com.java.thread.TranRunnable.run(TranThread.java:29)
at java.lang.Thread.run(Unknown Source)
SerialNum :10010
SerialNum :10010
SerialNum :10010
SerialNum :10010
SerialNum :10010
SerialNum :10010
Scott Johnson
Ranch Hand

Joined: Aug 24, 2005
Posts: 518
There is only one instance of TranRunnable therefore there is only one Connection object being shared between all of the Threads. You are trying to run several sqls simultaneously on the same Connection.

Also, your line that prints the serial number:

System.out.println("SerialNum :"+SerialNum);

is referencing the static SerialNum field which is shared by (and overwritten by) all Threads. (In the above output, by the time you get to the println() call, all threads have incremented the static field so you see 10010.)
[ July 26, 2006: Message edited by: Scott Johnson ]
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
 
Similar Threads
Connection is busy with results for another hstmt
Can you use nested sql statements in jsp??
Recursive DB queries in a Stateless Session Bean
SQL Exception Connection is busy with results for another hstmt.
Connection Busy with another result hstmt Error - updated (SOLVED WITH JTDS)