Insert a pdf into a sql table's image field

Question

I have a python script where I need to insert a pdf into an SQL table in an image field. I can add just about anything into this field - a number, text, a hex version of the pdf, but I cannot add the pdf using bytes or bytearray. The document_image field is defined as an image. I have veried the sql statement itself is correct - if I use null for the document_image.

In the following code, I have tried the hex_report (which will insert, but isn't readable by adobe), array_report and bin_report - these two giving the same errors.

report_map = "C:\\simple_test_pdf.pdf"
initial_report = open(report_map, 'rb').read()

hex_report =  binascii.b2a_hex(initial_report)
array_report = bytearray(initial_report)
bin_report = bytes(initial_report)

db = abc_sql()
db.Set_database("image")
conn   = db.Open_Connection()   

conn.execute_scalar("DECLARE   @return_value int, \
@new_serial_id int, \
@return_msg varchar(4000) \
EXEC    @return_value = image.image_insert \
@new_serial_id = @new_serial_id  OUTPUT, \
@return_msg = @return_msg OUTPUT,  \
@pd_request_id = 776, \
@mime_type = 'application/pdf', \
@document_file_name = 'Report_Results.pdf', \
@document_description = 'Report and Map', \
@document_image = %s \
SELECT @new_serial_id as '@new_serial_id',\
@return_msg as '@return_msg', \
'Return Value' = @return_value", str(bin_report ))

The error I'm getting is:

mssql.MssqlDatabaseException: SQL Server message 105, severity 15, state 1, line 1: Unclosed quotation mark after the character string '%PDF-1.5 %âãÏÓ 10 0 obj <> endobj 15 0 obj

... Incorrect syntax near '%PDF-1.5 ... (truncated)

Is there any reason for using IMAGE over FILESTREAM? See http://stackoverflow.com/questions/4450432/sql-server-how-to-store-binary-data-e-g-word-file — twoleggedhorse, Dec 17 '12 at 22:21

Robert-2012 · Answer 1 · 2012-12-17T20:45:10.743

0

Can you show table structure, so we can start from there?

I would use for that purposes varbinary(MAX). It will give you some flexability. Also you can find some help on MSDN

edited Dec 17 '12 at 20:45

answered Dec 17 '12 at 20:36

Robert-2012

1
1

The relevant field is defined as document_image(image,null) changing the field type is not an option, as it is used by many other people. – user1327390 Dec 17 '12 at 20:53
I wonder if it has something to do with the returns. The hex format is one continuous bunch of numbers. The byte and the array have lots of special symbols and carraige returns (tho not visible). – user1327390 Dec 17 '12 at 21:04

score 0 · Answer 2 · edited May 22 '14 at 14:16

You can do something like this,

INSERT INTO Table(ID, Blob) 
SELECT 0, * FROM OPENROWSET(BULK N'C:\YourPathFolder\File.pdf', SINGLE_BLOB) rs

If you want to parametrize that TSQL statement you can do it like this:

declare @path varchar(5000)
declare @SQL varchar(5000)
set @path = 'c:\YourPath\Yourfile.pdf'
set @SQL = 'INSERT INTO Table(ID, Blob) 
SELECT 0, * FROM OPENROWSET(BULK N''' +@CertPath +''', SINGLE_BLOB) rs'
execute (@SQL)

Having said all that, I don't recommend using an image field - it will be deprecated soon. Use varBinary(max) instead, as MS recommends.

Insert a pdf into a sql table's image field

2 Answers2