Skip to Content.
Sympa Menu

freetds - [freetds] problem with ct_dynamic(....CS_PREPARE...) against SQL Server 2005

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: ocfs2 AT asset-control.com
  • To: freetds AT lists.ibiblio.org
  • Subject: [freetds] problem with ct_dynamic(....CS_PREPARE...) against SQL Server 2005
  • Date: Mon, 16 Feb 2009 15:37:32 +0100

I could really use some help with the following issue:
I am trying to prepare and execute a statement containing parameter
placeholders (?) multiple times using ctlib.
According to the API documentation, ct_dynamic() is fully supported.

Here is a compressed source code snippet:

ct_dynamic(cmd, CS_PREPARE, statement_name, CS_NULLTERM, sql_qry,
CS_NULLTERM)
/* statement_name equals something like "s5368709312" */
/* sql_qry equals "insert into list_contents(entry_key, list_id,
position) values(?, ?, ?)" */
ct_send(cmd)
ct_dynamic(cmd, CS_DESCRIBE_INPUT, statement_name, CS_NULLTERM, NULL,
CS_UNUSED)
ct_send(scip->cmd)
LOOP ct_results(cmd, ..)
/* One of the expected result sets is a CS_DESCRIBE_RESULT for
which we call */
ct_res_info(cmd, CS_NUMDATA, &num_cols, CS_UNUSED, NULL) /* to get
the number of parameters in the statement*/
/* after which, for every parameter i we call */
ct_describe(scip->cmd, i+1, &sd_column[i].sd_datafmt) /* to obtain
the data format needed for parameter i */
ct_dynamic(cmd, CS_EXECUTE, statement_name, CS_NULLTERM, NULL, CS_UNUSED)
/* now for every parameter i we call */
ct_setparam(cmd, &column[i].sd_datafmt, sd_column[i].sd_sqldata,
&sd_column[i].sd_sqllen, &sd_column[i].sd_sqlind)
/* and finally send the execute */
ct_send(cmd)
LOOP ct_results(cmd, ..)
/* go repeat from ct_dynamic(...CS_EXECUTE...) if more rows need to be
processed */

This works fine with a Sybase ASE 12.5 backend, using tds version 5.0
However it fails with SQL Server 2005, using tds version 8.0.
When debugging, inside ct_res_info() there appears to be no result sets
(tds->current_results equals 0), although ct_results() before said that
there is a CS_DESCRIBE_RESULT available. This causes no parameters to be
bound and therefore the insert obviously fails.

Below is a fragment from the tds log.
I was wondering if there is somebody here with tds protocol knowledge that
can tell from this log what the problem might be?
tia.
Tim Knigge
p.s. if it is helpful, I can post the same tds.log fragment for Sybase/tds
5.0 where this encounters no problems.

