fredag, december 14, 2007

JDBC and DDL

It would really be time for JDBC to add support for database agnostic DDL. This is still one of the more gross areas of many database libraries (just look at dialects in Hibernate). Most of it is actually caused by DDL. But at the end of the day, most of the operations supported are actually exactly the same. Am I the only one who thinks it would be nice to have programmatic access to DDL operations?

3 kommentarer:

Daniel Spiewak sa...

It would be nice, but considering how poor a job JDBC does on the *normal* database operations, I'm not so sure I would trust it to handle something as complicated as DDL.

As far as my uses are concerned, JDBC is nothing more than a low-level transaction broker. All of the database-specific stuff (with the exception of schema interrogation) has to be handled by custom code.

Carfeld Yim sa...

You can take a look of DB Copy Plugin for the SQuirreL SQL Client. It help me to move Data and schema from Oracle to Hsql. Very likely they've spend some time about issue you've mentioned

http://dbcopyplugin.sourceforge.net/

squirreldev sa...

The DBCopy plugin leverages Hibernate's Dialect framework to translate column types from one db to another. However, the Refactoring plugin extends the dialects to provide much more DDL that is tailored for many different DBs. Current refactorings are Add/Modify/Drop columns and Add/Drop primary key. More are in the works. The best aspect of the Refactoring plugin in my opinion is it lets you preview and edit the DDL that it emits, prior to running it on your database. It would be nice if JDBC vendors had a JDBC spec for DDL to maintain this type of functionality themselves, but I suspect it would greatly increase the number of JDBC API methods and support classes. Also, if all of the database vendors get involved, there will probably be a consensus API that is lowest common denominator (I'm thinking EJB 3, versus what is available in Hibernate). So developers will still have to dip down to native SQL or vendor-specific JDBC extensions for certain advanced features. Still, it would be a start...

Rob