Thursday, May 27, 2010

Images in SQL Server

A simple .NET class to dump image data into/from SQL Server. I'd like to explore a potential alternative to this using FILESTREAM.

In T-SQL:

update version set [file] = BulkColumn from
openrowset(bulk 'e:\....jpg', single_blob) as [file]
where ...;





In Python/MySQL, this is done like this: (the image column, image_data, is defined as BLOB in MySQL)


>>> import MySQLdb
>>> connection = MySQLdb.connect('','root','','RTest')
>>> blob = open('d:\\pic1.jpg', 'rb').read()
>>> sql = 'INSERT INTO rtest.mm_image(image_data, mm_person_id_mm_person) VALUES(%s, 1)'
>>> args = (blob,)
>>> cursor = connection.cursor()
>>> cursor.execute(sql, args)
>>> connection.commit()



blob is a string type.