FreeTDS Development Group

[Hari Krishna Dara <haridara AT gmail.com>]

I am trying to insert data into a varbinary column from python via
pyodbc and unixODBC and get the below error:

Traceback (most recent call last):
  File "test_pyodbc.py", line 36, in <module>
    cur.execute(sql, (pyodbc.Binary(data),))
pyodbc.DataError: ('22018', '[22018] [FreeTDS][SQL Server]Implicit
conversion from data type varchar to varbinary is not allowed. Use the
CONVERT function to run this query. (257) (SQLPrepare)')

The error occurs no matter what the data contains and how long it is
(happens with empty data also). I am using the below versions:

SQL Server 2005
freetds-0.83.dev.20100122
unixODBC-2.2.14-p2
pyodbc-2.0.58
python-2.6.2

Since I am using ODBC and FreeTDS in configuration less mode, I am not
posting my freetds.conf or odbc.ini files. The only important parts in
odbcinst.ini are the paths to the FreeTDS driver.

The table has just one single column like this:

CREATE TABLE testing (
    varbinary_col varbinary(4096) NULL
)

The following simple insert fails:

import pyodbc
con = 
pyodbc.connect('DRIVER={FreeTDS};SERVER=<server>;PORT=1433;DATABASE=engine;UID=<user>;PWD=<pwd>;CHARSET=UTF8;TDS_VERSION=8.0')
cur = con.cursor()
data = ''
sql = "INSERT INTO testing (varbinary_col) VALUES (?)"
cur.execute(sql, (pyodbc.Binary(data),))

If I insert into a column of type IMAGE (which is same as
varbinary(max)), then it works just fine, as I was able to read the
contents of various binary files up to size 4mb and insert
successfully.

I have the log from unixODBC and as well as FreeTDS for varbinary and
image data types and tried to compare but I couldn't find any clues.
All that I can see from unixODBC log is that the SQLPrepare exits with
SQL_ERROR instead of SQL_SUCCESS. Here are the FreeTDS logs around
SQLPrepare:

For IMAGE datatype:
-------------------------------------------------------
odbc.c:4256:SQLPrepare(0x0, INSERT INTO testing (myimage) VALUES (?),
-3)
  prepare_query.c:203:parsing 0 parameters
  prepare_query.c:208:parse_prepared_query: logic_error: parameter out
of bounds: 1 > 0 || 1 > 0
  odbc.c:4321:Creating prepared statement
  mem.c:603:tds_free_all_results()
  util.c:162:Changed query state from IDLE to QUERYING
  write.c:134:tds_put_string converting 3 bytes of "@P1"
  write.c:162:tds_put_string wrote 6 bytes
  util.c:162:Changed query state from QUERYING to PENDING
  net.c:786:Sending packet
  0000 03 01 00 ae 00 00 01 00-ff ff 0b 00 00 00 00 01 |........
........|
  0010 26 04 00 00 00 63 1e 00-00 00 09 04 d0 00 34 1e |&....c.. ......4.|
  0020 00 00 00 40 00 50 00 31-00 20 00 76 00 61 00 72 |...@.P.1 . .v.a.r|
  0030 00 63 00 68 00 61 00 72-00 28 00 38 00 30 00 29 |.c.h.a.r .(.8.0.)|
  0040 00 00 00 63 54 00 00 00-09 04 d0 00 34 54 00 00 |...cT...
....4T..|
  0050 00 49 00 4e 00 53 00 45-00 52 00 54 00 20 00 49 |.I.N.S.E .R.T. .I|
  0060 00 4e 00 54 00 4f 00 20-00 74 00 65 00 73 00 74 |.N.T.O.  .t.e.s.t|
  0070 00 69 00 6e 00 67 00 20-00 28 00 6d 00 79 00 69 |.i.n.g.
.(.m.y.i|
  0080 00 6d 00 61 00 67 00 65-00 29 00 20 00 56 00 41 |.m.a.g.e .).
.V.A|
  0090 00 4c 00 55 00 45 00 53-00 20 00 28 00 40 00 50 |.L.U.E.S .
.(.@.P|
  00a0 00 31 00 29 00 00 00 26-04 04 01 00 00 00       |.1.)...&
......|

  token.c:498:tds_process_tokens(0x0, 0x0, 0x0, 0x0)
  util.c:162:Changed query state from PENDING to READING
  net.c:605:Received header
  0000 04 01 00 2f 00 46 01 00-                        |.../.F..|

  net.c:659:Received packet
  0000 04 01 00 2f 00 46 01 00-ff 11 00 c3 00 00 00 00 |.../.F..
........|
  0010 00 79 00 00 00 00 ac 0d-00 00 01 00 00 00 00 26 |.y......
.......&|
  0020 04 04 01 00 00 00 fe 00-00 e0 00 00 00 00 00    |........
.......|
-------------------------------------------------------

For VARBINARY datatype:
-------------------------------------------------------
odbc.c:4256:SQLPrepare(0x0, INSERT INTO testing (varbinary_col) VALUES
(?), -3)
  prepare_query.c:203:parsing 0 parameters
  prepare_query.c:208:parse_prepared_query: logic_error: parameter out
of bounds: 1 > 0 || 1 > 0
  odbc.c:4321:Creating prepared statement
  mem.c:603:tds_free_all_results()
  util.c:162:Changed query state from IDLE to QUERYING
  write.c:134:tds_put_string converting 3 bytes of "@P1"
  write.c:162:tds_put_string wrote 6 bytes
  util.c:162:Changed query state from QUERYING to PENDING
  net.c:786:Sending packet
  0000 03 01 00 ba 00 00 01 00-ff ff 0b 00 00 00 00 01 |........
........|
  0010 26 04 00 00 00 63 1e 00-00 00 09 04 d0 00 34 1e |&....c.. ......4.|
  0020 00 00 00 40 00 50 00 31-00 20 00 76 00 61 00 72 |...@.P.1 . .v.a.r|
  0030 00 63 00 68 00 61 00 72-00 28 00 38 00 30 00 29 |.c.h.a.r .(.8.0.)|
  0040 00 00 00 63 60 00 00 00-09 04 d0 00 34 60 00 00 |...c`...
....4`..|
  0050 00 49 00 4e 00 53 00 45-00 52 00 54 00 20 00 49 |.I.N.S.E .R.T. .I|
  0060 00 4e 00 54 00 4f 00 20-00 74 00 65 00 73 00 74 |.N.T.O.  .t.e.s.t|
  0070 00 69 00 6e 00 67 00 20-00 28 00 76 00 61 00 72 |.i.n.g.
.(.v.a.r|
  0080 00 62 00 69 00 6e 00 61-00 72 00 79 00 5f 00 63 |.b.i.n.a
.r.y._.c|
  0090 00 6f 00 6c 00 29 00 20-00 56 00 41 00 4c 00 55 |.o.l.).
.V.A.L.U|
  00a0 00 45 00 53 00 20 00 28-00 40 00 50 00 31 00 29 |.E.S. .(
.@.P.1.)|
  00b0 00 00 00 26 04 04 01 00-00 00                   |...&.... ..|

  token.c:498:tds_process_tokens(0x0, 0x0, 0x0, 0x0)
  util.c:162:Changed query state from PENDING to READING
  net.c:605:Received header
  0000 04 01 01 8c 00 46 01 00-                        |.....F..|

  net.c:659:Received packet
  0000 04 01 01 8c 00 46 01 00-aa 02 01 01 01 00 00 03 |.....F..
........|
  0010 10 73 00 49 00 6d 00 70-00 6c 00 69 00 63 00 69 |.s.I.m.p
.l.i.c.i|
  0020 00 74 00 20 00 63 00 6f-00 6e 00 76 00 65 00 72 |.t. .c.o
.n.v.e.r|
  0030 00 73 00 69 00 6f 00 6e-00 20 00 66 00 72 00 6f |.s.i.o.n .
.f.r.o|
  0040 00 6d 00 20 00 64 00 61-00 74 00 61 00 20 00 74 |.m. .d.a .t.a.
.t|
  0050 00 79 00 70 00 65 00 20-00 76 00 61 00 72 00 63 |.y.p.e.
.v.a.r.c|
  0060 00 68 00 61 00 72 00 20-00 74 00 6f 00 20 00 76 |.h.a.r.  .t.o.
.v|
  0070 00 61 00 72 00 62 00 69-00 6e 00 61 00 72 00 79 |.a.r.b.i
.n.a.r.y|
  0080 00 20 00 69 00 73 00 20-00 6e 00 6f 00 74 00 20 |. .i.s.
.n.o.t. |
  0090 00 61 00 6c 00 6c 00 6f-00 77 00 65 00 64 00 2e |.a.l.l.o
.w.e.d..|
  00a0 00 20 00 55 00 73 00 65-00 20 00 74 00 68 00 65 |. .U.s.e .
.t.h.e|
  00b0 00 20 00 43 00 4f 00 4e-00 56 00 45 00 52 00 54 |. .C.O.N
.V.E.R.T|
  00c0 00 20 00 66 00 75 00 6e-00 63 00 74 00 69 00 6f |. .f.u.n
.c.t.i.o|
  00d0 00 6e 00 20 00 74 00 6f-00 20 00 72 00 75 00 6e |.n. .t.o .
.r.u.n|
  00e0 00 20 00 74 00 68 00 69-00 73 00 20 00 71 00 75 |. .t.h.i .s.
.q.u|
  00f0 00 65 00 72 00 79 00 2e-00 08 53 00 55 00 52 00 |.e.r.y..
..S.U.R.|
  0100 46 00 49 00 4e 00 47 00-32 00 00 01 00 aa 62 00 |F.I.N.G.
2.....b.|
  0110 f4 1f 00 00 01 10 23 00-53 00 74 00 61 00 74 00 |......#.
S.t.a.t.|
  0120 65 00 6d 00 65 00 6e 00-74 00 28 00 73 00 29 00 |e.m.e.n.
t.(.s.).|
  0130 20 00 63 00 6f 00 75 00-6c 00 64 00 20 00 6e 00 | .c.o.u. l.d.
.n.|
  0140 6f 00 74 00 20 00 62 00-65 00 20 00 70 00 72 00 |o.t. .b. e.
.p.r.|
  0150 65 00 70 00 61 00 72 00-65 00 64 00 2e 00 08 53 |e.p.a.r.
e.d....S|
  0160 00 55 00 52 00 46 00 49-00 4e 00 47 00 32 00 00 |.U.R.F.I
.N.G.2..|
  0170 01 00 79 f4 1f 00 00 ac-09 00 00 01 00 00 00 00 |..y.....
........|
  0180 26 04 00 fe 02 00 e0 00-00 00 00 00             |&....... ....|
-------------------------------------------------------

I also tried to explicitly convert to varbinary like this:

import pyodbc
con = 
pyodbc.connect('DRIVER={FreeTDS};SERVER=<server>;PORT=1433;DATABASE=engine;UID=<user>;PWD=<pwd>;CHARSET=UTF8;TDS_VERSION=8.0')
cur = con.cursor()
data = ''
sql = "INSERT INTO testing (varbinary_col) VALUES (convert(varbinary(4096), 
?))"
cur.execute(sql, (data,))

