Skip to Content.
Sympa Menu

freetds - Re: [freetds] Invalid cursor state when executing 2 SELECTstatements

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Sebastien FLAESCH <sf AT 4js.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Invalid cursor state when executing 2 SELECTstatements
  • Date: Tue, 18 Dec 2007 12:06:00 +0100

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






Archive powered by MHonArc 2.6.24.

Top of Page