ct.c:3299:ct_dynamic(717)
ct.c:4737:_ct_allocate_dynamic() attaching dynamic command to head
ct.c:215:setting command state from READY to READY
ct.c:844:ct_send() command_type = 160
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
write.c:136:tds_put_string converting 3 bytes of "@P1"
write.c:164:tds_put_string wrote 6 bytes
write.c:136:tds_put_string converting 3 bytes of "@P2"
write.c:164:tds_put_string wrote 6 bytes
write.c:136:tds_put_string converting 3 bytes of "@P3"
write.c:164:tds_put_string wrote 6 bytes
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 03 01 01 36 00 00 01 00-ff ff 0b 00 00 00 00 01 |...6.... ........|
0010 26 04 00 00 00 63 5e 00-00 00 09 04 d0 00 34 5e |&....c^. ......4^|
0020 00 00 00 40 00 50 00 31-00 20 00 76 00 61 00 72 |...@.P.1 . .v.a.r|
0030 00 63 00 68 00 61 00 72-00 28 00 38 00 30 00 29 |.c.h.a.r .(.8.0.)|
0040 00 2c 00 40 00 50 00 32-00 20 00 76 00 61 00 72 |.,.@.P.2 . .v.a.r|
0050 00 63 00 68 00 61 00 72-00 28 00 38 00 30 00 29 |.c.h.a.r .(.8.0.)|
0060 00 2c 00 40 00 50 00 33-00 20 00 76 00 61 00 72 |.,.@.P.3 . .v.a.r|
0070 00 63 00 68 00 61 00 72-00 28 00 38 00 30 00 29 |.c.h.a.r .(.8.0.)|
0080 00 00 00 63 9c 00 00 00-09 04 d0 00 34 9c 00 00 |...c.... ....4...|
0090 00 20 00 69 00 6e 00 73-00 65 00 72 00 74 00 20 |. .i.n.s .e.r.t. |
00a0 00 69 00 6e 00 74 00 6f-00 20 00 6c 00 69 00 73 |.i.n.t.o . .l.i.s|
00b0 00 74 00 5f 00 63 00 6f-00 6e 00 74 00 65 00 6e |.t._.c.o .n.t.e.n|
00c0 00 74 00 73 00 28 00 65-00 6e 00 74 00 72 00 79 |.t.s.(.e .n.t.r.y|
00d0 00 5f 00 6b 00 65 00 79-00 2c 00 20 00 6c 00 69 |._.k.e.y .,. .l.i|
00e0 00 73 00 74 00 5f 00 69-00 64 00 2c 00 20 00 70 |.s.t._.i .d.,. .p|
00f0 00 6f 00 73 00 69 00 74-00 69 00 6f 00 6e 00 29 |.o.s.i.t .i.o.n.)|
0100 00 20 00 76 00 61 00 6c-00 75 00 65 00 73 00 28 |. .v.a.l .u.e.s.(|
0110 00 40 00 50 00 31 00 2c-00 20 00 40 00 50 00 32 |.@.P.1., . .@.P.2|
0120 00 2c 00 20 00 40 00 50-00 33 00 29 00 00 00 26 |.,. .@.P .3.)...&|
0130 04 04 01 00 00 00 - |......|

ct.c:215:setting command state from READY to SENT
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401970, 140196c, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 2f 00 3d 01 00- |.../.=..|

net.c:671:Received packet
0000 ff 11 00 c3 00 00 00 00-00 79 00 00 00 00 ac 0d |........ .y......|
0010 00 00 01 00 00 00 00 26-04 04 02 00 00 00 fe 00 |.......& ........|
0020 00 e0 00 00 00 00 00 - |.......|

token.c:510:processing result tokens. marker is ff(DONEINPROC)
token.c:2201:tds_process_end: more_results = 1
was_cancelled = 0
error = 0
done_count_valid = 1
token.c:2232: rows_affected = 0
token.c:510:processing result tokens. marker is 79(RETURNSTATUS)
token.c:510:processing result tokens. marker is ac(PARAM)
token.c:567:processing parameters for sp 11
token.c:569:calling tds_process_param_result
token.c:1198:tds_process_param_result(4dc2e8, 14018d8)
token.c:1596:processing result. type = 38(integer-null), varint_size 1
token.c:1617:processing result. column_size 4
token.c:1915:tds_get_data: type 38, varint size 1
token.c:1975:tds_get_data(): wire column size is 4
token.c:2139:swapping coltype 56
token.c:573:1 hidden return parameters
token.c:510:processing result tokens. marker is fe(DONEPROC)
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
util.c:162:Changed query state from READING to IDLE
token.c:2232: rows_affected = 0
util.c:110:logic error: cannot change query state from IDLE to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4053)
ct.c:1100:ct_results()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401970, 140196c, 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
ct.c:1157:ct_results() process_result_tokens returned 2 (type 4052)
ct.c:215:setting command state from SENT to READY
ct.c:3299:ct_dynamic(720)
ct.c:4762:_ct_locate_dynamic() looking for s5368709312
ct.c:4765:_ct_locate_dynamic() matching with s5368709312
ct.c:215:setting command state from READY to READY
ct.c:844:ct_send() command_type = 160
ct.c:891:ct_send(CS_DESCRIBE_INPUT)
ct.c:215:setting command state from READY to SENT
ct.c:1100:ct_results()
ct.c:2357:ct_res_info()
ct.c:2376:ct_res_info(): Number of columns is 0
ct.c:1100:ct_results()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401970, 140196c, 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
ct.c:1157:ct_results() process_result_tokens returned 2 (type 4052)
ct.c:215:setting command state from SENT to READY
ct.c:3299:ct_dynamic(718)
ct.c:4762:_ct_locate_dynamic() looking for s5368709312
ct.c:4765:_ct_locate_dynamic() matching with s5368709312
ct.c:3340:ct_dynamic() calling param_clear
ct.c:215:setting command state from READY to READY
ct.c:844:ct_send() command_type = 160
query.c:1631:tds_submit_execute()
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 03 01 00 29 00 00 01 00-0a 00 73 00 70 00 5f 00 |...).... ..s.p._.|
0010 65 00 78 00 65 00 63 00-75 00 74 00 65 00 00 00 |e.x.e.c. u.t.e...|
0020 00 00 26 04 04 02 00 00-00 |..&..... .|

