[freetds] error with freetds, python and Sql Server 2003
Erik Myllymaki
erik.myllymaki at aviawest.com
Tue Apr 4 17:33:18 EDT 2006
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
More information about the FreeTDS
mailing list