Image datatype and embedding pdfs in a Sybase SQL Anywhere 10 database

[Former Member]

We are wanting to store PDF files in our Sybase 10 database. Some of them are up to 50 megabytes in size. I looked it up online and saw that the Image datatype in a table appears to hold what we need.

How do you implement this feature? I am new to using BLOB's in databases and am unsure how to encode the pdf data into binary and store it in the database.

[chris_keating]

This depends on the connectivity technology that you plan to use. You can use the xp_read_file/xp_write_file system procedures as an option.

[MarkCulp]

You will want to use a LONG BINARY data type for the column to store the PDF content. Take a look at the documentation to read about some considerations when dealing with BLOBs: http://dcx.sybase.com/index.html#1201/en/dbadmin/design-s-3180714.html

What API do you plan to use to communicate with the database? You have many options: ODBC, ESQL, JDBC, OLEDB, and many more. See http://dcx.sybase.com/index.html#1201/en/dbprogramming/dbprogramming12.html

[thomas_duemesnil · Accepted solution]

IMHO I'm not a fan of using large blob files in server tables. You could think about a Directory Server where you can save you files in a directory of the server.

IF you use ASA 11 and above .....

To Transfer Files to the Server without additional Data Connections check out the Documentation about Access to data on client computers.

If you use ODBC to connect to the Server you can use it directly out of the box. Make sure that you statement includes the READ_CLIENT_FILE() function directly.

This Works

select READ_CLIENT_FILE('C:\\\\TEST.PDF') into BlobVar from dummy;

This will case an error !

select UDF_THAT_INCLUDES_A_READ_CLIENT_FILE_CALL('C:\\\\TEST.PDF') 
into BlobVar from dummy;

The ODBC Driver will do some magic on the executed Statement to get your data to the DB Server.

[VolkerBarth · Accepted solution]

Just to note: Apparently, storing the files outside the database (which surely has its benefits) means you have to backup these files in addition to the database backup.

[thomas_duemesnil · Accepted solution]

And these transactions are not transactional !