FreeTDS Development Group

[Frediano Ziglio <freddy77 AT gmail.com>]

Il giorno mer, 09/01/2008 alle 12.52 +0100, Sebastien FLAESCH ha
scritto:
> 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

I just applied a patch for this problem. TDS_CANCELLED result was not
tested everywhere in the code. However it seems that Sybase and
Microsoft returns different DONE tokens for cancellation... Microsoft
returns 2 finals DONE while Sybase returns only one. This require an
additional fix in libTDS (in CVS HEAD but not in 0.82 branch). Also it
seems that in some different systems signal are handled in a slightly
different way. That is
- query is sent, state is set to TDS_READING
- select/poll stops
- signal is caught, SQLCancel is called
- cancel token is sent, in_cancel get 1, current_statement is reset
- select/poll stop with EINTR error
- interrupt is call (which do nothing)
- tds_select returns 0
- tdserror(TDSETIME) is called
- odbc_errmsg_handler close connection cause current_statement is
NULL...
So in this case resetting current_statement is not so fine. However even
if I remove reset in SQLCancel odbc_errmsg_handler close connection
cause in_cancel is set. This to handle correctly timeouts. This cause on
first timeout ODBC sent cancel request while on second in a while it
close connection.

freddy77