This actually works and I can insert small binary strings, such as of
length 610, but for longer strings of length 1236 and 2684, I get this
error:

Traceback (most recent call last):
  File "test_pyodbc.py", line 37, in <module>
    cur.execute(sql, (pyodbc.Binary(data),))
pyodbc.Error: ('HY000', 'The driver did not supply an error!')

Here are the messages I can see In FreeTDS dump file with some error codes:

-------------------------------------------------------
token.c:513:processing result tokens.  marker is  aa(ERROR)
token.c:111:tds_process_default_tokens() marker is aa(ERROR)
token.c:2643:tds_process_msg() reading message 8152 from server
token.c:2716:tds_process_msg() calling client msg handler
odbc.c:2101:msgno 8152 20003
token.c:2729:tds_process_msg() returning TDS_SUCCEED
token.c:513:processing result tokens.  marker is  ab(INFO)
token.c:111:tds_process_default_tokens() marker is ab(INFO)
token.c:2643:tds_process_msg() reading message 3621 from server
token.c:2716:tds_process_msg() calling client msg handler
odbc.c:2101:msgno 3621 20003
token.c:2729:tds_process_msg() returning TDS_SUCCEED
token.c:513:processing result tokens.  marker is  ff(DONEINPROC)
token.c:2394:tds_process_end: more_results = 1
                was_cancelled = 0
                error = 1
                done_count_valid = 0
