I have written a stored procedure in SQL2005 that returns a resultset. I am calling this procedure from AX4 from a class that is used in different parts of the application
The code i’m using to call the resultset is
[code] connection = new UserConnection();
statement = Connection.createStatement();
sqlStatement = strfmt(‘exec usp_findTickets %1, %2, %3, ‘%4’, ‘%5’, ‘%6’’,
//parameters );
perm = new SqlStatementExecutePermission(sqlStatement );
perm.assert();
resultSet = statement.executeQuery(sqlStatement);
while ( ResultSet.next() )
{
// do something with results
}
CodeAccessPermission::revertAssert();
rcmAdvDiscTable = RCMAdvDiscTable::find(discountId);
[/code]
this seemed to work at first, but later on i discovered when called from a batchprocess there was an error thrown on the last line in above code-block:
[Microsoft][ODBC SQL Server Driver]Connection is busy with results for another hstmt.
The obvious conclusion was the cursor for retrieving data through the executeQuery wasn’t closed correctly. I’ve tried several things resulting in adding
[code]
resultSet.close();
resultSet.notifyAll();
connection.notifyAll();[/code]
before the last line of code. It solved the problem for the first error message, but gives me a new error
[Microsoft][ODBC SQL Server Driver]Invalid cursor state.
a little further in the process. This error is thrown on the line with resultset.next().
I’m getting a bit desperate as to the proper way to call a stored procedure, work with the resultset and close it properly again. How can I solve the issues that either cursors are open when they shouldn’t or closed when they should be open? Usable both when called from client or executed through batch.