Problem:
Using Sql Server and getting error message "Connection is busy with results for another hstmt" .
which is caused when using Firehose cursors (client-side). Need to force server side cursors in my application.
Resolution:
Although client-side cursors are much faster than server-side cursors there is the SQL Server limitation that you can only have one client-side cursor open per connection. Microsoft introduced fastfoward readonly cursors to alleviate the limitation of working with firehose cursors. To force a server side cursor you can set scrolloption or use the new SQL compiler directive BEHAVIOR specifying primitive options to force a fastfoward readonly (server-side) cursor. In either instance the following sql compiler directive is required SQL(TARGETDB=MSSQLSERVER).
1. setting scrolloption
EXEC SQL SET SCROLLOPTION FASTFORWARD END-EXEC
or specifying it in DECLARE CURSOR statement
EXEC SQL DECLARE CSR1 FASTFORWARD CURSOR FOR .... END-EXEC
2. Using SQL compiler directive BEHAVIOR=[MAINFRAME | ASCII]
Use primitive value RO_CURSOR=FF. This directive determines what type of database cursor your COBOL read only cursors use, FF=FASTFORWARD.
example: sql(dbman=odbc, targetdb=mssqlserver, behavior=mainframe, ro_cursor=FF)
For additional information see Net Express on-line help for the SET and DECLARE CURSOR syntax and compiler directive BEHAVIOR and RO_CURSOR.
