[freetds] Sending Unicode (UTF-8) data to dbrpcparam - SQL Server receives blank string

[Marc Abramowitz]

Hi all,

I'm working on a bug that I discovered in pymssql (http://code.google.com/p/pymssql/issues/detail?id=109). I'm trying to send a string with 10 Unicode characters as a parameter to a stored procedure.

I've traced through the code and pymssql is taking the Unicode string of length 10 and encoding it to 20 UTF-8 bytes and then it calls dbrpcparam as follows:

dbrpcparam(proc = ???, param_name = '', status = 0, dbtype = 39, max_length = -1, length = 20, data = '\xd0\x97\xd0\xb4\xd1\x80\xd0\xb0\xd0\xb2\xd1\x81\xd1\x82\xd0\xb2\xd1\x83\xd0\xb9') => 1

The result is that SQL Server receives a blank string for that parameter. In the FreeTDS log, I can see an error related to character set conversion:

Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)

What's also interesting is that if instead of creating a stored proc and binding a parameter, I simply send a raw query with the Unicode string in it (preceded by the "N" prefix), then it works and the database receives the string as expected. The problem only occurs when explicitly instantiating the stored proc and binding the parameter to it.

Is this a known issue? Is this the same issue as this: http://lists.ibiblio.org/pipermail/freetds/2008q2/023367.html ?

Relevants snippets from the FreeTDS log:

log.c:196:Starting log file for FreeTDS 0.91
on 2013-02-27 13:28:32 with debug flags 0x4fff.
iconv.c:330:tds_iconv_open(0x10158bc40, UTF-8)
iconv.c:187:local name for ISO-8859-1 is ISO-8859-1
iconv.c:187:local name for UTF-8 is UTF-8
iconv.c:187:local name for UCS-2LE is UCS-2LE
iconv.c:187:local name for UCS-2BE is UCS-2BE
iconv.c:349:setting up conversions for client charset "UTF-8"
iconv.c:351:preparing iconv for "UTF-8" <-> "UCS-2LE" conversion
iconv.c:391:preparing iconv for "ISO-8859-1" <-> "UCS-2LE" conversion
iconv.c:394:tds_iconv_open: done
net.c:205:Connecting to 127.0.0.1 port 1433 (TDS version 7.1)
?
rpc.c:84:dbrpcinit(0x10159fac0, someProcWithOneParam, 0)
rpc.c:136:dbrpcinit() added rpcname "someProcWithOneParam"
rpc.c:171:dbrpcparam(0x10159fac0, , 0x0, 39, -1, 20, 0x101591680)
rpc.c:263:dbrpcparam() added parameter ""
dblib.c:3196:dbcancel(0x10159fac0)
query.c:2155:tds_send_cancel: not in_cancel and idle
rpc.c:282:dbrpcsend(0x10159fac0)
rpc.c:401:parm_info_alloc(): parameter null-ness = 0
rpc.c:331:parameter size = 20, data = 0x101567db0, row_size = 0
rpc.c:336:copying 20 bytes of data to parameter #0
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 20
util.c:331:tdserror(0x101506eb0, 0x10159fdb0, 2402, 0)
dblib.c:7929:dbperror(0x10159fac0, 2402, 0)
dblib.c:7981:2402: "Error converting characters into server's character set. Some character(s) could not be converted"
dblib.c:8002:"Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
query.c:1640:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 40 00 00 01 00-14 00 73 00 6f 00 6d 00 |... at .... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 a7 14-00 09 04 d0 00 34 00 00 |m....... .....4..|

rpc.c:319:dbrpcsend() returning SUCCEED

[Marc Abramowitz]

A little more info?

If I add the following:

diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..17f227c 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\"; colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, colsize, curcol->char_conv->client_charset.name, curcol->char_conv->server_charset.name);
s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */

Here's what I get:

