Message-Id: <200106080530.f585UO601918@piglet.dstc.edu.au>
To: Ricardo Maia <rjmaia@student.dei.uc.pt>
cc: pgsql-interfaces@postgresql.org, crawley@dstc.edu.au
Subject: Re: Using BLOBs with several DBMS 
In-Reply-To: Message from Ricardo Maia <rjmaia@student.dei.uc.pt>
	of "Tue, 29 May 2001 15:04:52 +0100." <01052915045203.13393@valkyrie>
Mime-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Date: Fri, 08 Jun 2001 15:30:26 +1000
From: Stephen Crawley <crawley@dstc.edu.au>


> My question is: how can i create a table with one BLOB field, in a DBMS 
> independent way?
>
> I am using PostgreSQL7.1 and Oracle 8 but I want to make it be able to work 
> with other DBMSs.

It is difficult, because:

  *  Different versions of SQL use different names for the BLOB type.

  *  Some DBMS don't support blobs at all.

  *  Some DBMS treat blobs differently to other SQL types and
     require you to use special APIs to access them.

  *  Not all JDBC drivers support the JDBC 2.0 Blob APIs.  Of those
     that do, many implement them in part only.

Given this mess, you've got two choices:

  *  Define a Java interface for all of your database interactions.  
     Create a different implementation for each DBMS you plan to support,
     complete with embedded SQL statements, table schemas, etc.

  *  Define wrapper classes for JDBC, and try to isolate the DBMS 
     differences in "adapters".  This (more or less) allows you to
     share SQL statements / table schemas across DBMS, but it is 
     kind of hard getting the wrapper / adapter APIs right.

-- Steve