[freetds] Forcing usage of sp_cursorprepare / sp_cursorexecute system procedures with SQL Server

Sebastien FLAESCH sf at 4js.com
Thu Dec 13 11:02:09 EST 2007


Attached a test case.

Hope you can adapt to your regression tests.

Tested with SQL Native Client, uses sp_cursor* procedures to prepare
and execute the SELECT FOR UPDATE statement.

As a side note you probably should have a look at this:

http://technet.microsoft.com/en-us/library/ms187088.aspx

There are other SQLSetStmtAttr() options we are using that should be
supported by FreeTDS. Here is a piece of our code that gives you an
idea of what we need:


static int setCursorAttributes(SqlStatement * st, int dpoff)
{
     /* Warning: Force usage of server cursors to have no pending results. */
     SQLRETURN r;
     if (st->scroll) { /* SCROLLABLE CURSOR */
         r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_CURSOR_SCROLLABLE,
                            (SQLPOINTER) SQL_SCROLLABLE, SQL_IS_UINTEGER);
         if (!CHECK_SQLRETURN(r)) {
             DBGMSG(1, "Could not set SQL_ATTR_CURSOR_SCROLLABLE");
             return -1;
         }
         r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_CURSOR_SENSITIVITY,
                            (SQLPOINTER) SQL_INSENSITIVE, SQL_IS_UINTEGER);
         if (!CHECK_SQLRETURN(r)) {
             DBGMSG(1, "Could not set SQL_ATTR_CURSOR_SENSITIVITY");
             return -1;
         }
     } else if (st->stmtType == SQLST_SELFUP) { /* SELECT FOR UPDATE */
         r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_CONCURRENCY,
                            (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
         if (!CHECK_SQLRETURN(r)) {
             DBGMSG(1, "Could not set SQL_ATTR_CONCURRENCY");
             return -1;
         }
     } else {                    /* Use fast forward cursor */
         r = SQLSetStmtAttr(st->stmtHandle, SQL_SOPT_SS_CURSOR_OPTIONS,
                            (SQLPOINTER) SQL_CO_FFO, SQL_IS_UINTEGER);
         if (!CHECK_SQLRETURN(r)) {
             DBGMSG(1, "Could not set SQL_SOPT_SS_CURSOR_OPTIONS");
             return -1;
         }
     }
     if (dpoff) {
         /* Force statement to be sent to the server to check errors. */
         r = SQLSetStmtAttr(st->stmtHandle, SQL_SOPT_SS_DEFER_PREPARE,
                            (SQLPOINTER) SQL_DP_OFF, SQL_IS_UINTEGER);
         if (!CHECK_SQLRETURN(r)) {
             DBGMSG(1, "Could not set SQL_SOPT_SS_DEFER_PREPARE");
             return -1;
         }
     }
     /* Array fetch */
     if (st->stmtType == SQLST_SELFUP || st->scroll) {
         st->arraySize = 1;
         DBGMSG(3, "Fetch array size set to 1");
     } else {
         st->arraySize = st->connection->prefetchRows;
         DBGMSGINT(3, "Fetch array size = %d", (int) st->arraySize);
     }
     r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_ROW_BIND_TYPE,
                        (SQLPOINTER) SQL_BIND_BY_COLUMN, SQL_IS_UINTEGER);
     if (!CHECK_SQLRETURN(r)) {
         DBGMSG(1, "Could not set SQL_ATTR_ROW_BIND_TYPE");
         return -1;
     }
     r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_ROW_ARRAY_SIZE,
                        (SQLPOINTER) st->arraySize, SQL_IS_UINTEGER);
     if (!CHECK_SQLRETURN(r)) {
         DBGMSG(1, "Could not set SQL_ATTR_ROW_ARRAY_SIZE");
         return -1;
     }
     r = SQLSetStmtAttr(st->stmtHandle, SQL_ATTR_ROWS_FETCHED_PTR,
                        (SQLPOINTER) & (st->rowsFetched), SQL_IS_UINTEGER);
     if (!CHECK_SQLRETURN(r)) {
         DBGMSG(1, "Could not set SQL_ATTR_ROWS_FETCHED_PTR");
         return -1;
     }
     return 0;
}


Seb

ZIGLIO, Frediano, VF-IT wrote:
>> Dear all,
>>
>> In our Native SQL Server interface using ODBC (MDAC), we used to set
>> SQL statement attributes like:
>>
>> r = SQLSetStmtAttr(stmtHandle, SQL_ATTR_CONCURRENCY,
>>                      (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
>>
>> ... to force the client to use sp_cursorprepare + sp_cursorexecute
>> system procedures, and get better control...
>>
>> The sp_cursor* procedures do for example support "SELECT ... 
>> FOR UPDATE"
>> statements, that are apparently not accepted by sp_prepare / 
>> sp_execute.
>>
>> I could not find any sp_cursorprepare / sp_cursorexecute text in the
>> sources so I guess this is not possible with FreeTDS...
>>
> 
> I just realize you are right :(.
> Could you please provide a simple test to help us finishing this stuff?
> I recently like to work test-driven.
> I think we can get support for this stuff in a couple of weeks. Current
> CVS support cursors and multiple statements (using cursors) but it seems
> that prepared cursors are not supported.
> 
> freddy77
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
> 

-------------- next part --------------
A non-text attachment was scrubbed...
Name: odbctest13.c
Type: text/x-csrc
Size: 5494 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20071213/f6501aa3/attachment.bin 


More information about the FreeTDS mailing list