SQL Exception Connection is busy with results for another hstmt.

Hi Ranchers

I am running a Java application which has multiple sql queries including update and insert.Basically I am connecting to a MS Sql Server.

But I am getting the following error.

Error Details - [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.

I am getting this error only when my Jave application is accessing the database to do this update/insert. At the same time when
another application is updating the same database.

Please let me know if I need to post my piece of code which has the JDBC operations or this error is due to the database contention when two apps working on the same table.

Can you please let me know a work around for this kind of sql exception.

Thanks in advance
Bhazee

Prem,
Are you using different connection objects for the concurrent queries?

Yes,
I am using different connection objects for the concurrent queries and I am also closing both the connection objects.

Bhazee

can your share your snippet of code (which has JDBC Operation).

Hi,
First of all thanks for your time. Here is the JDBC snippet.

Connection objConnection = getConnection();
Connection objConnectiontwo = getConnection();
objConnection.setAutoCommit(false);
objConnectiontwo.setAutoCommit(false);
Statement objStatement = null;
Statement objStatementtwo = null;
ResultSet objResultSet = null;
 
try {

strQuery = "SELECT  A "
+ "FROM Table A,Table B "
+ "Where A.Req = B.Req" 
+ "' ORDER BY A.TS desc ";
           //Here I will be taking only the most recent record from the //above query
objStatement = objConnection.createStatement();
objResultSet = objStatement.executeQuery(strQuery);
if (objResultSet != null) {
if (objResultSet.next()) {
strA = getEmptyString(objResultSet
.getString("A"));
}
}

if (strA != null && !strA.trim().equals("")) {
strB = "UPDATE Query for Table C with strA
objStatement =objConnection.createStatement();
objStatement.executeUpdate(strB);

strQueryC = "SELECT from Table D with strA;
 objStatement = objConnection.createStatement();
objResultSet = objStatement.executeQuery(strQueryC);
// If the select query finds a row then it will update
//else it will insert strA

if (objResultSet != null && objResultSet.next()) {
strSQLPRN = "UPDATE Table D with strA
objStatementtwo = objConnectiontwo.createStatement();
objStatementtwo.executeUpdate(strSQLPRN);
 
} else {
strQueryPRN= "Insert Table D with strA
objStatementtwo = objConnectiontwo.createStatement();
objStatementtwo.executeUpdate(strQueryPRN);
 
} 
 
} 
objConnection.commit();
objConnectiontwo.commit();
} catch (SQLException sqlException) {
if (objConnection != null) {
objConnection.rollback();
}
if (objConnectiontwo != null) {
objConnectiontwo.rollback();
}
sqlException.printStackTrace();
throw new Exception(sqlException.getMessage());
 
} catch (Exception e) {
if (objConnection != null) {
objConnection.rollback();
}
if (objConnectiontwo != null) {
objConnectiontwo.rollback();
}
e.printStackTrace();
throw new Exception(e.getMessage());
}
 
finally {
try {
if (objResultSet != null) {
objResultSet.close();
}

if (objStatement != null) {
objStatement.close();
}
if (objStatementtwo != null) {
objStatementtwo.close();
}
 
if (objConnection != null) {
objConnection.close();
}
if (objConnectiontwo != null) {
objConnectiontwo.close();
}
 
} catch (SQLException ex) {

ex.printStackTrace();
 
} catch (Exception ex1) {
ex1.printStackTrace();
 
}
 
}
}

Please let me know if you need more details.
Thanks
Bhazee

try to commit your first connection object before creating another one.

think it works. (not sure)

Hi
But I need to commit only when all the queries are executed successfully or else rollback both.

Thanks
Bhazee

what if? if you use the same connection Object for this transaction.

Originally posted by Prem Bhazee:
Yes,
I am using different connection objects for the concurrent queries and I am also closing both the connection objects.

Bhazee

Are you sure?
You call getConnection(), but where do you check that it returns two different instances?