Hi everybody !

The project is a program which uses intensively database but for more portability, it must work with several dbms types
What is the good way to proceed ?
Ex: the program works both SQL Server and Oracle
- To write separate code for Ora Db and MsSql Db
- To use OleDb Driver (Windows) to have one code for the both dbms (OleDb has many disadvantages and incompatibilities)
- Other solutions

All ideas are welcome

In fact, before coding the application, we had to migrate the first used db to the others, we have kept the same structures and the same datas.

In the new OOP paradigm you'd delegate the db interface to a class/object, and make a different subclass for each different db type.
that way round you could handle any db type, arguably you could also handle different versions of the same DB. although it will probably expand the maintenance requirements unless the whole project was OOP's designed

"Well-said" healdem !!

But it is alike recreate Microsoft ADO, that'll a spare time

I'd be inclined to try to keep the Oracle and SQL Server code separate, as stored procedures/packages. That way, you can "leverage" (horrible word, but what's the proper one?) the abilities of each DBMS to its maximum and not sully your application code with lots of conditional code to work around the quirks of each DBMS's SQL syntax.

id expect you to do that in any event (to take advantage of the servers stored procedures), but in the OOPS world you should also be abstracting any such db call into a separate sub class that is unique to the db, but with the same standard interface as its sibling db classes. so the main application code does what it needs to, and instantiates a class reflecting the sites db. the db abstraction layer (the class) should take into account any idiosyncracies between each db.. there should be no need to have lots of conditional code as the db is defined as part of the class, providing the correct class is initialised it should need no further conditional code. granted supporting a different class per db could become a maintenance problem, especially if you have separate classes by version of db.

theres is a heck of a lot to be said for the KISS principle.. ie service the top 2 or 3 db's in that field and leave the rest to hang...... (AKA to be supported in future releases)..

Yes, there would be different scripts for each dbms but we're talking about the software code (but not sql)
I understand the technic; so, we'll use Microsoft ADO or what ? Shall we load the appropriate drivers for the dbms, it will be very difficult !!!

You'll probably got through ADO to make the actual interaction with the db. Intrinsically ADO is a class wrapper to the generic db manipulations.

But, IF you are using OOPS then I'd expect to have 2 classes actually handling the calls to the DB, one for MS SQL server, one for Oracle, with an identical interface that the main class interacts with. having proven that interface then adding a new db module becomes relatively trivial.. as you have already proved the interface 'all' you need to is design, test and deploy the new db class.

in terms of deploying the application you could do a conditional compile which then only instantiates the relevant db class. after all its my guess it unlikely that a customer / user will want to run MS SQL Server if they are an Oracle house, and vice versa

Another opinion ??

Applications designed to work with multiple DB platforms invariably extract all business logic and most of the referential integrity into the application layer or middle tier.
So they invariably end up being slow, buggy, crappy applications with corrupted data.

Seconded!

thirded

where's the debate we were promised in the thread title?

Sorry, but I have to disagree with myself here. I obviously do not know what I am talking about. How long have I been working with databases, anyway?
I certainly would never hire somebody like me in my company.

This thread is the debate site !!
We discuss about how to implement an application which can work with several DBMS with the same structure and data.
Personally, I agree a bit with healdem's opinion but it's so hard and I wonder what about using Microsoft ADO (for Windows platform) because we don't need to change the code of the application just certain SQL queries but for Linux, I don't know