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

ZIGLIO, Frediano, VF-IT Frediano.Ziglio at vodafone.com
Thu Jan 10 09:45:08 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
> 

Timeout and cancel... still a nigthmare it seems.

freddy77




More information about the FreeTDS mailing list