Skip to Content.
Sympa Menu

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

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] Forcing usage of sp_cursorprepare / sp_cursorexecute system procedures with SQL Server
  • Date: Thu, 13 Dec 2007 17:02:09 +0100

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


/* Test sp_cursorprepare / sp_cursorexecute usage to support SELECT FOR UPDATE
 * This test compiles and works fine with SQL Server Native Client, and uses
 * the sp_cursor* AIP Server Cursors ...
 */

static const char * g_dbname = "msvtest1_nc";
static const char * g_usernm = "msvuser";
static const char * g_passwd = "fourjs";

#ifdef _WIN32
#include <WINDOWS.H>
#endif
#include <stdio.h>
#include <sql.h>
#include <sqlext.h>

static SQLHENV m_henv;
static SQLHDBC m_hdbc;

#define CHECK_RCODE(t,h,m) \
   if ( rcode != SQL_NO_DATA \
     && rcode != SQL_SUCCESS \
     && rcode != SQL_SUCCESS_WITH_INFO  \
     && rcode != SQL_NEED_DATA ) { \
      fprintf(stderr,"Error %d at: %s\n",rcode,m); \
      getErrorInfo(t,h); \
      exit(1); \
   }

int getErrorInfo(SQLSMALLINT sqlhdltype, SQLHANDLE sqlhandle)
{
    SQLRETURN rcode = 0;
    SQLCHAR sqlstate[SQL_SQLSTATE_SIZE + 1];
    SQLINTEGER naterror = 0;
    SQLCHAR msgtext[SQL_MAX_MESSAGE_LENGTH + 1];
    SQLSMALLINT msgtextl = 0;
    int ifxerror = 0;
    rcode = SQLGetDiagRec((SQLSMALLINT) sqlhdltype,
                          (SQLHANDLE) sqlhandle,
                          (SQLSMALLINT) 1,
                          (SQLCHAR *) sqlstate,
                          (SQLINTEGER *) & naterror,
                          (SQLCHAR *) msgtext,
                          (SQLSMALLINT) sizeof(msgtext),
                          (SQLSMALLINT *) & msgtextl);
    fprintf(stderr, "Diagnostic info:\n");
    fprintf(stderr, "  SQL State: %s\n", (char *) sqlstate);
    fprintf(stderr, "  SQL code : %d\n", (int) naterror);
    fprintf(stderr, "  Message  : %s\n", (char *) msgtext);
}

void exec_direct(int check, const char * stmt)
{
    SQLRETURN rcode;
    SQLHSTMT stmth = 0;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hdbc, (SQLHANDLE *) & stmth);
    CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLAllocHandle");
    rcode = SQLExecDirect(stmth, (SQLCHAR *) stmt, SQL_NTS);
    if (check) {
        CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLExecDirect");
    }
    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) stmth);
    CHECK_RCODE(SQL_HANDLE_STMT,stmth,"SQLFreeHandle");
}

main(int argc,char **argv)
{
    SQLRETURN rcode;
    SQLHSTMT m_hstmt1;
    const char * dbname;
    const char * usernm;
    const char * passwd;
    char buff[64];
    int ind;

    if (argc == 4) {
       dbname = argv[1];
       usernm = argv[2];
       passwd = argv[3];
    } else {
       dbname = g_dbname;
       usernm = g_usernm;
       passwd = g_passwd;
    }

    rcode = SQLAllocHandle(SQL_HANDLE_ENV, SQL_NULL_HANDLE, &m_henv);
    CHECK_RCODE(SQL_HANDLE_ENV,NULL,"SQLAllocHandle EnvH");

    rcode = SQLSetEnvAttr(m_henv, SQL_ATTR_ODBC_VERSION, (SQLPOINTER) SQL_OV_ODBC3, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"ODBC V3");

    m_hdbc = 0;
    rcode = SQLAllocHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_henv, (SQLHANDLE *) &m_hdbc);
    CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLAllocHandle DbcH");

    rcode = SQLConnect((SQLHANDLE) m_hdbc,
                       (SQLCHAR *) dbname,
                       (SQLINTEGER) SQL_NTS,
                       (SQLCHAR *) usernm,
                       (SQLINTEGER) SQL_NTS,
                       (SQLCHAR *) passwd,
                       (SQLINTEGER) SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLConnect");

    exec_direct(0, "DROP TABLE t1");
    exec_direct(1, "CREATE TABLE t1 ( k INT, c VARCHAR(20))");
    exec_direct(1, "INSERT INTO t1 VALUES (1, 'aaa')");

    m_hstmt1 = NULL;
    rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle 2");

    rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"Set attribute SQL_ATTR_CONCURRENCY");

    rcode = SQLSetCursorName( m_hstmt1, "c112", SQL_NTS );
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SetCursorName c112");
 
    rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "SELECT * FROM t1 FOR UPDATE", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"Prepare 2");

    exec_direct(1, "BEGIN TRANSACTION");
    
    rcode = SQLExecute(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 2");

    rcode = SQLFetch(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFetch 1");
 
    exec_direct(1, "UPDATE t1 SET c = 'xxx' WHERE CURRENT OF c112");
    
    rcode = SQLCloseCursor(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCloseCursor 2");

    exec_direct(1, "COMMIT TRANSACTION");

    rcode = SQLExecDirect(m_hstmt1, "SELECT c FROM t1 WHERE k = 1", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 2");

    rcode = SQLFetch(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFetch 2");

    rcode = SQLGetData(m_hstmt1, 1, SQL_C_CHAR, buff, sizeof(buff), &ind);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLGetData");

    fprintf(stdout,">> New value after update = [%s] (should be [xxx]) \n", buff);

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLFreeHandle 2");

    exec_direct(1, "DROP TABLE t1");

    rcode = SQLDisconnect(m_hdbc);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLDisconnect");

    rcode = SQLFreeHandle(SQL_HANDLE_DBC, (SQLHANDLE) m_hdbc);
    CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLFreeHandle DbcH");

    rcode = SQLFreeHandle(SQL_HANDLE_ENV, (SQLHANDLE) m_henv);
    CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLFreeHandle EnvH");
}



Archive powered by MHonArc 2.6.24.

Top of Page