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

Any thoughts would be appreciated.

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 (+)"
else
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).

warning: opinion, not fact

the best approach is to have a solid logical data model

write your application logic based on a "black box" approach using logical entities and their attributes

e.g. "insert order and order items" black box
pass in: order info, order item info
results: status "success" or error message

the black box can be implemented with INCLUDEs or whatever modularization programming method you wish to implement

the included module takes care of the appropriate syntax (e.g. difference between an IDENTITY in sql server and a SEQUENCE in oracle)

then just write different modules for different databases, and distribute the application with the target database set of black boxes

thus, you can (and should) continue to use stored procs


your idea about common syntax is good in theory, except in practice, there is so very little SQL in common that you couldn't really do it that way

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

Appreciate your thoughts.

this part of your last quote worries me: "we don't have particular database skills here"

may i suggest that if your company is building an application that will run off a database, your company should really have database skills

Yeah, that'd be nice indeed!