ct.c:215:setting command state from READY to SENT
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401970, 140196c, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 01 71 00 3d 01 00- |...q.=..|

net.c:671:Received packet
0000 aa 58 01 f2 1f 00 00 01-10 8f 00 54 00 68 00 65 |.X...... ...T.h.e|
0010 00 20 00 70 00 61 00 72-00 61 00 6d 00 65 00 74 |. .p.a.r .a.m.e.t|
0020 00 65 00 72 00 69 00 7a-00 65 00 64 00 20 00 71 |.e.r.i.z .e.d. .q|
0030 00 75 00 65 00 72 00 79-00 20 00 27 00 28 00 40 |.u.e.r.y . .'.(.@|
0040 00 50 00 31 00 20 00 76-00 61 00 72 00 63 00 68 |.P.1. .v .a.r.c.h|
0050 00 61 00 72 00 28 00 38-00 30 00 29 00 2c 00 40 |.a.r.(.8 .0.).,.@|
0060 00 50 00 32 00 20 00 76-00 61 00 72 00 63 00 68 |.P.2. .v .a.r.c.h|
0070 00 61 00 72 00 28 00 38-00 30 00 29 00 2c 00 40 |.a.r.(.8 .0.).,.@|
0080 00 50 00 33 00 20 00 76-00 61 00 72 00 63 00 68 |.P.3. .v .a.r.c.h|
0090 00 61 00 72 00 28 00 38-00 30 00 29 00 29 00 20 |.a.r.(.8 .0.).). |
00a0 00 69 00 6e 00 73 00 65-00 72 00 74 00 20 00 69 |.i.n.s.e .r.t. .i|
00b0 00 6e 00 74 00 6f 00 20-00 6c 00 69 00 27 00 20 |.n.t.o. .l.i.'. |
00c0 00 65 00 78 00 70 00 65-00 63 00 74 00 73 00 20 |.e.x.p.e .c.t.s. |
00d0 00 74 00 68 00 65 00 20-00 70 00 61 00 72 00 61 |.t.h.e. .p.a.r.a|
00e0 00 6d 00 65 00 74 00 65-00 72 00 20 00 27 00 40 |.m.e.t.e .r. .'.@|
00f0 00 50 00 31 00 27 00 2c-00 20 00 77 00 68 00 69 |.P.1.'., . .w.h.i|
0100 00 63 00 68 00 20 00 77-00 61 00 73 00 20 00 6e |.c.h. .w .a.s. .n|
0110 00 6f 00 74 00 20 00 73-00 75 00 70 00 70 00 6c |.o.t. .s .u.p.p.l|
0120 00 69 00 65 00 64 00 2e-00 17 43 00 4f 00 4e 00 |.i.e.d.. ..C.O.N.|
0130 46 00 49 00 47 00 32 00-33 00 32 00 56 00 4d 00 |F.I.G.2. 3.2.V.M.|
0140 30 00 5c 00 53 00 51 00-4c 00 45 00 58 00 50 00 |0.\.S.Q. L.E.X.P.|
0150 52 00 45 00 53 00 53 00-00 01 00 79 01 00 00 00 |R.E.S.S. ...y....|
0160 fe 02 00 e0 00 00 00 00-00 |........ .|

