Thursday, January 11, 2007

Portable SQL?

The app I work on is going on 7 years old. We're slowly migrating to Hibernate, but there's plenty of "old fashioned" JDBC assets left. So, I maintain DDL for about 80 tables and 40 views. We've always tried to keep it portable. So far, the same DDL has run on Oracle, MySQL, Postgres, Derby, and Mckoi. You can imagine my shock when this didn't hold up when I tried SQL Server 2000. SQL Server has no DATE type, and has a bizarre (and deprecated) TIMESTAMP type which really has nothing do w/ storing traditional timestamps. They want you to use the "DATETIME" type for both.

I desperately want to a single set of portable, "pure" DDL for all of these databases, but I may be out of luck. I was able to fix the lack of DATE type by adding an "alias type":

CallableStatement cs = getConnection().prepareCall("{call sp_addtype(?, ?, ?)}");
cs.setString(1, "DATE");
cs.setString(2, "DATETIME");
cs.setString(3, "NOT NULL");
cs.executeUpdate();

But I can't do the same for TIMESTAMP, since it's a reserved word. There must be something clever that can be done...

2 comments:

Anonymous said...

Umm...

sqlserver.properties:
timestamp-type=DATETIME

postgres.properties:
timestamp-type=TIMESTAMP

Whala. There are a million ways to solve this by avoiding magic numbers/strings in your code.

Horse Admin said...

The key was I wanted "pure" ddl - no string replacement allowed.