token.c:2425:                rows_affected = 0
util.c:162:Changed query state from READING to PENDING
odbc.c:3363:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3364:    result_type=4054, TDS_DONE_COUNT=0, TDS_DONE_ERROR=2
odbc.c:3428:odbc_process_tokens: processed TDS_DONEINPROC_RESULT
odbc.c:3203:_SQLExecute: odbc_process_tokens returned result_type 4054
token.c:498:tds_process_tokens(0x9aa11a8, 0xbf8f9114, 0xbf8f9110, 0x6914)
util.c:162:Changed query state from PENDING to READING
token.c:513:processing result tokens.  marker is  79(RETURNSTATUS)
token.c:513:processing result tokens.  marker is  fe(DONEPROC)
token.c:2394:tds_process_end: more_results = 0
                was_cancelled = 0
                error = 0
                done_count_valid = 0
token.c:2410:tds_process_end() state set to TDS_IDLE
util.c:162:Changed query state from READING to IDLE
token.c:2425:                rows_affected = 0
util.c:110:logic error: cannot change query state from IDLE to PENDING
odbc.c:3363:odbc_process_tokens: tds_process_tokens returned 1
odbc.c:3364:    result_type=4053, TDS_DONE_COUNT=0, TDS_DONE_ERROR=0
odbc.c:3404:odbc_process_tokens: row_count=-1
odbc.c:3203:_SQLExecute: odbc_process_tokens returned result_type 4053
odbc.c:5934:SQLParamData returns SQL_ERROR
error.c:616:SQLGetDiagRec(3, 0x9a92188, 1, 0xbf8f91da, 0xbf8f91e8,
0xbf8f8fda, 512, 0xbf8f91e4)
error.c:562:_SQLError: "String or binary data would be truncated."
error.c:621:SQLGetDiagRec returning 0
error.c:616:SQLGetDiagRec(3, 0x9a92188, 2, 0xbf8f91da, 0xbf8f91e8,
0xbf8f8fda, 512, 0xbf8f91e4)
error.c:562:_SQLError: "The statement has been terminated."
error.c:621:SQLGetDiagRec returning 0
-------------------------------------------------------

It seems like there is a truncation error, though column length (4096)
is bigger than the data I am trying to insert (2684 or even 1236).

I tried to search on the internet for several different keyword
variations and didn't find any helpful hits. I found that the same
error appears in the FreeTDS test log (for both 0.82 and 0.83
versions) for DBD:ODBC, e.g., see:
http://freetds.sourceforge.net/up/out83/test/test.1.html. I am not
sure if these are related though. I would appreciate if anyone can
help me with a solution or workaround for this problem. If anyone
thinks it will help, I can send the full log files for unixODBC and
FreeTDS.

-- 
Thank you,
Hari