Skip to Content.
Sympa Menu

freetds - Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008SQLSTATE

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] SQLCancel() and error diagnostic: Expecting HY008SQLSTATE
  • Date: Tue, 15 Jan 2008 13:24:24 +0100

Ok, I could reproduce the new problem:

I don't get SQLSTATE=HY008 when using server-side cursors...

Try attached sample to reproduce.

Seb

Sebastien FLAESCH wrote:
Frediano,

I have just tested my little sample with the HEAD and I get an HY008 SQLSTATE as expected.

But I still have a problem: I do not get this SQLSTATE when using our Genero VM...

Attached the log ... (using default debug flags, tell me what I should set if needed).

...

Some details that may help:

Our VM is a pseudo-code interpretor where we handle user interruption in different ways.

In console / TUI mode:

When a SIGINT is catched, we just set a global indicator which is checked in the p-code interpreter loop.
=> we do not call directly SQLCancel() from the SIGINT handler.

In GUI mode:

We have a proprietary GUI protocol to connect presentation clients to application with sockets.
When the front-end waits for the application to respond (running SQL statement), it can still send a asynchronous event on the socket.
We use non-blocking select() and we detect SIGIO signals if need data is sent by front-ends.
In this can we do also set a global flag if we catch an interruption GUI event, and processed in pcode interpreter loop.
=> we do not call directly SQLCancel() from the SIGIO handler.

I have tested with TUI and GUI mode, in both cases I can interrupt, but SQLSTATE is 00000.

...

Would the TDS driver only set HY008 when SQLCancel() is called during a sighandler?

I guess no, according to the code you just check the TDS return code:

case TDS_CANCELLED:
odbc_errs_add(&stmt->errs, "HY008", NULL);

So I really wonder... I will investigate a bit more.

It's possible that my FreeTDS interface does more ODBC calls than the like sample I have send.


Thanks,
Seb

Sebastien FLAESCH wrote:
Thanks Frediano.

Will cvs update the HEAD and make some tests.

Seb

ZIGLIO, Frediano, VF-IT wrote:
Hello James,

First thanks a lot for taking the time to explain the internals.

Resources (just to compare):

With DB2 CLI, they don't talk about SIGINT, but rather multi-threaded applications where a second thread issues SQLCancel().

http://webdocs.caspur.it/ibm/db2/8.1/doc/htmlcd/en_US/ad/r0000567.htm

With Oracle OCI, they talk about Control-C:

http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10779
/oci02bas.htm#462655

Of course, nothing about SIGINT on MSDN:

http://msdn2.microsoft.com/en-us/library/ms714112.aspx

...

I can probably use any solution you will provide to handle SQL interruption.
But I believe there is a good chance to have other ODBC programmers handle SQL interruption with a SIGINT + SQLCancel() as I did.
Therefore, you better choose the second "robust" solution.

Currently CVS HEAD seems to handle all situations correctly.

Note BTW that several SIGINT signals can be generated successively: Frustrated users keeping CONTROL-C pressed down ;-).
=> Many SQLCancel() calls ...

First send cancel than it wait for cancel handling by server. So others
SQLCancel just do nothing.

I wonder however why the TDS protocol does not yet specify how to handle query interruption.
To me this should be part of the protocol definition (= you should not have to choose between different solutions)

...

??? it calls cancellation.

Looks like you have to do tricky things (cleaning up the connection) to handle this... right?

Just a remark:

Maybe SQLCancel() (query interruption in TDS) is only supported since SQL Server since version 2005?

I just remember that we started to support SQL interruption with SQL Server native ODBC drivers since 2005 only.
Quite sure it was not working before that version... but must double check.

cancellation is supported even by protocol 4.2.

I can only suggest to try my little SQLCancel() example with SQL Server 2005 and sniff the protocol.
See odbc cancel test. I use alarm to emulate ctrl-c

But it must be adapted to catch console events to trap CTRL-C (does not with with SIGINT of course)
I will try to adapt the sample and send it back.

Thanks!
Seb

The bad thing... it seems that if test is executed using unixODBC it
fails. Our SQLCancel is not called (that is you call SQLCancel and
unixODBC do not call driver one... it hangs). I use unixODBC 2.2.11,
I'll test with 2.2.12


freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds


_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds



------------------------------------------------------------------------

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

/* Testing SQLCancel() with server-side cursor */

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>
#include <stdio.h>
#include <time.h>
#include <sys/time.h>
#include <signal.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); \
}

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

static SQLHSTMT m_hstmt1;

static void sigint_handler(int s)
{
int rcode;
printf(">>>> SQLCancel() ...\n");
rcode = SQLCancel(m_hstmt1);
printf(">>>> ... SQLCancel done rcode = %d\n", rcode);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLCancel failed");
signal(SIGINT, sigint_handler);
}

main(int argc,char **argv)
{
SQLRETURN rcode;
SQLHENV m_henv;
SQLHDBC m_hdbc;
int i,j;
const char * dbname;
const char * usernm;
const char * passwd;
int rows;
SQLINTEGER ind;

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

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

m_hstmt1 = 0;
rcode = SQLAllocHandle(SQL_HANDLE_STMT, m_hdbc, &m_hstmt1);
CHECK_RCODE(SQL_HANDLE_DBC,m_hdbc,"SQLAllocHandle StmtH 1.1");

rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "drop table tab1", SQL_NTS);
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "create table tab1 ( k int,
vc varchar(200) )", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect create table 1");

rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SCROLLABLE,
(SQLPOINTER) SQL_NONSCROLLABLE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr
SQL_ATTR_CURSOR_SCROLLABLE");
rcode = SQLSetStmtAttr(m_hstmt1, SQL_ATTR_CURSOR_SENSITIVITY,
(SQLPOINTER) SQL_SENSITIVE, SQL_IS_UINTEGER);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLSetStmtAttr
SQL_ATTR_CURSOR_SENSITIVITY");

printf(">> Creating tab1...\n");
rcode = SQLPrepare(m_hstmt1, (SQLCHAR *) "insert into tab1 values ( ?,
'aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa' )", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLPrepare 1");
rcode = SQLBindParameter(m_hstmt1, 1, SQL_PARAM_INPUT, SQL_C_LONG,
SQL_INTEGER, 0, 0, &i, 0, &ind);
for( i=1; i<=2000; i++ ) {
rcode = SQLExecute(m_hstmt1);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecute 1");
}
printf(">> ...done.\n");

signal(SIGINT, sigint_handler);
printf(">> Select tab1...\n");
rcode = SQLExecDirect(m_hstmt1, (SQLCHAR *) "select max(p1.k) from tab1
p1, tab1 p2, tab1 p3, tab1 p4, tab1 p5", SQL_NTS);
CHECK_RCODE(SQL_HANDLE_STMT,m_hstmt1,"SQLExecDirect 3");
printf(">> ... done rcode = %d\n", rcode);
signal(SIGINT, NULL);

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

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