[egenix-users] Newlines and VARBINARY

[Alexandre Pigolkine]

Hello all,

I try to use mx.ODBC in the following environment:
mx.ODBC, Linux, iODBC, FreeTDS-Library, Microsoft SQL Server.

I get an error message when I try to insert a string with
new lines into a table with a TEXT field:

import mx.ODBC
con = mx.ODBC.iODBC.DriverConnect("DRIVER={SQL Server};SERVER=<server_ip>;DATABASE=<dbname>;UID=<user>;PWD=<pwd>")
cur = con.cursor()
cur.execute("CREATE TABLE #test (t text)")
cur.close()
cur = con.cursor()
cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
cur.close()
con.close()

/etc/odbcinst.ini:
[ODBC Drivers]
TDS MSSQL = installed

[SQL Server]
Description = TDS MSSQL ODBC Driver v 4.2
Driver = /usr/local/lib/libtdsodbc.so

after the line cur.execute("INSERT ... ") I get an error:
>>> cur.execute("INSERT INTO #test(t) VALUES(?)", ('test\n',))
Traceback (most recent call last):
  File "<stdin>", line 1, in ?
mx.ODBC.Error.ProgrammingError: ('22018', 206, '[FreeTDS][SQL Server]Operand type clash: varbinary is incompatible with text', 7028)

Part of freetds-Log:

token.c:2159:tds_process_end() state set to TDS_IDLE
util.c:119:Changing query state from READING to IDLE
util.c:119:Changing query state from IDLE to PENDING
token.c:514:tds_process_tokens() state is COMPLETED
sql2tds.c:151:type=-3
sql2tds.c:157:trace
sql2tds.c:344:trace
odbc.c:2506:Creating prepared statement
util.c:119:Changing query state from IDLE to QUERYING
mem.c:519:tds_free_all_results()
write.c:134:tds_put_string converting 3 bytes of "@P1"
write.c:162:tds_put_string wrote 6 bytes
util.c:119:Changing query state from QUERYING to PENDING
net.c:673:Sending packet
0000 03 01 00 9c 00 00 01 00-ff ff 0b 00 00 00 00 01 |........ ........|
0010 26 04 00 00 00 63 20 00-00 00 09 04 c0 00 69 20 |&....c . ......i |
0020 00 00 00 40 00 50 00 31-00 20 00 56 00 41 00 52 |... at .P.1 . .V.A.R|
0030 00 42 00 49 00 4e 00 41-00 52 00 59 00 28 00 39 |.B.I.N.A .R.Y.(.9|
0040 00 29 00 00 00 63 40 00-00 00 09 04 c0 00 69 40 |.)...c at . ......i@|
0050 00 00 00 49 00 4e 00 53-00 45 00 52 00 54 00 20 |...I.N.S .E.R.T. |
0060 00 49 00 4e 00 54 00 4f-00 20 00 23 00 74 00 65 |.I.N.T.O . .#.t.e|
0070 00 73 00 74 00 28 00 74-00 29 00 20 00 56 00 41 |.s.t.(.t .). .V.A|
0080 00 4c 00 55 00 45 00 53-00 28 00 40 00 50 00 31 |.L.U.E.S .(. at .P.1|
0090 00 29 00 00 00 26 04 04-01 00 00 00             |.)...&.. ....|

util.c:119:Changing query state from PENDING to READING
net.c:446:Received header
0000 04 01 01 18 00 3b 01 00-                        |.....;..|

net.c:542:Received packet
0000 aa 8c 00 ce 00 00 00 02-10 37 00 4f 00 70 00 65 |........ .7.O.p.e|
0010 00 72 00 61 00 6e 00 64-00 20 00 74 00 79 00 70 |.r.a.n.d . .t.y.p|
0020 00 65 00 20 00 63 00 6c-00 61 00 73 00 68 00 3a |.e. .c.l .a.s.h.:|
0030 00 20 00 76 00 61 00 72-00 62 00 69 00 6e 00 61 |. .v.a.r .b.i.n.a|
0040 00 72 00 79 00 20 00 69-00 73 00 20 00 69 00 6e |.r.y. .i .s. .i.n|
0050 00 63 00 6f 00 6d 00 70-00 61 00 74 00 69 00 62 |.c.o.m.p .a.t.i.b|
0060 00 6c 00 65 00 20 00 77-00 69 00 74 00 68 00 20 |.l.e. .w .i.t.h. |
0070 00 74 00 65 00 78 00 74-00 09 54 00 52 00 56 00 |.t.e.x.t ..T.R.V.|
0080 4d 00 53 00 52 00 56 00-30 00 31 00 00 01 00 aa |M.S.R.V. 0.1.....|
0090 64 00 f4 1f 00 00 01 10-23 00 53 00 74 00 61 00 |d....... #.S.t.a.|
00a0 74 00 65 00 6d 00 65 00-6e 00 74 00 28 00 73 00 |t.e.m.e. n.t.(.s.|
00b0 29 00 20 00 63 00 6f 00-75 00 6c 00 64 00 20 00 |). .c.o. u.l.d. .|
00c0 6e 00 6f 00 74 00 20 00-62 00 65 00 20 00 70 00 |n.o.t. . b.e. .p.|
00d0 72 00 65 00 70 00 61 00-72 00 65 00 64 00 2e 00 |r.e.p.a. r.e.d...|
00e0 09 54 00 52 00 56 00 4d-00 53 00 52 00 56 00 30 |.T.R.V.M .S.R.V.0|
00f0 00 31 00 00 01 00 79 f4-1f 00 00 ac 09 00 00 01 |.1....y. ........|
0100 00 00 00 00 26 04 00 fe-02 00 e0 00 00 00 00 00 |....&... ........|


Is there a way to avoid such behavior and force mxODBC to always pass 
strings as a varchar-type?

Thanks a lot in advance.

Alexander Pigolkin