freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008SQLSTATE

From: Sebastien FLAESCH <sf AT 4js.com>
To: FreeTDS Development Group <freetds AT lists.ibiblio.org>
Subject: Re: [freetds] SQLCancel() and error diagnostic: Expecting HY008SQLSTATE
Date: Tue, 15 Jan 2008 12:26:43 +0100

Frediano,

I have just tested my little sample with the HEAD and I get an HY008 SQLSTATE
as expected.

But I still have a problem: I do not get this SQLSTATE when using our Genero
VM...

Attached the log ... (using default debug flags, tell me what I should set if
needed).

...

Some details that may help:

Our VM is a pseudo-code interpretor where we handle user interruption in
different ways.

In console / TUI mode:

When a SIGINT is catched, we just set a global indicator which is checked in
the p-code interpreter loop.
=> we do not call directly SQLCancel() from the SIGINT handler.

In GUI mode:

We have a proprietary GUI protocol to connect presentation clients to
application with sockets.
When the front-end waits for the application to respond (running SQL
statement), it can still send a asynchronous event on the socket.
We use non-blocking select() and we detect SIGIO signals if need data is sent
by front-ends.
In this can we do also set a global flag if we catch an interruption GUI
event, and processed in pcode interpreter loop.
=> we do not call directly SQLCancel() from the SIGIO handler.

I have tested with TUI and GUI mode, in both cases I can interrupt, but
SQLSTATE is 00000.

...

Would the TDS driver only set HY008 when SQLCancel() is called during a
sighandler?

I guess no, according to the code you just check the TDS return code:

case TDS_CANCELLED:
odbc_errs_add(&stmt->errs, "HY008", NULL);

So I really wonder... I will investigate a bit more.

It's possible that my FreeTDS interface does more ODBC calls than the like
sample I have send.

Thanks,
Seb

Sebastien FLAESCH wrote:

Thanks Frediano.

Will cvs update the HEAD and make some tests.

Seb

ZIGLIO, Frediano, VF-IT wrote:

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.

Currently CVS HEAD seems to handle all situations correctly.

Note BTW that several SIGINT signals can be generated successively: Frustrated users keeping CONTROL-C pressed down ;-).
=> Many SQLCancel() calls ...

First send cancel than it wait for cancel handling by server. So others
SQLCancel just do nothing.

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)

...

??? it calls cancellation.

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.

cancellation is supported even by protocol 4.2.

I can only suggest to try my little SQLCancel() example with SQL Server 2005 and sniff the protocol.
See odbc cancel test. I use alarm to emulate ctrl-c

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

The bad thing... it seems that if test is executed using unixODBC it
fails. Our SQLCancel is not called (that is you call SQLCancel and
unixODBC do not call driver one... it hangs). I use unixODBC 2.2.11,
I'll test with 2.2.12

freddy77
_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

_______________________________________________
FreeTDS mailing list
FreeTDS AT lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

log.c:190:Starting log file for FreeTDS 0.83.dev.20080111
	on 2008-01-15 12:15:36 with debug flags 0x4fff.
iconv.c:197:names for ISO-8859-1: ISO-8859-1
iconv.c:197:names for UTF-8: UTF-8
iconv.c:197:names for UCS-2LE: UCS-2LE
iconv.c:197:names for UCS-2BE: UCS-2BE
iconv.c:363:iconv to convert client-side data to the "ISO-8859-1" character set
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"UCS-2LE"
net.c:204:Connecting to 10.0.0.115 port 1680 (TDS version 8.0)
net.c:258:tds_open_socket: connect(2) returned "Operation now in progress"
net.c:296:tds_open_socket() succeeded
util.c:162:Changed query state from DEAD to IDLE
login.c:735:quietly sending TDS 7+ login packet
token.c:312:tds_process_login_tokens()
net.c:584:Received header
0000 04 01 01 89 00 33 01 00-                        |.....3..|

