psqlODBC HOWTO - Access Large Objects

Author: Unknown
Release Date: Unknown
Description: Using large objects in Microsoft Access (notes from the original psqlODBC docs)

Using Large Objects for handling LongVarBinary (OLE Objects in Access)

Large objects are mapped to LONGVARBINARY in the driver to allow storing things like OLE objects in Microsoft Access. Multiple SQLPutData and SQLGetData calls are usually used to send and retrieve these objects. The driver creates a new large object and simply inserts its 'identifier' into the respective table. However, since PostgreSQL uses an 'Oid' to identify a Large Object, it is necessary to create a new PostgreSQL type to be able to discriminate between an ordinary Oid and a Large Object Oid. Until this new type becomes an official part of PostgreSQL, it must be added into the desired database and looked up for each connection. The type used in the driver is simply called "lo" and here is the command used to create it:

create type lo (
    internallength=4,
    externallength=10,
    input=int4in,
    output=int4out,
    default='',
    passedbyvalue
);

Once this is done, simply use the new 'lo' type to define columns in that database. Note that this must be done for each database you want to use large objects in with the driver. When the driver sees an 'lo' type, it will handle it as LONGVARBINARY.

Another important note is that this new type is lacking in functionality. It will not cleanup after itself on updates and deletes, thus leaving orphans around and using up extra disk space. And currently, PostgreSQL does not support the vacuuming of large objects. Hopefully in the future, a real large object data type will be available.

But for now, it sure is fun to stick a Word document, Visio document, or avi of a dancing baby into a database column, even if you will fill up your server's hard disk after a while!