FreeTDS Development Group

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

Frediano,

On the 12/13 you wrote that scrollable cursors are supported in 0.65:

ZIGLIO, Frediano, VF-IT wrote:
> > - No scrollable cursors??? (this is very old stuff - now?)
> Are supported by CVS. Currently we are next to a release (we hope).

Is this committed?

Attached a little ODBC sample using a scrollable cursor.

Tested with SQL Native Client (SQL Server 2005).

Output gives:

-------------------------------------------------------------------------
>> fetch  3          0 : 3 [ccc                 ]
>> fetch  4          0 : 2 [bbb                 ]
>> fetch  4          0 : 1 [aaa                 ]
>> fetch  4          0 : no data found
>> fetch  1          0 : 1 [aaa                 ]
>> fetch  1          0 : 2 [bbb                 ]
>> fetch  1          0 : 3 [ccc                 ]
>> fetch  1          0 : no data found
>> fetch  2          0 : 1 [aaa                 ]
>> fetch  1          0 : 2 [bbb                 ]
>> fetch  1          0 : 3 [ccc                 ]
>> fetch  5          3 : 3 [ccc                 ]
>> fetch  6         -2 : 1 [aaa                 ]
>> fetch  6         -2 : no data found
>> fetch  6          5 : no data found
-------------------------------------------------------------------------

But with FreeTDS 0.65, I get this:

Error -1 at: SQLFetchScroll
Diagnostic info:
  SQL State: HY106
  SQL code : 0
  Message  : [FreeTDS][SQL Server]Fetch type out of range

Using SQL_CURSOR_STATIC does not help:

SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) 
SQL_CURSOR_STATIC, 0);

Am I using the correct version?

Did I miss something?

Thank you.
Seb
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>

#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 int v_int_3;
static SQLINTEGER v_ind_3_1;

static char v_char_3[21];
static SQLINTEGER v_ind_3_2;

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

static void doFetch(SQLHSTMT m_hstmt, int dir, int pos)
{
    SQLRETURN rcode = SQLFetchScroll(m_hstmt,dir,pos);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt,"SQLFetchScroll");
    if (rcode != SQL_NO_DATA)
         fprintf(stdout,">> fetch %2d %10d : %d [%s]\n",dir,pos,v_ind_3_1?v_int_3:-1,v_ind_3_2?v_char_3:"null");
    else 
         fprintf(stdout,">> fetch %2d %10d : no data found\n",dir,pos);
}

main(int argc,char **argv)
{
    SQLRETURN rcode;
    SQLHENV m_henv;
    SQLHDBC m_hdbc;
    SQLHSTMT m_hstmt1;
    SQLHSTMT m_hstmt2;
    int i,j;
    const char * dbname;
    const char * usernm;
    const char * passwd;

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

    m_henv = NULL;
    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 = NULL;
    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");

    rcode = SQLSetConnectAttr(m_hdbc,
               SQL_ATTR_AUTOCOMMIT, (SQLPOINTER) SQL_AUTOCOMMIT_ON, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_ENV,m_henv,"SQLSetConnectAttr(autocommit)");

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

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

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table mytab1", SQL_NTS);
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table mytab1 (k int, c char(30))", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.1");

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab1 values (1,'aaa')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.2");
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab1 values (2,'bbb')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.3");
    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "insert into mytab1 values (3,'ccc')", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.4");

/*
    rcode = SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CURSOR_TYPE, (SQLPOINTER) SQL_CURSOR_STATIC, 0);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLSetStmtAttr 1");
*/

    rcode = SQLSetStmtAttr(m_hstmt2, SQL_ATTR_CURSOR_SCROLLABLE, (SQLPOINTER) SQL_SCROLLABLE, SQL_IS_UINTEGER);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLSetStmtAttr 1");

    rcode = SQLPrepare(m_hstmt2, (SQLCHAR *) "select k, c from mytab1 order by k", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLPrepare 3");

    rcode = SQLBindCol(m_hstmt2, 1, SQL_C_LONG, &v_int_3, 0, &v_ind_3_1);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLBindCol 3.1");

    rcode = SQLBindCol(m_hstmt2, 2, SQL_C_CHAR, v_char_3, sizeof(v_char_3), &v_ind_3_2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLBindCol 3.2");

    rcode = SQLExecute(m_hstmt2);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt2,"SQLExecute StmtH 3");

    doFetch(m_hstmt2, SQL_FETCH_LAST, 0);
    doFetch(m_hstmt2, SQL_FETCH_PRIOR, 0);
    doFetch(m_hstmt2, SQL_FETCH_PRIOR, 0);
    doFetch(m_hstmt2, SQL_FETCH_PRIOR, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_FIRST, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_NEXT, 0);
    doFetch(m_hstmt2, SQL_FETCH_ABSOLUTE, 3);
    doFetch(m_hstmt2, SQL_FETCH_RELATIVE, -2);
    doFetch(m_hstmt2, SQL_FETCH_RELATIVE, -2);
    doFetch(m_hstmt2, SQL_FETCH_RELATIVE, 5);

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

    rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table mytab1", SQL_NTS);
    CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 1.3");

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

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

    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");

}