net.c:663:Received packet
0000 e3 1f 00 01 08 6d 00 73-00 76 00 74 00 65 00 73 |.....m.s .v.t.e.s|
0010 00 74 00 31 00 06 6d 00-61 00 73 00 74 00 65 00 |.t.1..m. a.s.t.e.|
0020 72 00 ab 6e 00 45 16 00-00 02 00 27 00 43 00 68 |r..n.E.. ...'.C.h|
0030 00 61 00 6e 00 67 00 65-00 64 00 20 00 64 00 61 |.a.n.g.e .d. .d.a|
0040 00 74 00 61 00 62 00 61-00 73 00 65 00 20 00 63 |.t.a.b.a .s.e. .c|
0050 00 6f 00 6e 00 74 00 65-00 78 00 74 00 20 00 74 |.o.n.t.e .x.t. .t|
0060 00 6f 00 20 00 27 00 6d-00 73 00 76 00 74 00 65 |.o. .'.m .s.v.t.e|
0070 00 73 00 74 00 31 00 27-00 2e 00 0a 43 00 4f 00 |.s.t.1.' ....C.O.|
0080 42 00 52 00 41 00 5c 00-4d 00 53 00 56 00 31 00 |B.R.A.\. M.S.V.1.|
0090 00 01 00 e3 08 00 07 05-09 04 c0 00 33 00 e3 17 |........ ....3...|
00a0 00 02 0a 75 00 73 00 5f-00 65 00 6e 00 67 00 6c |...u.s._ .e.n.g.l|
00b0 00 69 00 73 00 68 00 00-ab 6e 00 47 16 00 00 01 |.i.s.h.. .n.G....|
00c0 00 27 00 43 00 68 00 61-00 6e 00 67 00 65 00 64 |.'.C.h.a .n.g.e.d|
00d0 00 20 00 6c 00 61 00 6e-00 67 00 75 00 61 00 67 |. .l.a.n .g.u.a.g|
00e0 00 65 00 20 00 73 00 65-00 74 00 74 00 69 00 6e |.e. .s.e .t.t.i.n|
00f0 00 67 00 20 00 74 00 6f-00 20 00 75 00 73 00 5f |.g. .t.o . .u.s._|
0100 00 65 00 6e 00 67 00 6c-00 69 00 73 00 68 00 2e |.e.n.g.l .i.s.h..|
0110 00 0a 43 00 4f 00 42 00-52 00 41 00 5c 00 4d 00 |..C.O.B. R.A.\.M.|
0120 53 00 56 00 31 00 00 01-00 ad 36 00 01 71 00 00 |S.V.1... ..6..q..|
0130 01 16 4d 00 69 00 63 00-72 00 6f 00 73 00 6f 00 |..M.i.c. r.o.s.o.|
0140 66 00 74 00 20 00 53 00-51 00 4c 00 20 00 53 00 |f.t. .S. Q.L. .S.|
0150 65 00 72 00 76 00 65 00-72 00 00 00 00 00 09 00 |e.r.v.e. r.......|
0160 05 77 e3 13 00 04 04 34-00 30 00 39 00 36 00 04 |.w.....4 .0.9.6..|
0170 34 00 30 00 39 00 36 00-fd 00 00 00 00 00 00 00 |4.0.9.6. ........|
0180 00                     -                        |.|

token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  ab(INFO)
token.c:108:tds_process_default_tokens() marker is ab(INFO)
token.c:2451:tds_process_msg() reading message from server
token.c:2516:tds_process_msg() calling client msg handler
odbc.c:2026:msgno 5701 20003
token.c:2529:tds_process_msg() returning TDS_SUCCEED
token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:2281:tds_process_env_chg(): 5 bytes of collation data received
token.c:2282:tds->collation was
0000 00 00 00 00 00         -                        |.....|

iconv.c:985:setting server single-byte charset to "CP1252"
iconv.c:516:tds_iconv_info_init: converting "ISO-8859-1"->"CP1252"
token.c:2292:tds->collation now
0000 09 04 c0 00 33         -                        |....3|

token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  ab(INFO)
token.c:108:tds_process_default_tokens() marker is ab(INFO)
token.c:2451:tds_process_msg() reading message from server
token.c:2516:tds_process_msg() calling client msg handler
odbc.c:2026:msgno 5703 20003
token.c:2529:tds_process_msg() returning TDS_SUCCEED
token.c:316:looking for login token, got  ad(LOGINACK)
token.c:316:looking for login token, got  e3(ENVCHANGE)
token.c:108:tds_process_default_tokens() marker is e3(ENVCHANGE)
token.c:316:looking for login token, got  fd(DONE)
token.c:108:tds_process_default_tokens() marker is fd(DONE)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 0
		error = 0
		done_count_valid = 0
