Skip to Content.
Sympa Menu

freetds - Re: [freetds] error with freetds, python and Sql Server 2003

freetds AT lists.ibiblio.org

Subject: FreeTDS Development Group

List archive

Chronological Thread  
  • From: Erik Myllymaki <erik.myllymaki AT aviawest.com>
  • To: "ZIGLIO, Frediano, VF-IT" <Frediano.Ziglio AT vodafone.com>
  • Cc: FreeTDS Development Group <freetds AT lists.ibiblio.org>
  • Subject: Re: [freetds] error with freetds, python and Sql Server 2003
  • Date: Tue, 04 Apr 2006 14:33:18 -0700

ZIGLIO, Frediano, VF-IT wrote:
freetds 0.63-1.1 on RedHat Enterprise Linux 3.

Python 2.3 using mx.ODBC v 2.0.7

I am using a python script to connect to MSSQL Server 2003, and am having trouble inserting any strings with escaped characters ('\n','\t',etc.)


mssql 2003 ??? It doesn't exist.... ms released mssql 2000 and mssql
2005...

sorry, that should have been MSSQL 2000 on Windows 2003 Server.

and, I think I have incorrectly identified my troubles. I am going to try and summarize here and then will post the freetds.log file (large!) if need be.

My aim is to store emails in MSSQL via a python script on Linux. The script works on Windows but not Linux using freetds.

In particular, it i the size of the python strings to be inserted that causes the errors:

inserting string <= 255 characters into a MSSQL VARBINARY(8000) field works

inserting string of length 256 characters into a MSSQL VARBINARY(8000) errors
with:
mx.ODBC.Error.Warning: ('22001', 8152, '[unixODBC][FreeTDS][SQL Server]String or binary data would be truncated.', 6083)

inserting A string =< 255 characters into TEXT field, errors with:

mx.ODBC.Error.OperationalError: ('', 206, '[unixODBC][FreeTDS][SQL Server]Operand type clash: varbinary is incompatible with text', 6083)

string > 255 characters into MSSQL TEXT field errors with:

mx.ODBC.Error.OperationalError: ('', 206, '[unixODBC][FreeTDS][SQL Server]Operand type clash: image is incompatible with text', 6083)

inserting string <= 255 characters into a MSSQL NTEXT field, errors with:

mx.ODBC.Error.OperationalError: ('', 8180, '[unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.', 6083)

string > 255 characters into a MSSQL NTEXT field errors with:
mx.ODBC.Error.OperationalError: ('', 8180, '[unixODBC][FreeTDS][SQL Server]Statement(s) could not be prepared.', 6083)


SQL Query:

mystring = r'''Various strings of various lengths
'''

sql = "insert into DEVEL..email_bodypart(message_id,part_number,name,type,encoding,body) values(?,?,?,?,?,?)"

c.execute(sql, (10000,1,'filename','file','8bit',mystring))


freetds.conf:
[MSSQL2000]
host = 192.168.1.9
port = 1433
tds version = 8.0
dump file = /tmp/freetds.log
client charset = UTF-8



Any strings with carriage returns or newlines error with:

Error Type: InterfaceError
Error Value: ('HY004', 0, '[unixODBC][FreeTDS][SQL Server]Invalid data type', 5665)

the important part of the logfile:

Received packet @ 08:13:57.747411
0000 aa 82 00 ce 00 00 00 02-10 34 00 4f 00 70 00 65 |........ .4.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 69 00 6d 00 61-00 67 00 65 00 20 00 69 |. .i.m.a .g.e. .i|
0040 00 73 00 20 00 69 00 6e-00 63 00 6f 00 6d 00 70 |.s. .i.n .c.o.m.p|
0050 00 61 00 74 00 69 00 62-00 6c 00 65 00 20 00 77 |.a.t.i.b .l.e. .w|
0060 00 69 00 74 00 68 00 20-00 6e 00 74 00 65 00 78 |.i.t.h. .n.t.e.x|
0070 00 74 00 07 4e 00 41 00-4e 00 53 00 51 00 4c 00 |.t..N.A. N.S.Q.L.|
0080 33 00 00 01 00 aa 60 00-f4 1f 00 00 01 10 23 00 |3.....`. ......#.|
0090 53 00 74 00 61 00 74 00-65 00 6d 00 65 00 6e 00 |S.t.a.t. e.m.e.n.|
00a0 74 00 28 00 73 00 29 00-20 00 63 00 6f 00 75 00 |t.(.s.). .c.o.u.|
00b0 6c 00 64 00 20 00 6e 00-6f 00 74 00 20 00 62 00 |l.d. .n. o.t. .b.|
00c0 65 00 20 00 70 00 72 00-65 00 70 00 61 00 72 00 |e. .p.r. e.p.a.r.|
00d0 65 00 64 00 2e 00 07 4e-00 41 00 4e 00 53 00 51 |e.d....N .A.N.S.Q|
00e0 00 4c 00 33 00 00 01 00-79 f4 1f 00 00 ac 09 00 |.L.3.... y.......|
00f0 00 01 38 00 00 00 26 04-00 fe 02 00 e0 00 01 00 |..8...&. ........|
0100 00 00 - |..|



Could you post more log. The query would be very useful.

What I am trying to insert is just HTML without embedded images.

Thnaks for any direction you can provide.

freddy77




Archive powered by MHonArc 2.6.24.

Top of Page