Brian,
I think you would be best served by creating separate maps for each database system. I went from Oracle to SQL Server and found it hard to impossible to make the sql identical. Using separate maps also gives you the ability to tune sql statements for the targeted RDBMS. Hopefully you can write DB specific Type Handlers to handle all your data conversion issues you might have so your data access code is identical.
Regards, Mike Fagan
Barnett, Brian W. wrote:
We have a web app that runs against SQL Server. All of our SQL maps are SQL Server compliant. We now have to be able to support Oracle as well. (We never thought it would happen... a mistake.)
Anyway, we are wondering if anyone has some general guidelines for writing SQL Maps so that they run against both databases. Before you get scared and close this email, let me say we're not looking for a list of differences between the two databases, and how to resolve those differences. We already have a doc that explains those things.
The first issue we have run into is the CONVERT and CAST functions of SQL Server. We make use of them in our SQL maps. We are debating on whether we should take them out and do the conversion or casting in java code or pass a parameter to the SQL map indicating SQL Server or Oracle and then have a <dynamic> element that generates the appropriate CONVERT or CAST syntax.
All input is welcome.
Thank you, Brian Barnett