token.c:510:processing result tokens. marker is aa(ERROR)
token.c:108:tds_process_default_tokens() marker is aa(ERROR)
token.c:2451:tds_process_msg() reading message from server
token.c:2516:tds_process_msg() calling client msg handler
ct.c:334:ct_con_props() action = CS_GET property = 9108
ct.c:486:fetching userdata c7148
token.c:2529:tds_process_msg() returning TDS_SUCCEED
token.c:510:processing result tokens. marker is 79(RETURNSTATUS)
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: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
util.c:110:logic error: cannot change query state from IDLE to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4053)
ct.c:2357:ct_res_info()
ct.c:2382:ct_res_info(): Number of rows is -1
ct.c:1100:ct_results()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401970, 140196c, 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
ct.c:1157:ct_results() process_result_tokens returned 2 (type 4052)
ct.c:215:setting command state from SENT to READY
ct.c:334:ct_con_props() action = CS_GET property = 9143
ct.c:3299:ct_dynamic(711)
ct.c:4762:_ct_locate_dynamic() looking for s5368709312
ct.c:4765:_ct_locate_dynamic() matching with s5368709312
ct.c:215:setting command state from READY to READY
ct.c:844:ct_send() command_type = 160
query.c:1768:tds_submit_unprepare() s5368709312
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 03 01 00 17 00 00 01 00-ff ff 0f 00 00 00 00 00 |........ ........|
0010 26 04 04 02 00 00 00 - |&......|

ct.c:215:setting command state from READY to SENT
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401888, 1401884, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 16 00 3d 01 00- |.....=..|

net.c:671:Received packet
0000 79 f2 1f 00 00 fe 00 00-e0 00 00 00 00 00 |y....... ......|

token.c:510:processing result tokens. marker is 79(RETURNSTATUS)
token.c:510:processing result tokens. marker is fe(DONEPROC)
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
util.c:162:Changed query state from READING to IDLE
token.c:2232: rows_affected = 0
util.c:110:logic error: cannot change query state from IDLE to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4053)
ct.c:1100:ct_results()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401888, 1401884, 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
ct.c:1157:ct_results() process_result_tokens returned 2 (type 4052)
ct.c:215:setting command state from SENT to READY
ct.c:4797:ct_deallocate_dynamic() : command entry found in list
ct.c:4810:ct_deallocate_dynamic() : relinking list
ct.c:4817:ct_deallocate_dynamic() : relinked list
ct.c:682:ct_command()
ct.c:215:setting command state from READY to IDLE
ct.c:215:setting command state from IDLE to IDLE
ct.c:215:setting command state from IDLE to READY
ct.c:844:ct_send() command_type = 148
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
write.c:136:tds_put_string converting 20 bytes of "rollback transaction"
write.c:164:tds_put_string wrote 40 bytes
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 01 01 00 30 00 00 01 00-72 00 6f 00 6c 00 6c 00 |...0.... r.o.l.l.|
0010 62 00 61 00 63 00 6b 00-20 00 74 00 72 00 61 00 |b.a.c.k. .t.r.a.|
0020 6e 00 73 00 61 00 63 00-74 00 69 00 6f 00 6e 00 |n.s.a.c. t.i.o.n.|

ct.c:215:setting command state from READY to SENT
ct.c:965:ct_send() succeeded
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401998, 1401994, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 11 00 3d 01 00- |.....=..|

net.c:671:Received packet
0000 fd 00 00 d2 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 = 0
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
util.c:110:logic error: cannot change query state from IDLE to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4052)
ct.c:1262:ct_results() results state = 0
ct.c:1263:ct_results() command type = 148
ct.c:1264:ct_results() dynamic cmd = 711
ct.c:1100:ct_results()
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(4dc2e8, 1401998, 1401994, 0x6914)
token.c:498:tds_process_tokens() state is COMPLETED
ct.c:1157:ct_results() process_result_tokens returned 2 (type 4052)
ct.c:215:setting command state from SENT to READY







Archive powered by MHonArc 2.6.24.

Top of Page