Using pyodbc on ubuntu to insert image fields on SQL Server

Question

I am using Ubuntu 9.04

I installed the following versions of packages:

unixodbc and unixodbc-dev: 2.2.11-16build3 tdsodbc: 0.82-4 libsybdb5: 0.82-4 freetds-common and freetds-dev: 0.82-4 python2.6-dev 

I configured /etc/unixodbc.ini as follows:

 [FreeTDS] Description = TDS driver (Sybase/MS SQL) Driver = /usr/lib/odbc/libtdsodbc.so Setup = /usr/lib/odbc/libtdsS.so CPTimeout = CPReuse = UsageCount = 2 

I configured /etc/freetds/freetds.conf as follows:

 [global] tds version = 8.0 client charset = UTF-8 text size = 4294967295 

I took a revision of podbc 31e2fae4adbf1b2af1726e5668a3414cf46b454f from http://github.com/mkleehammer/pyodbc and installed it using " python setup.py install "

I have a Windows machine with Microsoft SQL Server 2000 installed on my local network, up and listening on the local IP address 10.32.42.69. I have an empty database created with the name "Common". I have a user "sa" with the password "secret" with full privileges.

I am using the following python code to configure the connection:

 import pyodbc odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS" con = pyodbc.connect(odbcstring) cur = con.cursor() cur.execute(""" IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testing') DROP TABLE testing """) cur.execute(''' CREATE TABLE testing ( id INTEGER NOT NULL IDENTITY(1,1), myimage IMAGE NULL, PRIMARY KEY (id) ) ''') con.commit() 

All WORKS to this point. I used SQLServer Enterprise Manager on the server and there is a new table there. Now I want to insert some data into the table.

 cur = con.cursor() # using web data for exact reproduction of the error by all. # I'm actually reading a local file in my real code. url = 'http://www.forestwander.com/wp-content/original/2009_02/west-virginia-mountains.jpg' data = urllib2.urlopen(url).read() sql = "INSERT INTO testing (myimage) VALUES (?)" 

Now, when I asked my initial question, I had problems using cur.execute(sql, (data,)) , but now I edited the question because after Vinay Sajip's answer below (THANKS) I changed it to:

 cur.execute(sql, (pyodbc.Binary(data),)) con.commit() 

And the insert works fine . I can confirm the size of the inserted data using the following test code:

 cur.execute('SELECT DATALENGTH(myimage) FROM testing WHERE id = 1') data_inside = cur.fetchone()[0] assert data_inside == len(data) 

What goes great !!!

Now the problem is retrieving the data back.

I am trying to use a general approach:

 cur.execute('SELECT myimage FROM testing WHERE id = 1') result = cur.fetchone() returned_data = str(result[0]) # transforming buffer object print 'Original: %d; Returned: %d' % (len(data), len(returned_data)) assert data == returned_data 

However, this will not work !!

 Original: 4744611; Returned: 4096 Traceback (most recent call last): File "/home/nosklo/devel/teste_mssql_pyodbc_unicode.py", line 53, in <module> assert data == returned_data AssertionError 

I put all the code above in a single file here , for easy testing of anyone who wants to help.

Now to the question:

I want python code to insert an image file in mssql. I want to request the image back and show it to the user.

I don't need the column type in mssql. I use the " IMAGE " column type in the example, but any type of binary / block will do as long as I get the binary data for the file that I inserted back into the untouched. Vinay Sajip said below that this is the preferred data type for this in SQL SERVER 2000.

Now data is inserted without errors, but only 4k is returned when receiving data. (Data is truncated at 4096).

How can I do this job?

---

EDIT : Vinay Sajip's answer below gave me a hint to use pyodbc.Binary on the field. I updated this question accordingly. Thanks Vinay Sajip!

Commentary by Alex Martelli gave me the idea of ​​using the DATALENGTH MS SQL function to check if the data in the column is fully loaded. Thanks Alex Martelli!

Answer 1

Yes, after I offered generosity, I found a solution.

You must use SET TEXTSIZE 2147483647 in the query, in addition to the text size configuration option in /etc/freetds/freetds.conf .

I used

 cur.execute('SET TEXTSIZE 2147483647 SELECT myimage FROM testing WHERE id = 1') 

And it worked fine.

The strange thing is that the FreeTDS Documentation says about the text size configuration parameter:

The default value is TEXTSIZE , in bytes. For text and image types, datatypes sets the maximum width of any returned column. Cp set TEXTSIZE in the T-SQL documentation for your server.

The configuration also indicates that the maximum value (and the default value) is 4,294,967,295. However, when I try to use this value in the request, I get an error message, the maximum number that I could use in the request is 2,147,483,647 (half).

From this explanation, I thought it was enough to install this configuration option. It turns out that I was mistaken, installing TEXTSIZE in the request, fixed the problem.

The following is the full working code:

 #!/usr/bin/env python # -*- coding: utf-8 -*- import pyodbc import urllib2 odbcstring = "SERVER=10.32.42.69;UID=sa;PWD=secret;DATABASE=Common;DRIVER=FreeTDS" con = pyodbc.connect(odbcstring) cur = con.cursor() cur.execute(""" IF EXISTS(SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'testing') DROP TABLE testing """) cur.execute(''' CREATE TABLE testing ( id INTEGER NOT NULL IDENTITY(1,1), myimage IMAGE NULL, PRIMARY KEY (id) ) ''') con.commit() cur = con.cursor() url = 'http://www.forestwander.com/wp-content/original/2009_02/west-virginia-mountains.jpg' data = urllib2.urlopen(url).read() sql = "INSERT INTO testing (myimage) VALUES (?)" cur.execute(sql, (pyodbc.Binary(data),)) con.commit() cur.execute('SELECT DATALENGTH(myimage) FROM testing WHERE id = 1') data_inside = cur.fetchone()[0] assert data_inside == len(data) cur.execute('SET TEXTSIZE 2147483647 SELECT myimage FROM testing WHERE id = 1') result = cur.fetchone() returned_data = str(result[0]) print 'Original: %d; Returned; %d' % (len(data), len(returned_data)) assert data == returned_data 

Answer 2

I think you should use an instance of pyodbc.Binary to transfer data:

 cur.execute('INSERT INTO testing (myimage) VALUES (?)', (pyodbc.Binary(data),)) 

The extraction should be

 cur.execute('SELECT myimage FROM testing') print "image bytes: %r" % str(cur.fetchall()[0][0]) 

UPDATE: The problem is the insert. Modify your SQL insert query as follows:

 """DECLARE @txtptr varbinary(16) INSERT INTO testing (myimage) VALUES ('') SELECT @txtptr = TEXTPTR(myimage) FROM testing WRITETEXT testing.myimage @txtptr ? """ 

I also updated the error I made using the value attribute in the search code.

With this change, I can insert and extract a 320K JPEG image into the database (the received data is identical to the inserted data).

NB The image data type is deprecated and is being replaced by varbinary(max) in later versions of SQL Server. However, for a newer column type, the same logic for insert / extract should be applied.

Answer 3

I had a similar 4096 crop problem in TEXT fields, for which SET TEXTSIZE 2147483647 fixed for me, but it also fixed this for me:

 import os os.environ['TDSVER'] = '8.0'