freetds AT lists.ibiblio.org
Subject: FreeTDS Development Group
List archive
Re: [freetds] Invalid cursor state when executing 2 SELECT statements
- From: Sebastien FLAESCH <sf AT 4js.com>
- To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
- Subject: Re: [freetds] Invalid cursor state when executing 2 SELECT statements
- Date: Mon, 17 Dec 2007 12:09:12 +0100
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");
}
-
[freetds] Invalid cursor state when executing 2 SELECT statements,
Sebastien FLAESCH, 12/13/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECT statements,
Sebastien FLAESCH, 12/17/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
ZIGLIO, Frediano, VF-IT, 12/18/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
Sebastien FLAESCH, 12/18/2007
- Re: [freetds] Invalid cursor state when executing 2 SELECTstatements, ZIGLIO, Frediano, VF-IT, 12/19/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
ZIGLIO, Frediano, VF-IT, 12/21/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
James K. Lowden, 12/21/2007
- Re: [freetds] Invalid cursor state when executing 2 SELECTstatements, Sebastien FLAESCH, 12/21/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
James K. Lowden, 12/21/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
Sebastien FLAESCH, 12/18/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECTstatements,
ZIGLIO, Frediano, VF-IT, 12/18/2007
-
Re: [freetds] Invalid cursor state when executing 2 SELECT statements,
Sebastien FLAESCH, 12/17/2007
Archive powered by MHonArc 2.6.24.