token.c:2217:tds_process_end() state set to TDS_IDLE
token.c:2232:                rows_affected = 0
token.c:393:leaving tds_process_login_tokens() returning 1
odbc.c:5868:SQLSetConnectAttr(0x8791b80, 103, 0x5, -6)
odbc.c:5786:_SQLSetConnectAttr(0x8791b80, 103, 0x5, -6)
odbc.c:1237:SQLAllocHandle(3, 0x8791b80, 0x87c5a18)
odbc.c:1396:_SQLAllocStmt(0x8791b80, 0x87c5a18)
odbc.c:4258:SQLSetCursorName(0x87c5b60, 0x6632fe, -3)
odbc.c:6163:SQLSetStmtAttr(0x87c5b60, -1, (nil), -5)
odbc.c:5891:_SQLSetStmtAttr(0x87c5b60, -1, (nil), -5)
odbc.c:6163:SQLSetStmtAttr(0x87c5b60, -2, 0x2, -5)
odbc.c:5891:_SQLSetStmtAttr(0x87c5b60, -2, 0x2, -5)
odbc.c:6163:SQLSetStmtAttr(0x87c5b60, 5, (nil), -5)
odbc.c:5891:_SQLSetStmtAttr(0x87c5b60, 5, (nil), -5)
odbc.c:6163:SQLSetStmtAttr(0x87c5b60, 27, 0xa, -5)
odbc.c:5891:_SQLSetStmtAttr(0x87c5b60, 27, 0xa, -5)
odbc.c:6163:SQLSetStmtAttr(0x87c5b60, 26, 0x87c5a38, -5)
odbc.c:5891:_SQLSetStmtAttr(0x87c5b60, 26, 0x87c5a38, -5)
odbc.c:4098:SQLPrepare(0x87c5b60, select COUNT(*) from   dbit1  t1,dbit1  t2,dbit1  t3,dbit1  t4,dbit1  t5,dbit1  t6,dbit1  t7,dbit1  t8, -3)
odbc.c:3234:SQLExecute(0x87c5b60)
odbc.c:2959:_SQLExecute(0x87c5b60)
odbc.c:2964:_SQLExecute() starting with state 0
query.c:2053:tds_cursor_declare() cursor id = 0
query.c:2103:tds_cursor_open() cursor id = 0
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
query.c:2231:tds_cursor_open (): RPC call set up 
query.c:2235:tds_cursor_open (): cursor open completed
net.c:769:Sending packet
0000 03 01 01 0a 00 00 01 00-ff ff 02 00 00 00 00 01 |........ ........|
0010 26 04 00 00 00 63 cc 00-00 00 09 04 c0 00 33 cc |&....c.. ......3.|
0020 00 00 00 73 00 65 00 6c-00 65 00 63 00 74 00 20 |...s.e.l .e.c.t. |
0030 00 43 00 4f 00 55 00 4e-00 54 00 28 00 2a 00 29 |.C.O.U.N .T.(.*.)|
0040 00 20 00 66 00 72 00 6f-00 6d 00 20 00 20 00 20 |. .f.r.o .m. . . |
0050 00 64 00 62 00 69 00 74-00 31 00 20 00 20 00 74 |.d.b.i.t .1. . .t|
0060 00 31 00 2c 00 64 00 62-00 69 00 74 00 31 00 20 |.1.,.d.b .i.t.1. |
0070 00 20 00 74 00 32 00 2c-00 64 00 62 00 69 00 74 |. .t.2., .d.b.i.t|
0080 00 31 00 20 00 20 00 74-00 33 00 2c 00 64 00 62 |.1. . .t .3.,.d.b|
0090 00 69 00 74 00 31 00 20-00 20 00 74 00 34 00 2c |.i.t.1.  . .t.4.,|
00a0 00 64 00 62 00 69 00 74-00 31 00 20 00 20 00 74 |.d.b.i.t .1. . .t|
00b0 00 35 00 2c 00 64 00 62-00 69 00 74 00 31 00 20 |.5.,.d.b .i.t.1. |
00c0 00 20 00 74 00 36 00 2c-00 64 00 62 00 69 00 74 |. .t.6., .d.b.i.t|
00d0 00 31 00 20 00 20 00 74-00 37 00 2c 00 64 00 62 |.1. . .t .7.,.d.b|
00e0 00 69 00 74 00 31 00 20-00 20 00 74 00 38 00 00 |.i.t.1.  . .t.8..|
00f0 01 26 04 04 04 00 00 00-00 01 26 04 04 04 20 00 |.&...... ..&... .|
0100 00 00 01 26 04 04 00 00-00 00                   |...&.... ..|

