Skip to Content.
Sympa Menu

freetds - Re: [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 Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] problem with ct_dynamic(....CS_PREPARE...) against SQL Server 2005
  • Date: Tue, 3 Mar 2009 15:40:09 +0100

> ct-lib relies on server-side support to determine the SQL datatype of
the
> placeholder variable. Sybase provides such support, Microsoft does not.

> Thus ct-lib placeholders don't work with Microsoft servers and TDS 7.0+.


> --jkl
OK that is clear.
I am trying to work around this now, so instead of asking the server to
describe
the placeholder parameters for me, I will now just go with the datatypes
that
that are being passed in by my caller and assume that they have the right
type.
If they don't the DML will likely fail with an appropriate error message.

This approach works, even though the initial statement prepare package
that is sent to the server lists all parameters as varchar(80)
(see log attached below, this is for tds 8.0 against SQL Server 2005)

***********************************************************************
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 98 00 00 01 00-ff ff 0b 00 00 00 00 01 |........ ........|
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 fe 00 00 00-09 04 d0 00 34 fe 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 66 00 75 00 6e |.i.n.t.o . .f.u.n|
00b0 00 64 00 6d 00 73 00 74-00 72 00 28 00 73 00 79 |.d.m.s.t .r.(.s.y|
00c0 00 6d 00 62 00 6f 00 6c-00 2c 00 20 00 6c 00 6f |.m.b.o.l .,. .l.o|
00d0 00 6e 00 67 00 6e 00 61-00 6d 00 65 00 2c 00 20 |.n.g.n.a .m.e.,. |
00e0 00 64 00 65 00 66 00 5f-00 61 00 74 00 74 00 72 |.d.e.f._ .a.t.t.r|
00f0 00 5f 00 74 00 65 00 6d-00 70 00 6c 00 2c 00 20 |._.t.e.m .p.l.,. |
0100 00 6f 00 77 00 6e 00 65-00 72 00 5f 00 69 00 64 |.o.w.n.e .r._.i.d|
0110 00 2c 00 20 00 67 00 72-00 6f 00 75 00 70 00 5f |.,. .g.r .o.u.p._|
0120 00 69 00 64 00 2c 00 20-00 70 00 65 00 72 00 6d |.i.d.,. .p.e.r.m|
0130 00 69 00 73 00 73 00 69-00 6f 00 6e 00 73 00 29 |.i.s.s.i .o.n.s.)|
0140 00 20 00 76 00 61 00 6c-00 75 00 65 00 73 00 28 |. .v.a.l .u.e.s.(|
0150 00 40 00 50 00 31 00 2c-00 20 00 40 00 50 00 32 |.@.P.1., . .@.P.2|
0160 00 2c 00 20 00 27 00 53-00 48 00 41 00 52 00 45 |.,. .'.S .H.A.R.E|
0170 00 27 00 2c 00 20 00 40-00 50 00 33 00 2c 00 20 |.'.,. .@ .P.3.,. |
0180 00 31 00 30 00 31 00 2c-00 20 00 30 00 29 00 00 |.1.0.1., . .0.)..|
0190 00 26 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(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 2f 00 40 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 01 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(0x945a0d0, 0xd0a498)
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: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(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 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:3460:ct_setparam() command type = 160, data type = 0
ct.c:4288: _ct_fill_param() status = 256
ct.c:2003:_ct_get_server_type(0)
ct.c:3530:ct_setparam() added dynamic parameter
ct.c:3460:ct_setparam() command type = 160, data type = 0
ct.c:4288: _ct_fill_param() status = 256
ct.c:2003:_ct_get_server_type(0)
ct.c:3530:ct_setparam() added dynamic parameter
ct.c:3460:ct_setparam() command type = 160, data type = 8
ct.c:4288: _ct_fill_param() status = 256
ct.c:2003:_ct_get_server_type(8)
ct.c:3530:ct_setparam() added dynamic parameter
ct.c:844:ct_send() command_type = 160
ct.c:4203:paraminfoalloc: status = 256, maxlen 255
ct.c:4204:paraminfoalloc: name = , varint size 2 column_type 47 size 255,
255 column_cur_size 5 column_output = 0
ct.c:4043:paramrowalloc, size = 5, data = 0x96c30c8, row_size = 0
ct.c:4203:paraminfoalloc: status = 256, maxlen 255
ct.c:4204:paraminfoalloc: name = , varint size 2 column_type 47 size 255,
255 column_cur_size 16 column_output = 0
ct.c:4043:paramrowalloc, size = 16, data = 0x96c31d0, row_size = 0
ct.c:4203:paraminfoalloc: status = 256, maxlen 4
ct.c:4204:paraminfoalloc: name = , varint size 1 column_type 56 size 4, 4
column_cur_size 4 column_output = 0
ct.c:4043:paramrowalloc, size = 4, data = 0x9ad6500, row_size = 0
query.c:1631:tds_submit_execute()
mem.c:563:tds_free_all_results()
util.c:162:Changed query state from IDLE to QUERYING
query.c:1326:tds_put_data_info putting status
query.c:1420:tds_put_data: colsize = 5
query.c:1449:tds_put_data: not null param varint_size = 2
query.c:1326:tds_put_data_info putting status
query.c:1420:tds_put_data: colsize = 16
query.c:1449:tds_put_data: not null param varint_size = 2
query.c:1326:tds_put_data_info putting status
query.c:1420:tds_put_data: colsize = 4
query.c:1449:tds_put_data: not null param varint_size = 1
util.c:162:Changed query state from QUERYING to PENDING
net.c:779:Sending packet
0000 03 01 00 5f 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 01 00 00-00 00 00 af ff 00 09 04 |..&..... ........|
0030 d0 00 34 05 00 54 45 53-54 32 00 00 af ff 00 09 |..4..TES T2......|
0040 04 d0 00 34 10 00 41 4e-4f 54 48 45 52 20 54 45 |...4..AN OTHER TE|
0050 53 54 20 41 44 4f 00 00-26 04 04 64 00 00 00 |ST ADO.. &..d...|

ct.c:215:setting command state from READY to SENT
ct.c:1100:ct_results()
token.c:495:tds_process_tokens(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
net.c:592:Received header
0000 04 01 00 3a 00 40 01 00- |...:.@..|

net.c:671:Received packet
0000 ff 01 00 c0 00 00 00 00-00 ff 11 00 c5 00 01 00 |........ ........|
0010 00 00 ff 11 00 c3 00 01-00 00 00 ff 11 00 c3 00 |........ ........|
0020 01 00 00 00 79 00 00 00-00 fe 00 00 e0 00 00 00 |....y... ........|
0030 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 = 0
token.c:2232: rows_affected = 0
util.c:162:Changed query state from READING to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4054)
token.c:495:tds_process_tokens(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
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 = 1
util.c:162:Changed query state from READING to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4054)
token.c:495:tds_process_tokens(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
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 = 1
util.c:162:Changed query state from READING to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4054)
token.c:495:tds_process_tokens(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
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 = 1
util.c:162:Changed query state from READING to PENDING
ct.c:1157:ct_results() process_result_tokens returned 1 (type 4054)
token.c:495:tds_process_tokens(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 0x6914)
util.c:162:Changed query state from PENDING to READING
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: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(0x945a0d0, 0xd0a4e4, 0xd0a4e0, 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

***********************************************************************

Now I just need one more piece of the puzzle.
To recap here is what I do now:

ct_dynamic(cmd, CS_PREPARE, ...)
ct_send(cmd)
ct_results(cmd, &res_type)
ct_dynamic(cmd, CS_EXECUTE, ...)
forall (parameters)
ct_setparam(cmd, &datafmt, data, &len, &ind)
ct_send(cmd)
ct_results(cmd, &res_type)
if (res_type == CS_SUCCEED)
ct_res_info(cmd, CS_ROW_COUNT, ...)

In the last ct_results() call it appears that the packet
that is received from the server contains 6 'result tokens' (see log).
For three of them done_count_valid holds and therefore rows_affected
contains a valid value and it is copied into the TDSSOCKET * struct.
This is information that I am interested in.
However the last (DONEPROC) token does not contain a valid done_count,
and therefore inside tds_process_end() tds->rows_affected is reset to
TDS_NO_COUNT.
Now ct_results() returns with a result type of CS_CMS_SUCCEED, and I
should be able to call ct_res_info(...CS_ROW_COUNT...) to obtain the
number
of rows affected by my statement. Because of the processing inside
ct_results()
this information is unfortunately lost, and ct_res_info() returns
CS_NO_COUNT.
Another thing is that there is a logic error reported at util.c line
110...

A quick fix would be to remove the else part near the bottom of
tds_process_end(),
so a valid affected rows count does not get overwritten and I have a
chance
to pick it up with ct_res_info().
I suppose in that case it may be necessary to set it to TDS_NO_COUNT
somewhere
else, e.g. in tds_free_all_results().
Is there anybody that can shed some light on this?

btw. the behaviour against Sybase ASE 12.5 using tds 5.0 is identical,
again I 'lose' the affected rows, and there is the same logic error.

regards,
Tim Knigge





Archive powered by MHonArc 2.6.24.

Top of Page