freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
- From: Sebastien FLAESCH <sf AT 4js.com>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: [freetds] ODBC scrollable cursors in 0.65
- Date: Mon, 17 Dec 2007 11:39:46 +0100
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");
}
-
[freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
ZIGLIO, Frediano, VF-IT, 12/13/2007
- [freetds] ODBC scrollable cursors in 0.65, Sebastien FLAESCH, 12/17/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
James K. Lowden, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
- Re: [freetds] SQL Server 2005 / 2008 support, Frediano Ziglio, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
- Re: [freetds] SQL Server 2005 / 2008 support, James K. Lowden, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Reid, Roger L., 12/13/2007
- Re: [freetds] SQL Server 2005 / 2008 support, James K. Lowden, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] SQL Server 2005 / 2008 support,
ZIGLIO, Frediano, VF-IT, 12/13/2007
Archive powered by MHonArc 2.6.24.