Error : SQL error description: [Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt
Need help!
Hi Team,
when I am trying to execute 3 select statements in one ODBC connection i getting above error because of the multiple resultSet objects. The values from select statements resultSet will be used in another select statement where condition? So what is the best approach to address this issue as I need to execute them in an external database?
//Pull data from sql server.
void getDataFromSql()
{
lConStr = this.getConnStr();
if (!lConStr)
{
throw error("Database connection setup not found !!");
}
try
{
lloginProperty = new LoginProperty();
lloginProperty.setOther(lConStr);
lOdbcCon = new ODBCConnection(lloginProperty);
if(lOdbcCon)
{
lExportSQLQry = "SELECT * from ....";
//Assert permission for executing the sql string.
lSqlPermission = new SqlStatementExecutePermission(lExportSQLQry );
lSqlPermission.assert();
//Prepare the sql statement.
lstatement = lOdbcCon.createStatement();
gExportResultSet = lstatement.executeQuery(lExportSQLQry);
CodeAccessPermission::revertAssert();
lstatement.close();
//Cause the sql statement to run,
//then loop through each row in the result.
while (gExportResultSet.next())
{
lMasterSQLQry = "";
lMasterSQLQry += "SELECT * from....
//Assert permission for executing the sql string.
lSqlPermission = new SqlStatementExecutePermission(lMasterSQLQry);
lSqlPermission.assert();
//Prepare the sql statement.
lstatement = lOdbcCon.createStatement();
gMasterResultSet = lstatement.executeQuery(lShipMasterSQLQry);
CodeAccessPermission::revertAssert();
lstatement.close();
while(gMasterResultSet.next())
{
lDetailsSQLQry = "";
lDetailsSQLQry += "SELECT * from.....
//Assert permission for executing the sql string.
lSqlPermission = new SqlStatementExecutePermission(lDetailsSQLQry);
lSqlPermission.assert();
//Prepare the sql statement.
lstatement = lOdbcCon.createStatement();
gDetailsResultSet = lstatement.executeQuery(lDetailsSQLQry);
CodeAccessPermission::revertAssert();
lstatement.close();
while(DetailsResultSet.next())
{
XXXXXXXX
}
}
lSqlPermission = new SqlStatementExecutePermission(gCtrlMgrSQLQry);
lSqlPermission.assert();
lstatement = lOdbcCon.createStatement();
lstatement.executeUpdate(gCtrlMgrSQLQry);
CodeAccessPermission::revertAssert();
lstatement.close();
}
}
}
catch
{
if (lSqlPermission)
CodeAccessPermission::revertAssert();
if (lstatement)
lstatement.close();
throw error("Failed to transfer data to SQL server !!");
}
}
