[freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE
Sebastien FLAESCH
sf at 4js.com
Tue Jan 15 04:09:57 EST 2008
Hello James,
First thanks a lot for taking the time to explain the internals.
Resources (just to compare):
With DB2 CLI, they don't talk about SIGINT, but rather multi-threaded applications where a second thread issues SQLCancel().
http://webdocs.caspur.it/ibm/db2/8.1/doc/htmlcd/en_US/ad/r0000567.htm
With Oracle OCI, they talk about Control-C:
http://download.oracle.com/docs/cd/B14117_01/appdev.101/b10779/oci02bas.htm#462655
Of course, nothing about SIGINT on MSDN:
http://msdn2.microsoft.com/en-us/library/ms714112.aspx
...
I can probably use any solution you will provide to handle SQL interruption.
But I believe there is a good chance to have other ODBC programmers handle SQL interruption with a SIGINT + SQLCancel() as I did.
Therefore, you better choose the second "robust" solution.
Note BTW that several SIGINT signals can be generated successively: Frustrated users keeping CONTROL-C pressed down ;-).
=> Many SQLCancel() calls ...
I wonder however why the TDS protocol does not yet specify how to handle query interruption.
To me this should be part of the protocol definition (= you should not have to choose between different solutions)
...
Looks like you have to do tricky things (cleaning up the connection) to handle this... right?
Just a remark:
Maybe SQLCancel() (query interruption in TDS) is only supported since SQL Server since version 2005?
I just remember that we started to support SQL interruption with SQL Server native ODBC drivers since 2005 only.
Quite sure it was not working before that version... but must double check.
I can only suggest to try my little SQLCancel() example with SQL Server 2005 and sniff the protocol.
But it must be adapted to catch console events to trap CTRL-C (does not with with SIGINT of course)
I will try to adapt the sample and send it back.
Thanks!
Seb
James K. Lowden wrote:
> Sebastien FLAESCH wrote:
>> James K. Lowden wrote:
>>> I think all the signal handler can do is record the fact that a signal
>>> was caught in a more-or-less global variable. When the called
>>> function returns an error -- e.g. a timeout on reads -- the app can
>>> then decide whether or not to call SQLCancel.
>> How would you implement
>> SQL interruption in a text-based SQL command interpreter?
>
> Before I answer that, let me remind you:
>
>> James K. Lowden wrote:
>>> I don't know the proper treatment of signals in an ODBC application,
>
> :-)
>
> But this for sure:
>
>>> The
>>> handler doesn't have enough information to know whether that's the
>>> right thing to do.
>
> The application can't know the state of the TDS connection. The handler
> can't even know (without a lot of help) the state of the application.
> Together they definitely don't know whether the server is ready to receive
> a cancel packet.
>
> More: Is the driver ready? How will the driver handle a nested
> signal-driven call?
>
> SQLFetch
> waiting ... waiting ...
> [sigint]
> SQLCancel ... OK (or not)
> (handler returns control to SQLFetch)
> waiting ... waiting ...
> waiting ... waiting ...
>
> But you asked what to do, not what not to do.
>
> Signal handling is tricky. The trick is to make a note and adjust course
> when time permits. The application and the driver have to cooperate in a
> 12-step program back to sanity:
>
> 1. Application installs handler.
> 2. Driver *also* installs handler, stores app's handler.
> 3. Connect, Exec, Fetch ....
> 4. SIGINT!
> 5. Driver catches. Prepares to send SYBETIME (HY008).
> 6. Driver calls app's handler.
> 7. App catches, prepares to send Cancel.
> 8. App handler returns, Driver handler returns.
> 9. Driver "times out".
> 10. SQLFetch fails with HY008.
> 11. App sees timeout and SIGINT evidence.
> 12. App: 4 = 2 + 2. Calls SQLCancel.
>
>> For sure GUI applications typically handle user interruption by catching
>> a special GUI event (click on cancel button), and can then call
>> SQLCancel() outside a signal handler flow... be this is about a non-GUI
>> application.
>
> GUI or not makes no difference. Win32 makes a difference. Details vary
> by OS even within the Unixish world.
>
> An interactive program might want to query the user by writing to the tty
> or putting up a message box asking what to do. Depending on the
> environment, that might be OK to do inside the handler.
>
>> This is to me unexpected: The application should handle signals such as
>> SIGINT, and do the DB client API calls (if available) to cancel a query.
>>
>> This works for us with:
>>
>> - Postgresql using PQcancel()
>> - DB2 CLI using SQLCancel()
>> - ANTS DS using SQLCancel()
>> - Informix using sqlbreak() on Unix, and callback method on MS Windows.
>> - Oracle using OCIBreak() (but need to register SIGINT handle after
>> connection)
>
> To be robust, the FreeTDS could be made to tolerate having SQLCancel
> called inside a signal handler.
>
> 1. SIGINT!
> 2. Driver catches, calls app's handler.
> 3. App catches, calls SQLCancel
> 4. Driver calls special low-level function to tidy up the connection.
> 5. Driver completes SQLCancel, returns.
> 6. App handler returns, Driver handler returns.
> 7. Driver sees the maid was here, "times out".
> 8. SQLFetch fails with HY008.
>
> With some housekeeping -- keeping track of nesting level and which API
> function is being executed -- step #2 wouldn't be needed. SQLCancel could
> tell that it was on a separate, nested thread of control and be mindful of
> the TDS state.
>
> You may know I rewrote the socket logic last year, to make it handle
> timeouts. I focussed on db-lib semantics, which include callbacks and
> *prohibit* calling any API function inside any handler. The "special
> low-level function to tidy up the connection" has to live in that world.
>
> It's easy to fake, and it will work most of the time:
>
> 3. App catches, calls SQLCancel
> 4. SQLCancels set flag for odbc_errmsg_handler, returns OK.
> 5. App handler returns.
> 6. Driver handler returns.
> 7. Driver calls odbc_errmsg_handler (per normal)
> 8 Driver sends Cancel -- might or might not work, but OK
> 9. SQLFetch fails with HY008.
>
> It would be easy to implement and hard to tell the difference. Let's see
> what Frediano says.
>
> Regards,
>
> --jkl
>
>
> _______________________________________________
> FreeTDS mailing list
> FreeTDS at lists.ibiblio.org
> http://lists.ibiblio.org/mailman/listinfo/freetds
>
More information about the FreeTDS
mailing list