Skip to Content.
Sympa Menu

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

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Sebastien FLAESCH <sf AT 4js.com>
  • To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008 SQLSTATE
  • Date: Tue, 15 Jan 2008 10:09:57 +0100

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






Archive powered by MHonArc 2.6.24.

Top of Page