FreeTDS Development Group

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

I would like to add one correction to my final conclusion. The
workaround to use an explicit convert() actually works for binary
strings of longer size also. The reason it didn't work for my earlier
test was because I happen to test against a different table that has a
column length of 1024, which explains why I got the truncation error
for binary strings longer than 1024. This is somewhat of a relief as
the convert() could be a viable workaround. I would still appreciate
if a proper solution can be found.

On Tue, Feb 23, 2010 at 3:41 PM, Hari Krishna Dara <haridara AT gmail.com> wrote:
> 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
>