PDF document in a field in SQLServer image

[jlmunoz]

Messrs.

How I can store a PDF document in a field in SQLServer image, and then being able to show in a form.

But if we have achieved with documents in BMP, JPG, GIF.

thanks

[Jeff Luther]

How I can store a PDF document in a field in SQLServer image, and then being able to show in a form.

Your question is actually several parts, though I am not sure what you mean by a PDF doc in a 'field'. The PDF is displayed in a data field?

Anyway, assuming the PDF is also a file on disk:
* DB storage: storage would be alike you do for any binary file (bmp, etc.) You'd need to have a MS SS column that supported binary data, SalFileOpen/Read to read in the file, use TD's SqlSetLongBindDatatype() to define the bind variables as 23 = binary, and INSERT it into the DB

* Fetch: SqlSetLongBindDatatype() to define the into variables as 23 = binary, SELECT it, SalFile functions to save it to disk, then call SalLoadApp() with the PDF name and Windows should open Adobe Acrobat Reader to display the file.

then being able to show in a form

Displaying in a form is different and there's been a TD sample of this floating around for a long time. I just made a v5.2 version of it and have attached the zip which includes a sample PDF file. That should show you how to display a PDF on a form.

[jlmunoz]

Thanks for your input and we are showing the form in a PDF document.

Our problem is how to insert the document in a PDF file within the BD SQLServer 2008. We are using column type "image" for the rest of the documents BMP, JPG, etc.

For the PDF we can use this same type of column?

You have some example to insert PDF in SQLServer?

thanks

[Jeff Luther]

JL -- I'll reply to you but I also suggest you write a small test application, use a temp DB table for testing and a sample PDF file and use this to help you understand the issues.

For the PDF we can use this same type of column?

Image -- a search of the 'net: sql server image
yields that image is going to be deprecated, but I'm sure works now. See this page: http://stackoverflow.com/questions/4440 ... inary-data

You have some example to insert PDF in SQLServer?

No, but you certainly could write your own using TD, SalFile functions, etc.

Here is how it works here on the forum: You post an issue like this, try and write a small test case to understand how to insert and fetch a PDF, etc. and if you have trouble getting it to work you can attach your test case. That should have a CREATE TABLE statement for a temp. DB table, test TD application + sample PDF file and code to manage it with the DB.

We are using column type "image" for the rest of the documents BMP, JPG, etc.

Well, if you mean a separate column for PDF files that is of type 'image' that should work. If you have only one 'image' column, on the other hand, and are putting BMP, JPG etc. + PDF now, then I might suggest your DB table look something like:
ID
filename
image
file_type<maybe a constant value that indicates the type of file?> Like 1 = BMP, 2 = JPG, 3 = PDF, etc.

That way your code can fetch everything, know what filename to save the image value as and if necessary know what to do with the image contents.

[jlmunoz]

gentlemen

Thank you, your example was very useful to display a PDF in the application of a directory on the PC.

And now we are storing in DB SQLServer2008 with data type varbinary (MAX) for BMP, JPG and GIF correctly.

We experiment with the PDF, to store in DB SQLServer2008. You currently have managed to do? and how?

thanks

[jlmunoz]

Gentlemen
To complement what we have done.

With the images (BMP, JPG, GIF) works well, use the following instructions:
1) Insert row in the table:
set w_IDtraba = 7682483
set w_typeimg = 'JPG'
Call SqlPrepare( hSqlOtros, 'INSERT INTO RETRADOC (IDtraba, Typeimg) VALUES (:w_IDtraba , :w_typeimg )')

2) update table row with data type varbinary (MAX):
Call SqlConnect( hSqlTextoWrite )
Call SalPicGetString( picPhoto, PIC_FormatObject, s2photo )
Call SqlPrepare( hSqlTextoWrite, 'UPDATE RETRADOC set idocto = :s2photo WHERE IDtraba = :w_IDtraba and Typeimg = :w_typeimg' )
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute( hSqlTextoWrite )
Call SqlDisconnect( hSqlTextoWrite )

For PDF do the same, but I tried and SalPicGetBinary SalPicGetString but always gives me 1.
1) Insert row in the table:
set w_IDtraba = 7682483
set w_typeimg = 'PDF'
Call SqlPrepare( hSqlOtros, 'INSERT INTO RETRADOC (IDtraba, Typeimg) VALUES (:w_IDtraba , :w_typeimg )')

2) update table row with data type varbinary (MAX):
Call SqlConnect( hSqlTextoWrite )
! Call SalPicGetBinary( oPDFDocument, PIC_FormatObject, nBinary )
Call SalPicGetString( oPDFDocument, PIC_FormatObject, nBinary )
Call SqlPrepare( hSqlTextoWrite, 'UPDATE RETRADOC set idocto = :nBinary WHERE WHERE IDtraba = :w_IDtraba and Typeimg = :w_typeimg' )
Call SqlSetLongBindDatatype( 1, 23 )
Call SqlExecute( hSqlTextoWrite )
Call SqlDisconnect( hSqlTextoWrite )

[jlmunoz]

Lords.

I have managed to store a PDF document in the database SQLServer2008 with native SQL statements, but with TD 5.2 failed to operate, gives the message SQLWindows - SQL Error: 20035 "Microsoft SQL Server: 102 [Microsoft] [ODBC SQL Server Driver] [SQL Server] Incorrect syntax near '@ P1'. Microsoft SQL "

These instructions use native SQL where it works:
1) insert a row with identification data:
INSERT INTO RETRADOC (IDtraba, Typeimg ) VALUES(7682483, 'PDF' )

2) I update to the PDF:
UPDATE RETRADOC SET idocto = (SELECT BulkColumn FROM OPENROWSET (BULK 'C:\Certifcado.pdf', SINGLE_BLOB) idocto) where IDtraba = 7682483 and Typeimg = 'PDF'

Where the "idocto" is data type varbinary (MAX) in Table RETRADOC.