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...

Acrobat replacement

Over the years, my hate affair with Acrobat Reader has grown.
1. The UI seemed to block until the entire document was downloaded
2. The slow startup time, complimented by their oh-so-impressive patent list
3. The ever present, marketing driven, "Update Reminder"
4. The downright goofy interface.

It got so bad, I'd skip PDF hits in search results - that's insane. I was SO happy to stumble across
Foxit Reader. For my purposes at least, it's a fully functional drop-in replacement for Acrobat. I haven't had a single problem with it, and it's downright snappy. And it's free.