util.c:162:Changed query state from QUERYING to PENDING
token.c:495:tds_process_tokens(0x8793110, 0xbfea0ca4, 0xbfea0ca8, 0x100)
util.c:162:Changed query state from PENDING to READING
odbc.c:1580:SQLCancel(0x87c5b60)
query.c:1934:tds_send_cancel: not in_cancel and not idle
query.c:1943:tds_send_cancel: sending cancel packet
net.c:769:Sending packet
0000 06 01 00 08 00 00 01 00-                        |........|

net.c:584:Received header
0000 04 01 00 38 00 33 01 00-                        |...8.3..|

net.c:663:Received packet
0000 81 02 00 00 00 01 00 26-04 00 00 00 00 00 38 07 |.......& ......8.|
0010 52 00 4f 00 57 00 53 00-54 00 41 00 54 00 a5 06 |R.O.W.S. T.A.T...|
0020 00 01 00 04 02 00 14 fe-02 00 e0 00 00 00 00 00 |........ ........|

token.c:510:processing result tokens.  marker is  81(TDS7_RESULT)
token.c:1520:processing TDS7 result metadata.
mem.c:563:tds_free_all_results()
token.c:1541:set current_results to cursor->res_info
token.c:1555:setting up column 0
token.c:1490:tds7_get_data_info: 
	colname =  (0 bytes)
	type = 38 (integer-null)
	server's type = 38 (integer-null)
	column_varint_size = 1
	column_size = 4 (4 on server)
token.c:1555:setting up column 1
token.c:1490:tds7_get_data_info: 
	colname = ROWSTAT (7 bytes)
	type = 56 (int)
	server's type = 56 (int)
	column_varint_size = 0
	column_size = 4 (4 on server)
token.c:510:processing result tokens.  marker is  a5(COLINFO)
token.c:108:tds_process_default_tokens() marker is a5(COLINFO)
token.c:510:processing result tokens.  marker is  fe(DONEPROC)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 0
		error = 1
		done_count_valid = 0
token.c:2232:                rows_affected = 0
net.c:584:Received header
0000 04 01 00 11 00 33 01 00-                        |.....3..|

net.c:663:Received packet
0000 fd 20 00 fd 00 00 00 00-00                      |. ...... .|

token.c:510:processing result tokens.  marker is  fd(DONE)
token.c:2201:tds_process_end: more_results = 0
		was_cancelled = 1
		error = 0
		done_count_valid = 0
token.c:2217:tds_process_end() state set to TDS_IDLE
util.c:162:Changed query state from READING to IDLE
token.c:2232:                rows_affected = 0
mem.c:835:tds_cursor_deallocated() : freeing cursor_id 0
mem.c:860:tds_cursor_deallocated() : cursor_id 0 found
mem.c:864:tds_cursor_deallocated() : relinking list
mem.c:871:tds_cursor_deallocated() : relinked list
mem.c:887:tds_release_cursor() : freeing cursor_id 0
mem.c:889:tds_release_cursor() : freeing cursor results
mem.c:895:tds_release_cursor() : freeing cursor name
mem.c:900:tds_release_cursor() : freeing cursor query
mem.c:904:tds_release_cursor() : cursor_id 0 freed
error.c:609:SQLGetDiagRec(3, 0x87c5b60, 1, 0xbfea0d40, 0xbfea0b28, 0xbfea0b30, 513, 0xbfea0b2e)
error.c:510:_SQLGetDiagRec(3, 0x87c5b60, 1, 0xbfea0d40, 0xbfea0b28, 0xbfea0b30, 513, 0xbfea0b2e)
odbc.c:3654:SQLFreeHandle(3, 0x0x87c5b60)
odbc.c:3743:_SQLFreeStmt(0x87c5b60, 1, 0)
odbc.c:1999:SQLDisconnect(0x8791b80)
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to DEAD
odbc.c:3654:SQLFreeHandle(2, 0x0x8791b80)
odbc.c:3680:_SQLFreeConnect(0x8791b80)

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