Re: Sending Unicode (UTF-8) data to dbrpcparam - SQL Server receives blank string

On 2/28/13 7:24 AM, "Frediano Ziglio" <[email protected]> wrote:

>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 |[email protected]. 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