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: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • To: "FreeTDS Development Group" <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] Invalid cursor state when executing 2 SELECTstatements
  • Date: Wed, 19 Dec 2007 16:43:50 +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.
>

I prefer network trace (I used so lot that I can read easier that...)
however we would get same results.
I wrote another small test, attributes.c derived from descrivecol.c that
test for statement attributes. It's script based, so you can extend it
changing attributes.in
(http://freetds.cvs.sourceforge.net/freetds/freetds/src/odbc/unittests/a
ttributes.in?revision=1.1&view=markup) which currently contains

# some cursor attributes
odbc 3
reset

attr SQL_ATTR_CURSOR_TYPE SQL_CURSOR_FORWARD_ONLY
attr SQL_ATTR_CURSOR_SCROLLABLE SQL_NONSCROLLABLE
attr SQL_ATTR_CONCURRENCY SQL_CONCUR_READ_ONLY
attr SQL_ATTR_CURSOR_SENSITIVITY SQL_INSENSITIVE

set SQL_ATTR_CURSOR_SENSITIVITY SQL_SENSITIVE

attr SQL_ATTR_CURSOR_TYPE SQL_CURSOR_FORWARD_ONLY
attr SQL_ATTR_CURSOR_SCROLLABLE SQL_NONSCROLLABLE
attr SQL_ATTR_CONCURRENCY SQL_CONCUR_ROWVER
attr SQL_ATTR_CURSOR_SENSITIVITY SQL_SENSITIVE

in other works... check initial state of 4 attributes than change
sensitivity and test how these attributes change. This test pass using
MS ODBC so you can see:
- sensitivity is by default INSENSITIVE while our driver do not support
this attribute. I honestly don't know who to support it... place cursors
inside a transaction or use some flags? I don't see any flags under
http://jtds.sourceforge.net/apiCursors.html#_sp_cursoropen.
- MS ODBC transparently change concurrency from READ_ONLY to ROWVER...
interensting cause in our driver we correctly check if we have to use
cursors using

if (stmt->attr.cursor_type != SQL_CURSOR_FORWARD_ONLY ||
stmt->attr.concurrency != SQL_CONCUR_READ_ONLY)

Now... a step forward. As said there are no prepared cursors that is at
line 3004 we should have a similar check and similar code. Also we need
to add tds_cursorprepare and similar (not that difficult however...). I
think that _SQLExecute is becoming too long...


Oh my god, odbc.c is 3 weeks old! I think either
- I'm too busy doing other stuff
- I'm becaming less paranoid
- it's too stable!
(I think a mix of all...)


>
> 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
>

sensitivity not supported...

> For SELECT FOR UPDATE cursors (forward only):
>
> SQL_ATTR_CONCURRENCY, SQL_CONCUR_LOCK
>

supported but only for SQLExecDirect (SQLPrepare/SQLExecute see above)

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

Mmmm... probably they enable some flags calling sp_cursor** functions.

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

This should just make prepare returning success (and don't prepare) that
wait first SQLExecute for preparing. This help cause you are sure to
have parameters binded. I always recommend to bind parameters BEFORE
SQLPrepare, some SQLPrepare implementation require parameters and
prepare two time (one with dummy parameters and another with real
parameters).

> We always set the cursor name with:
>
> SQLSetCursorName(...) => that generates a
> sp_cursoroption call I believe.
>

yes, in odbc.c

tds_alloc_cursor(tds, tds_dstr_cstr(&stmt->cursor_name),
tds_dstr_len(&stmt->cursor_name), stmt->query, strlen(stmt->query));

cursor has names (and yes, it use sp_cursoroption, see
tds_cursor_setname in query.c)

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

Yes, there is support in 0.64... but if you have some tests feel free to
send. It seems to be small issues with 0.64 and multiple parameters
(there is a bug at SF)

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

I did some test with Native Client mainly to add TDS 9 support but your
tests success even with normal MS ODBC so I think they can wait... you
know, I'm only a man...

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page