[freetds] Invalid cursor state when executing 2 SELECTstatements

Sebastien FLAESCH sf at 4js.com
Tue Dec 18 06:06:00 EST 2007


Hi Frediano,

First, thanks a lot for looking at this cursor issue!

ZIGLIO, Frediano, VF-IT wrote:
 > I think I have to check how attributes change in MS ODBC... perhaps
 > setting SENSITIVITY change also CURSOR_TYPE ??

As I wrote in a previous mail, you should test all possible combinations
of SQL Statement handle attributes with the MS ODBC MDAC dribver, and see
in the SQL Server Profiler trace what sp_cursor* calls it generates.

Resource in SQL Server 2005 MSDN (you probably know about this link):

http://msdn2.microsoft.com/en-us/library/ms130794.aspx

...

We do use the following SQLSetStmtAttr() attributes:

For scrollable cursors:

     SQL_ATTR_CURSOR_SCROLLABLE, SQL_SCROLLABLE
     SQL_ATTR_CURSOR_SENSITIVITY, SQL_INSENSITIVE

For SELECT FOR UPDATE cursors (forward only):

     SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK

For normal cursors, to get "Fast-forward cursors" (MSSQL specific attribute):

     SQL_SOPT_SS_CURSOR_OPTIONS, SQL_CO_FFO

In some situations, to force server to prepare (MSSQL specific attribute):

     SQL_SOPT_SS_DEFER_PREPARE, SQL_DP_OFF

We always set the cursor name with:

     SQLSetCursorName(...)  => that generates a sp_cursoroption call I believe.

We do also use array-fetch / column-wise binding (SQL_ATTR_ROW_BIND_TYPE,
SQL_BIND_BY_COLUMN) but that's probably another topic...

...

As a side note you may also install / test with SQL Server 2005 and the new
SQL Native Client driver...

Bye
Seb

ZIGLIO, Frediano, VF-IT wrote:
>> Dear all,
>> (hope it helps to send such little test programs in this 
>> mailing list).
>>
>> Attached a little test program executing 2 statements concurrently.
>>
>> For now with FreeTDS 0.65 I get:
>>
>> Error -1 at: SQLExecute 2
>> Diagnostic info:
>>    SQL State: 24000
>>    SQL code : 0
>>    Message  : [FreeTDS][SQL Server]Invalid cursor state
>>
>> When I run the same program with SQL Native Client, I get 
>> following output:
>>
>>  >> Fetch from 1: [aaa]
>>  >> Fetch from 2: [aaa]
>>
>> Note that you must set a statement attribute like:
>>
>> SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE
>>
>> or:
>>
>> SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK
>>
>> ... to get sp_cursor* driven cursors, as you probably know.
>>
>> It's certainly a good idea to test all possible statement 
>> attributes you can
>> set with ODBC, and check what SQL Native Client sends to the server...
>>
>> There are a couple of sp_cursoroption calls you should take care of.
>>
>> Seb
>>
> 
> Well... 
> step 1. Add tests to out unittests... done, see
> http://freetds.cvs.sourceforge.net/freetds/freetds/src/odbc/unittests/?s
> ortby=date#dirlist
> step 2. Do tests work? No...
> step 2. See how tests works under Windows. I used MS ODBC (no Native)
> with mssql2k
> 
> start test cursor3.exe  
> odbctest
> --------
> 
> connection parameters:
> server:   'TESTSRV'
> user:     'test'
> password: '????'
> database: 'test'
> use test
>>> Fetch from 1: [aaa]
>>> Fetch from 2: [aaa]
> ok test cursor3.exe 
> start test cursor4.exe  
> odbctest
> --------
> 
> connection parameters:
> server:   'TESTSRV'
> user:     'test'
> password: '????'
> database: 'test'
> use test
>>> New value after update = [xxx] (should be [xxx]) 
> ok test cursor4.exe 
> start test cursor5.exe  
> odbctest
> --------
> 
> connection parameters:
> server:   'TESTSRV'
> user:     'test'
> password: '????'
> database: 'test'
> use test
>>> fetch  3          0 : 3 [ccc                 ]
>>> fetch  4          0 : 2 [bbb                 ]
>>> fetch  4          0 : 1 [aaa                 ]
>>> fetch  4          0 : no data found
>>> fetch  1          0 : 1 [aaa                 ]
>>> fetch  1          0 : 2 [bbb                 ]
>>> fetch  1          0 : 3 [ccc                 ]
>>> fetch  1          0 : no data found
>>> fetch  2          0 : 1 [aaa                 ]
>>> fetch  1          0 : 2 [bbb                 ]
>>> fetch  1          0 : 3 [ccc                 ]
>>> fetch  5          3 : 3 [ccc                 ]
>>> fetch  6         -2 : 1 [aaa                 ]
>>> fetch  6         -2 : no data found
>>> fetch  6          5 : no data found
> ok test cursor5.exe 
> 
> Now, why our tests fails... It seems that the combination you use to
> setup cursor is not accepted by out driver... problem 2 (problem 1 is
> prepared cursors).
> 
> I think I have to check how attributes change in MS ODBC... perhaps
> setting SENSITIVITY change also CURSOR_TYPE ??
> 
> freddy77
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 



More information about the FreeTDS mailing list