Skip to Content.
Sympa Menu

freetds - [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE

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: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE
  • Date: Wed, 09 Jan 2008 12:52:15 +0100

I am working now on SQLCancel() to interrupt long running queries.

The good news it that it's interruption the statement.

But I face a little problem:

With the native Microsoft MDAC and SQL Native Client ODBC driver,
I can detect if a query was interrupted with SQLCancel() by checking
the SQLSTATE register: If a statement is interrupted during SQLExecute,
the function returns with SQL_ERROR, then we check SQLSTATE with the
SQLGetDiagRec() function.

Native MS drivers return HY008 in the SQLSTATE register, but FreeTDS
does not (see below).

Note that MS Drivers don't return any native sqlerror (it's zero),
however, the message text says "Operation canceled"...

It's critical for us to detect SQL interruption, to set properly our
SQL error / status registers in our language, to let the programs
distinguish SQL interruption errors from other SQL errors... (we map
native SQL errors / SQLSTATE to Informix SQL errors).

In Genero BDL you would manage SQL interruption this way:

DEFER INTERRUPT
OPTIONS SQL INTERRUPT ON

DECLARE c1 CURSOR FOR SELECT ...
WHENEVER ERROR CONTINUE
OPEN c1
WHENEVER ERROR STOP
IF SQLCA.SQLCODE = -213 THEN
MESSAGE "Statement interrupted by user..."
RETURN 0
END IF
IF SQLCA.SQLCODE != 0 THEN
ERROR "SQL Error: ", SQLCA.SQLCODE
RETURN -1
END IF
FETCH c1
...

With attached sample, I get the following ouput:

>> Creating tab1...
>> ...done.
>> Select tab1... (this should take time...)

<< press control-C on send SIGINT signal to process >>

>>>> SQLCancel() ...
>>>> ... SQLCancel done rcode = 0
Error -1 at: SQLExecDirect 3
Diagnostic info:
SQL State: \uffff\uffff}
SQL code : 0
Message :


Many thanks,
Seb
/* Testing SQLCancel() */

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

    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