binary data into MS-SQL server via ODBC

[Jimmy Retzlaff]

Steven (sadams123 at optushome.com.au) wrote:
>Could anyone provide a snippet of sample code to insert binary (image)
>data into a database.
>
>I'm current using the ODBC module on WinXP to connect to an MS-SQL
Server,
>the field is an 'image' type.
>
>Inserting text works fine, however when trying to insert the data from
an
>image I get:
>
>TypeError: argument 1 must be string without null bytes, not str
>
>I'm using the the cursor.execute and giving it my own "INSERT foo
(x,y,z)
>VALUES (.... " SQL statements, but cannot get it to work, any help is
much
>appreciated (and needed)

The answer may depend somewhat on which module you're using to talk to
ODBC from your Python code. If you're using the one included with
Win32all (which is also included in the ActiveState distribution of
Python), this should work:

import dbi
import odbc
connectionString = 'Driver={SQLServer};Server=(local);Database=tempdb'
cursor = odbc.odbc(connectionString).cursor()
cursor.execute('create table #testtable (testcolumn image)')
cursor.execute('insert #testtable (testcolumn) values (?)',
                          (dbi.dbiRaw('as\x00\x01\x02df'),)
              )
cursor.execute('select * from #testtable')
print repr(cursor.fetchall()[0][0].value)

When run on my system (Windows XP, SQL Server 2000, Python 2.2.2,
Win32all Build 150) this script prints:

'as\x00\x01\x02df'

Jimmy