Results 1 to 14 of 14
  1. #1
    Join Date
    Feb 2008
    Posts
    26

    Arrow [Debate]Develop an application which can work with different DBMS

    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

  2. #2
    Join Date
    Feb 2008
    Posts
    26
    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.

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Feb 2008
    Posts
    26
    "Well-said" healdem !!

    make a different subclass for each different db type.
    that way round you could handle any db type,
    But it is alike recreate Microsoft ADO, that'll a spare time

  5. #5
    Join Date
    Sep 2002
    Location
    UK
    Posts
    5,171
    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.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Quote Originally Posted by andrewst
    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)..
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Feb 2008
    Posts
    26
    Quote Originally Posted by andrewst
    try to keep the Oracle and SQL Server code separate, as stored procedures/packages
    Yes, there would be different scripts for each dbms but we're talking about the software code (but not sql)
    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
    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 !!!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    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
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Feb 2008
    Posts
    26
    Another opinion ??

  10. #10
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  11. #11
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Quote Originally Posted by blindman
    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!
    George
    Home | Blog

  12. #12
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    thirded

    where's the debate we were promised in the thread title?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  13. #13
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    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.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

  14. #14
    Join Date
    Feb 2008
    Posts
    26
    Quote Originally Posted by r937
    where's the debate we were promised in the thread title?
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •