I'm looking for suggestions as to how best to build an app that must support more than one type of database.
Up to now I have always been an advocate of using stored procedures, etc. to access and manipulate data at that level. The benefits of this approach are many ranging from getting the best performance out of the database to the ease of maintenance and tuning, etc. This has worked well in the past because, mostly, I am supporting just one database type at a time.
Now I have picked up an app that is a mess with lots and lots and lots of sql embedded into the ui. This app must support both Oracle and SQLServer and, perhaps, others as more clients are won. I, of course, have suggested a more structured approach but have actually been pondering (secretly) whether writing separate stored procedures for each database type is, in fact, the best approach. For example, doing a 'select...from...etc.' through a oledb driver works no matter what database is used whereas using a stored procedure approach means more code to maintain and, probably most importantly, to keep in sync.
Even SELECT statement syntax can vary between DBMSs, so you would have to find the lowest common denominator there too, as well as avoiding traps like "WHERE name = ''" which will work in SQL Server but not in Oracle.
Stored procedures are an excellent approach because:
1) You can write the best SQL for each DBMS, making use of the best proprietary features e.g. analytic functions.
2) You can tune problematic queries for one DBMS without impacting the other. For example, maybe NOT EXISTS is more efficient than NOT IN for a particular query in one DBMS, but NOT IN is better in the other. Or maybe for SQL Server a complex query requires use of temporary tables, where in Oracle you would not.
3) The "interface" between your UI and the database is simpler: procedure calls with parameters rathern than long, unwieldy text strings containing SQL statements.
4) The code is separated into UI code, Oracle code, SQL Server code, ... This means you can divide work between people with different skillsets, rather than requiring every developer to be expert in all areas.
I have had experience of the other way, unfortunately: a client had an ASP application that used SQL Server only without stored procedures, and wanted to add support for Oracle as an alternative. Their budget would not run to rebuilding with stored procedures. Their SQL used the ANSI join syntax, which is supported by Oracle 9i but not by older versions. Since they wanted to be able to support Oracle 8i, all queries had to be re-written without the ANSI join syntax. When there were outer joins in the query, we needed 2 versions:
if db = "oracle" then
sql = ".... where a.x = b.x (+)"
sql = ".... left join b on a.x = b.x"
There were many other quirks to consider, which I have mostly forgotten. One was that Oracle didn't like the keyword AS before a table alias (select * from emp as e).
Thanks for those replies. I really do agree with your approaches and, like I say, I have been an advocate myself. Its just that some doubts have sneaked in because we don't have particular database skills here so my attempts to show how better the app could be written has just confused people - because now they have to maintain the SQL code in 2 places for even the simplest 'select'.