query.c:1624:tds_put_data: calling tds_convert_string on "??????????^A"; colsize = 20; client_charset = "UTF-8"; server_charset = "CP1252"
util.c:331:tdserror(0x101506eb0, 0x10159fdb0, 2402, 0)
dblib.c:7929:dbperror(0x10159fac0, 2402, 0)
dblib.c:7981:2402: "Error converting characters into server's character set. Some character(s) could not be converted"
dblib.c:8002:"Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
query.c:1627:tds_put_data: result of tds_convert_string => "(null)"

Should I be suspicious of the "^A" (null byte?) tacked on to the end of my string? Maybe that 's why the conversion is failing?

[Marc Abramowitz]

Some more info?

I realized that CP1252 is a single byte encoding that isn't capable of representing Unicode strings and I read
in the FreeTDS manual that it encodes things to UCS-2 to send to the database and I
found examples of that in tds/query.c ? so I borrowed from some other spots
in tds/query.c and changed the code to this:

diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..95e5a4b 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
- s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\" (strlen %d); colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, (int) strlen(s), (int) colsize, tds->char_convs[client2ucs2]->client_charset.name, tds->char_convs[client2ucs2]->server_charset.name);
+ s = tds_convert_string(tds, tds->char_convs[client2ucs2], s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */

And that seems to help although now I get back a result that is encoded in UCS-2
(not sure if that is a FreeTDS bug or a pymssql bug).

Freetds log:

query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 20
query.c:1629:tds_put_data: calling tds_convert_string on "??????????^A" (strlen 21); colsize = 20; client_charset = "UTF-8"; server_charset = "UCS-2LE"
query.c:1632:tds_put_data: result of tds_convert_string => "^W^D4^D@^D0^D2^DA^DB^D2^DC^D9^Dt"
query.c:1648:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 54 00 00 01 00-14 00 73 00 6f 00 6d 00 |...T.... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 a7 14-00 09 04 d0 00 34 14 00 |m....... .....4..|
0040 17 04 34 04 40 04 30 04-32 04 41 04 42 04 32 04 |..4. at .0. 2.A.B.2.|
0050 43 04 39 04 - |C.9.|

Result:

AssertionError: u'\x17\x044\x04@\x040\x042\x04A\x04B\x042\x04C\x049\x04!' != u'\u0417\u0434\u0440\u0430\u0432\u0441\u0442\u0432\u0443\u0439!'

I am expecting to get back the Unicode string on the right, but what I get back is the UTF-16 string on the left.

From: Marc Abramowitz <marca at surveymonkey.com<mailto:marca at surveymonkey.com>>
Date: Wednesday, February 27, 2013 2:58 PM
To: "freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>" <freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>>
Subject: Re: Sending Unicode (UTF-8) data to dbrpcparam - SQL Server receives blank string

A little more info?

If I add the following:

diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..17f227c 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\"; colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, colsize, curcol->char_conv->client_charset.name, curcol->char_conv->server_charset.name);
s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */

Here's what I get:

query.c:1624:tds_put_data: calling tds_convert_string on "??????????^A"; colsize = 20; client_charset = "UTF-8"; server_charset = "CP1252"
util.c:331:tdserror(0x101506eb0, 0x10159fdb0, 2402, 0)
dblib.c:7929:dbperror(0x10159fac0, 2402, 0)
dblib.c:7981:2402: "Error converting characters into server's character set. Some character(s) could not be converted"
dblib.c:8002:"Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
query.c:1627:tds_put_data: result of tds_convert_string => "(null)"

Should I be suspicious of the "^A" (null byte?) tacked on to the end of my string? Maybe that 's why the conversion is failing?

[Frediano Ziglio]

You should try using XSYBNVARCHAR instead of SYBVARCHAR, you send
UCS-2/UTF-16 inside a multibyte string charset you'll end up in wrong
character set issues.

Frediano

Post by Marc Abramowitz
Some more info?
I realized that CP1252 is a single byte encoding that isn't capable of representing Unicode strings and I read
in the FreeTDS manual that it encodes things to UCS-2 to send to the database and I
found examples of that in tds/query.c ? so I borrowed from some other spots
diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..95e5a4b 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
- s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\" (strlen %d); colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, (int) strlen(s), (int) colsize, tds->char_convs[client2ucs2]->client_charset.name, tds->char_convs[client2ucs2]->server_charset.name);
+ s = tds_convert_string(tds, tds->char_convs[client2ucs2], s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */
And that seems to help although now I get back a result that is encoded in UCS-2
(not sure if that is a FreeTDS bug or a pymssql bug).
query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 20
query.c:1629:tds_put_data: calling tds_convert_string on "??????????^A" (strlen 21); colsize = 20; client_charset = "UTF-8"; server_charset = "UCS-2LE"
query.c:1648:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 54 00 00 01 00-14 00 73 00 6f 00 6d 00 |...T.... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 a7 14-00 09 04 d0 00 34 14 00 |m....... .....4..|
0040 17 04 34 04 40 04 30 04-32 04 41 04 42 04 32 04 |..4. at .0. 2.A.B.2.|
0050 43 04 39 04 - |C.9.|
I am expecting to get back the Unicode string on the right, but what I get back is the UTF-16 string on the left.
From: Marc Abramowitz <marca at surveymonkey.com<mailto:marca at surveymonkey.com>>
Date: Wednesday, February 27, 2013 2:58 PM
To: "freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>" <freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>>
Subject: Re: Sending Unicode (UTF-8) data to dbrpcparam - SQL Server receives blank string
A little more info?
diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..17f227c 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\"; colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, colsize, curcol->char_conv->client_charset.name, curcol->char_conv->server_charset.name);
s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */
query.c:1624:tds_put_data: calling tds_convert_string on "??????????^A"; colsize = 20; client_charset = "UTF-8"; server_charset = "CP1252"
util.c:331:tdserror(0x101506eb0, 0x10159fdb0, 2402, 0)
dblib.c:7929:dbperror(0x10159fac0, 2402, 0)
dblib.c:7981:2402: "Error converting characters into server's character set. Some character(s) could not be converted"
dblib.c:8002:"Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
query.c:1627:tds_put_data: result of tds_convert_string => "(null)"
Should I be suspicious of the "^A" (null byte?) tacked on to the end of my string? Maybe that 's why the conversion is failing?
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Frediano Ziglio]

Actually git version does what you want, if you look dbrpcparam

1) http://gitorious.org/freetds/freetds/blobs/master/src/dblib/rpc.c#line160
2) http://gitorious.org/freetds/freetds/blobs/Branch-0_91/src/dblib/rpc.c#line164

