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

Sebastien FLAESCH sf at 4js.com
Wed Jan 9 06:52:15 EST 2008


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
-------------- next part --------------
A non-text attachment was scrubbed...
Name: odbctest32.c
Type: text/x-csrc
Size: 4773 bytes
Desc: not available
Url : http://lists.ibiblio.org/pipermail/freetds/attachments/20080109/52674b5d/attachment.bin 


More information about the FreeTDS mailing list