I am writing a client application using ODBC. It currently uses PostgreSQL, but I might extend it to use some other DBMS in the future if needed. I need to store large binary objects (images) in the database and securely query them from my client application.
As I understand, in PostgreSQL I can take two approaches:
(1) Use BYTEA datatype and store large objects in table rows
(2) Use large objects facility where all objects are stored in pg_largeobject table and oid's are stored in all of my tables.
Case (1) is the easiest, I would be able to transfer the data to my application securely and could disallow other users from accessing these large objects. Also, my ODBC code would be (almost) portable to other DBMS's, by changing the BYTEA type to their corresponding BLOB type. I want to know what are the pros and cons of using this approach?
In case (2), all objects are stored in a global table which all users can access. This is a little insecure, but in my situation this might be admissible. Another problem with this is that my appliction code will rely on PostgreSQL specific functions and will cease to be portable. I'm fairly new to ODBC and SQL programming, and don't know if other databases also support this feature and if there is a method to implement this with ODBC, without relying on PostgreSQL specific methods. Is this possible, or would I have to explicitly test for PostgreSQL and implement this with BLOBs with other DMBS's from my code?
Does anyone know of another way how I can store large objects and query them securely from a database in a DBMS independent way? I've read a lot of posts and most of them advise on storing large objects in external files and storing the path in tables, however, then I would need to write a separate server to handout those images to users in a secure way, which is not what I want to do.
If portability is one of your main concerns, then go for the bytea solution. Something similar (BLOB datatype) is available for any decent DBMS whereas the large object solution is very PG specific and you will have a hard time porting that over to a different DBMS.
The biggest difference (as far as I understand the large objects) is, that with a large object it is very easy for you to read (and update) the data partially, which is not possible with bytea (you always read everything into memory)
As you mention you want to store images, I assume that you will always use the full blob data anyway. So you wouldn't make use of that advantage.
As for putting the images onto the filesystem: this is probably even more portable than using a bytea (BLOB) column. I don't know ODBC but with JDBC the different drivers tend to behave differently when it comes to reading and writing BLOBs.
BLOB vs. external file is very much a matter of taste in my opinion. Both solutions have the pros and cons.
Pro for BLOBs: you have all your data in one place which makes backup and restore easier, don't need to worry about the size of directories or fiilename restrictions across platforms), more sophisticated access security
Pro for external files: DB doesn't get bloated, not all DB query tools support blobs nicely (I think this is something important from the developer's perspective, because during testing you will need to check the DB frequently), when using a web application the images could be delivered directly by the webserver (if security is not that important)
shammat, thanks for your fast responce.
I see that my best choise is to use the BLOB type and store images in a database. It is unfortunate that SQL standard does not provide some "file" type, which would store links to file in database tables, but stream file content when asked. I would store the images in external files, but I need them accessed in a secure way (medical data), so this is not an option. If anyone would like to comment on their experience with storing large objects, I would very much appreciate your opinion.