[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