Granny's Programming Pearls
"inside of every large program is a small program struggling to get out"
The moose likes JDBC and Relational Databases and the fly likes blob/clob management irrespective of DBMS vendor Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "blob/clob management irrespective of DBMS vendor" Watch "blob/clob management irrespective of DBMS vendor" New topic

blob/clob management irrespective of DBMS vendor

andrei radu

Joined: Dec 09, 2009
Posts: 2
hello all,

i am implementing the content management tier of a j2ee web application. i am currently researching the possibility of implementing it such that it would be possible to use several database vendors (changing only the jdbc driver and the data source parameters). currently i am testing on oracle/mySql.

for blobs and clobs, there seems to be a different approach for oracle than mySql, but i might very well be missing something. that is:
insert empty lob with the normal insert/update query.
select the lob field for update
write the data using the inputStream/writer in the returned class.

pass the inputStream/writer to the prepared statement as parameter for the blob/clob field.

has anyone researched this before ? any oppinions ?

thank you very much for your time,
Scott Selikoff
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3753

Good luck with that. It's really hard to create a JDBC program that works with any database. Have you considered a ORM tool?

[OCA 8 Book] [Blog]
andrei radu

Joined: Dec 09, 2009
Posts: 2
yeap, but orm is not an option because older versions of database items should be saved for auditing purposes which means that there is no update or delete and it uses a custom primary key generation. in my oppinion these cannot be implemented as they are using orm.

later edit: and about the way, the program is not supposed to cover all particular features of all DBMSs. so far insertion/selection work for oracle and mySql and should work on others too as it's plain sql. if i get the blob/clob reading and writing to work the only job left would be to test on other database types (and figure out the data type differences for creation of the tables).

thanks for your answer
I agree. Here's the link:
subject: blob/clob management irrespective of DBMS vendor
jQuery in Action, 3rd edition