#1 is the last git version, #2 is the last 0.91, git have some lines

if (type == SYBVARCHAR && IS_TDS7_PLUS(dbproc->tds_socket->conn))
type = XSYBNVARCHAR;

that turns type to XSYBNVARCHAR instead of SYBVARCHAR. You should get
same result on 0.91 with

if (type == SYBVARCHAR && IS_TDS7_PLUS(dbproc->tds_socket))
type = XSYBNVARCHAR;

Frediano

Post by Marc Abramowitz
Some more info?
I realized that CP1252 is a single byte encoding that isn't capable of representing Unicode strings and I read
in the FreeTDS manual that it encodes things to UCS-2 to send to the database and I
found examples of that in tds/query.c ? so I borrowed from some other spots
diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..95e5a4b 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
- s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\" (strlen %d); colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, (int) strlen(s), (int) colsize, tds->char_convs[client2ucs2]->client_charset.name, tds->char_convs[client2ucs2]->server_charset.name);
+ s = tds_convert_string(tds, tds->char_convs[client2ucs2], s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */
And that seems to help although now I get back a result that is encoded in UCS-2
(not sure if that is a FreeTDS bug or a pymssql bug).
query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 20
query.c:1629:tds_put_data: calling tds_convert_string on "??????????^A" (strlen 21); colsize = 20; client_charset = "UTF-8"; server_charset = "UCS-2LE"
query.c:1648:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 54 00 00 01 00-14 00 73 00 6f 00 6d 00 |...T.... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 a7 14-00 09 04 d0 00 34 14 00 |m....... .....4..|
0040 17 04 34 04 40 04 30 04-32 04 41 04 42 04 32 04 |..4. at .0. 2.A.B.2.|
0050 43 04 39 04 - |C.9.|
I am expecting to get back the Unicode string on the right, but what I get back is the UTF-16 string on the left.
From: Marc Abramowitz <marca at surveymonkey.com<mailto:marca at surveymonkey.com>>
Date: Wednesday, February 27, 2013 2:58 PM
To: "freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>" <freetds at lists.ibiblio.org<mailto:freetds at lists.ibiblio.org>>
Subject: Re: Sending Unicode (UTF-8) data to dbrpcparam - SQL Server receives blank string
A little more info?
diff --git a/src/tds/query.c b/src/tds/query.c
index 168c4d5..17f227c 100644
--- a/src/tds/query.c
+++ b/src/tds/query.c
@@ -1621,7 +1621,10 @@ tds_put_data(TDSSOCKET * tds, TDSCOLUMN * curcol)
/* we need to convert data before */
/* TODO this can be a waste of memory... */
converted = 1;
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: calling tds_convert_string on \"%s\"; colsize = %d; client_charset = \"%s\"; server_charset = \"%s\"\n",
+ s, colsize, curcol->char_conv->client_charset.name, curcol->char_conv->server_charset.name);
s = tds_convert_string(tds, curcol->char_conv, s, colsize, &output_size);
+ tdsdump_log(TDS_DBG_INFO1, "tds_put_data: result of tds_convert_string => \"%s\"\n", s);
colsize = (TDS_INT)output_size;
if (!s) {
/* on conversion error put a empty string */
query.c:1624:tds_put_data: calling tds_convert_string on "??????????^A"; colsize = 20; client_charset = "UTF-8"; server_charset = "CP1252"
util.c:331:tdserror(0x101506eb0, 0x10159fdb0, 2402, 0)
dblib.c:7929:dbperror(0x10159fac0, 2402, 0)
dblib.c:7981:2402: "Error converting characters into server's character set. Some character(s) could not be converted"
dblib.c:8002:"Error converting characters into server's character set. Some character(s) could not be converted", client returns 2 (INT_CANCEL)
util.c:361:tdserror: client library returned TDS_INT_CANCEL(2)
util.c:384:tdserror: returning TDS_INT_CANCEL(2)
query.c:1627:tds_put_data: result of tds_convert_string => "(null)"
Should I be suspicious of the "^A" (null byte?) tacked on to the end of my string? Maybe that 's why the conversion is failing?
_______________________________________________
FreeTDS mailing list
FreeTDS at lists.ibiblio.org
http://lists.ibiblio.org/mailman/listinfo/freetds

[Marc Abramowitz]

Post by Frediano Ziglio
You should try using XSYBNVARCHAR instead of SYBVARCHAR, you send
UCS-2/UTF-16 inside a multibyte string charset you'll end up in wrong
character set issues.
Frediano

OK, I tried this and made a little bit of progress, though not all is
well and I've gotten myself a bit confused with the encodings.

I changed the pymssql code so that if it gets a Unicode string
parameter, it sends XSYBNVARCHAR.

The result is:

1. Now if I send that 10 glyph Russian string, encoded as 20 bytes of
UTF-8 (dbtype = XSYBNVARCHAR), everything seems to work. Basically,
the code is doing this:

dbrpcparam(
param_name='', status=0, db_type=231,
max_length=-1, data_length=20,

data='\xd0\x97\xd0\xb4\xd1\x80\xd0\xb0\xd0\xb2\xd1\x81\xd1\x82\xd0\xb2\xd1\
x83\xd0\xb9')

and in the freetds.log I get:

rpc.c:171:dbrpcparam(0x10159f440, , 0x0, 231, -1, 20, 0x10159fcc0)
rpc.c:263:dbrpcparam() added parameter ""
dblib.c:3196:dbcancel(0x10159f440)
query.c:2155:tds_send_cancel: not in_cancel and idle
rpc.c:282:dbrpcsend(0x10159f440)
rpc.c:401:parm_info_alloc(): parameter null-ness = 0
rpc.c:331:parameter size = 20, data = 0x101542020, row_size = 0
rpc.c:336:copying 20 bytes of data to parameter #0
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 20
query.c:1640:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 54 00 00 01 00-14 00 73 00 6f 00 6d 00 |...T.... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 e7 14-00 09 04 d0 00 34 14 00 |m....... .....4..|
0040 17 04 34 04 40 04 30 04-32 04 41 04 42 04 32 04 |..4. at .0. 2.A.B.2.|
0050 43 04 39 04 - |C.9.|

It looks like FreeTDS takes the 20 bytes of UTF-8 data I sent and
converts it to 20 bytes of UCS-2.

The result that I get back from my stored proc is what I expect.

So that's cool.

2. If I send a regular 5 glyph plain ol' English string, encoded as 5
bytes of UTF-8 (again with dbtype = XSYBNVARCHAR), things fail. The
code does this:

dbrpcparam(
param_name='', status=0, db_type=231,
max_length=-1, data_length=5,
data='hello')

and in the freetds.log I get:

rpc.c:171:dbrpcparam(0x10159f440, , 0x0, 231, -1, 5, 0x101591d50)
rpc.c:263:dbrpcparam() added parameter ""
dblib.c:3196:dbcancel(0x10159f440)
query.c:2155:tds_send_cancel: not in_cancel and idle
rpc.c:282:dbrpcsend(0x10159f440)
rpc.c:401:parm_info_alloc(): parameter null-ness = 0
rpc.c:331:parameter size = 5, data = 0x10157fd20, row_size = 0
rpc.c:336:copying 5 bytes of data to parameter #0
mem.c:615:tds_free_all_results()
util.c:156:Changed query state from IDLE to QUERYING
query.c:1437:tds_put_data_info putting param_name.
query.c:1471:tds_put_data_info putting status.
query.c:1572:tds_put_data: colsize = 5
query.c:1640:tds_put_data: not null param varint_size = 2
util.c:156:Changed query state from QUERYING to PENDING
net.c:741:Sending packet
0000 03 01 00 45 00 00 01 00-14 00 73 00 6f 00 6d 00 |...E.... ..s.o.m.|
0010 65 00 50 00 72 00 6f 00-63 00 57 00 69 00 74 00 |e.P.r.o. c.W.i.t.|
0020 68 00 4f 00 6e 00 65 00-50 00 61 00 72 00 61 00 |h.O.n.e. P.a.r.a.|
0030 6d 00 00 00 00 00 e7 05-00 09 04 d0 00 34 05 00 |m....... .....4..|
0040 68 00 65 00 6c - |h.e.l|

Note in the packet that it seems that FreeTDS looks like it tried to
convert 'hello' to UCS-2 but then only copied 5 bytes instead of 10 so
the string got truncated. I think this might be a bug?

and then later on a failure:

dbutil.c:86:msgno 8016: "The incoming tabular data stream (TDS) remote
procedure call (RPC) protocol stream is incorrect. Parameter 1 (""):
Data type 0xE7 has an invalid data length or metadata length."

So changing to XSYBNVARCHAR seems to have fixed the original problem
with sending wide strings, but then it breaks the previously working
case of sending non-wide strings.

Is this something that I can fix in the pymssql code by calling FreeTDS
a little differently (e.g.: send it data encoded in UCS-2?)? Or is this
because of a bug in FreeTDS?

Thanks,
Marc

[Marc Abramowitz]

Actually, I just tried out the git version and it appears that
everything works perfectly there and I didn't have to change any code in
pymssql.

So it seems that this was a bug present in FreeTDS 0.91 but not in the
git repo (the code looks quite different on git master from 0.91).

Now I wonder if there's a way to tweak the way pymssql does things so
that it can work properly with released versions of FreeTDS. Though
perhaps this is not worth the trouble.

Marc

Post by Marc Abramowitz
Is this something that I can fix in the pymssql code by calling FreeTDS
a little differently (e.g.: send it data encoded in UCS-2?)? Or is this
because of a bug in FreeTDS?