FreeTDS Development Group

["ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>]

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