FreeTDS Development Group

[Sebastien FLAESCH <sf AT 4js.com>]

Dear all,
(hope it helps to send such little test programs in this mailing list).

Attached a little test program executing 2 statements concurrently.

For now with FreeTDS 0.65 I get:

Error -1 at: SQLExecute 2
Diagnostic info:
  SQL State: 24000
  SQL code : 0
  Message  : [FreeTDS][SQL Server]Invalid cursor state

When I run the same program with SQL Native Client, I get following output:

>> Fetch from 1: [aaa]
>> Fetch from 2: [aaa]

Note that you must set a statement attribute like:

SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE

or:

SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK

... to get sp_cursor* driven cursors, as you probably know.

It's certainly a good idea to test all possible statement attributes you can
set with ODBC, and check what SQL Native Client sends to the server...

There are a couple of sp_cursoroption calls you should take care of.

Seb

Sebastien FLAESCH wrote:
> Dear all,
>
> I just want to:
>
>     SQLPrepare(stmt1, "SELECT ...");
>     SQLPrepare(stmt2, "SELECT ...");
>     SQLExecute(stmt1);
>     SQLExecute(stmt2);
>
> But second SQLExecute() returns "Invalid cursor state".
>
> I must either issue an SQLFetch() or SQLCloseCursor() with the first statement
> to be able to execute the second one.
>
> Is this a known limitation of the FreeTDS ODBC driver?
>
> Thanks
> Seb
> _______________________________________________
> FreeTDS mailing list
> FreeTDS AT lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds

/* Tests 2 active statements */

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); \
   }

static void 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;
    SQLHSTMT m_hstmt2;
    const char * dbname;
    const char * usernm;
    const char * passwd;
    char buff[64];
    SQLINTEGER 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')");
    exec_direct(1, "INSERT INTO t1 VALUES (2, 'bbbbb')");
    exec_direct(1, "INSERT INTO t1 VALUES (3, 'ccccccccc')");
    exec_direct(1, "INSERT INTO t1 VALUES (4, 'dd')");

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

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

/*
    rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"Set attribute SQL_ATTR_CURSOR_SCROLLABLE 1");
*/
    rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"Set attribute SQL_ATTR_CURSOR_SENSITIVITY 1");

/*
    rcode = SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"Set attribute SQL_ATTR_CURSOR_SCROLLABLE 2");
*/
    rcode = SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CURSOR_SENSITIVITY, (SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"Set attribute SQL_ATTR_CURSOR_SENSITIVITY 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 1");

    rcode = SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CONCURRENCY, (SQLPOINTER) SQL_CONCUR_LOCK, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"Set attribute SQL_ATTR_CONCURRENCY 2");
*/

    rcode = SQLSetCursorName( m_hstmt1, "c1", SQL_NTS );
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SetCursorName c1");

    rcode = SQLSetCursorName( m_hstmt2, "c2", SQL_NTS );
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SetCursorName c2");
 
    rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "SELECT * FROM t1", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"Prepare 1");

    rcode = SQLPrepare(m_hstmt2, (SQLCHAR *) "SELECT * FROM t1", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"Prepare 2");

    rcode = SQLExecute(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 1");

    rcode = SQLExecute(m_hstmt2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLExecute 2");

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

    rcode = SQLGetData(m_hstmt1, 2, SQL_C_CHAR, (SQLPOINTER) buff, sizeof(buff), &ind);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLGetData 1");
    fprintf(stdout,">> Fetch from 1: [%s]\n", buff);

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

    rcode = SQLGetData(m_hstmt2, 2, SQL_C_CHAR, (SQLPOINTER) buff, sizeof(buff), &ind);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLGetData 2");
    fprintf(stdout,">> Fetch from 2: [%s]\n", buff);
 
    rcode = SQLCloseCursor(m_hstmt1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCloseCursor 1");

    rcode = SQLCloseCursor(m_hstmt2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLCloseCursor 2");

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

    rcode = SQLFreeHandle(SQL_HANDLE_STMT, (SQLHANDLE) m_hstmt2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"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");
}