Database Abstract Factory

[Mark Herschberg]

We want our product to support multiple databases, but there are some issues. For example, different database vendors tend to store dates differently. We would want to make our core code as database independent as possible. I'm thinking we need some type of database abstract factory. I realize JDBC does this to some extent, but from what I know, it seems to be only for driver-related issues.
Can someone point me to any solution, paper, article, web site, etc which addresses this issue. I'm sure it's a fairly common problem, and has been solved by people more knowledgeable than us.
Thank you.

--Mark
[e-mail]

[Thomas Paul]

Use the java.sql.Date object.

[Mark Herschberg]

Originally posted by Thomas Paul:
Use the java.sql.Date object.

java.sql.Date only goes down to seconds, we need miliseconds. Moreover, asking the database itself (as opposed to our program) for the time requires a different command for different vendors. Asking db's for sequences have a similar issue.
Any thoughts?

--Mark
[e-mail]

[Peter Tran]

Thomas,
This won't work for SQL*Server if you're using the Merant drivers. I'm not sure with other driver implementation, but Merant says SQL*Server doesn't support the java.sql.Date object, because there is not date type in SQL*Server. You would have to use java.sql.Timestamp. What a royal pain-in-the-ass! I thought JDBC would sheild me away from being platform dependent, but now I still have to maintain two code base. Once that uses java.sql.Date for ORACLE and java.sql.Timestamp for SQL*Server.
-Peter

[Thomas Paul]

Carved over the entrance to hell: "Abandon all hope ye who use Microsoft products."