Thread: Using BLOBs with several DBMS

Hi,


I'm trying to make a application in JAVA that connects thought JDBC to DBMS. 
It should create tables, insert data and make queries.

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.


Thanks

Ricardo Maia

> 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
youplan 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)
allowsyou to    share SQL statements / table schemas across DBMS, but it is     kind of hard getting the wrapper /
adapterAPIs right.
 

-- Steve

Thanks Stephen.

Maybe I'll follow your second sugestion ...

Do you kown any JDBC Drivers (even if comercial) for PostgreSQL and/or Oracle 
that implements fully the Blobs API?

Ricardo Maia

On Friday 08 June 2001 06:30, Stephen Crawley wrote:
> > 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
>
>
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
>     (send "unregister YourEmailAddressHere" to majordomo@postgresql.org)

-- 
----------------------
Critical Software, SA 
Urbaniza��o Quinta da Fonte
Lote 15, TZ, r/c H
3030 Coimbra
Telef.: 239 708 520
Telem.: 938 314 605
----------------------

> Do you kown any JDBC Drivers (even if comercial) for PostgreSQL and/or Oracle 
> that implements fully the Blobs API?

The latest Oracle JDBC drivers support the JDBC2 Blobs API, but I don't know
of any that support the API for PostgreSQL.

-- Steve