FreeTDS Development Group

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

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

}