Sql for multiple vendor database server ...


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 3 of 3

Thread: Sql for multiple vendor database server ...

  1. #1
    artois_uo Guest

    Sql for multiple vendor database server ...



    Your new project manager (pm) want to have our whole product line supporting
    all the database available on the market. So the client can chose whatever
    he want or already use. He insist using odbc and brague about sql 92 standard...

    I admit some sql syntax is similar like the select but no real standard exists
    because they all implement their own syntax and proprietary feature. (Not
    to mention pl/sql vs transact-sql ;-) )

    I worked with Oralce, sql server and MySql and all the product we developed
    was for only one Db and we used all the feature (sp,trigger, ...) we needed
    without caring about other DB compatibility. But since your new pm got this
    ideas to use portable sql, how can we achieve this or is it possible ? Personally
    i disagree with him. But since he is the boss...

    If someone can lead me to an article, web site or have any suggestion on
    how to implement portable sql, i will appreciate it. Or if someone have all
    the reason to discard this ideas, it`s also welcome (like date format nightmare,
    sp language...) .






  2. #2
    Jason hawryluk Guest

    Re: Sql for multiple vendor database server ...

    I think that is what a data layer is for. We have one single data layer dll
    for each dbtype. This primary function of the data layer is to create
    executable sql statements for the given db type. Passed a db object Table
    name and useing a database catalog comprised of serveral tables. We generate
    all sql stements automaticly. We try to avoid Storedprocs whenever possible
    Although these too can be automaticly created and/or updated depending on db
    structure changes. If we want to add new support for a db we change this dll
    where needed and recompile. This dll has serveral objects the main objects
    are...

    cExecute 'executes commands updates,inserts etc..
    cProvider 'allows user movement,add new etc.. for bound updates
    cGenSQL 'does just that
    cConnection 'handles connection pooling and creation
    and many others...

    In short if you have not prepared ahead of time for other db types, you
    could quickly find your self in a backend rewrite of your system.

    We use oledb and ado, Ado and oledb handle alot of the translation, leaving
    us things like oracle and sql server joins, Blobs -> clobs. The catalog we
    created is updated automaticly and provides field , data types, joined
    tables, relations etc... giveing the data layer all info needed to do it's
    job.

    Sorry but i agree with the (pm) Support for multiple dbs is a good business
    decision especialy on large systems. Evey company has his or her
    prefferances. We found that the clients are often willing to absorb some of
    the cost to translate the datalayer to thier prefferance if not yet
    supported.

    We do not use much odbc except for import/export functions and i am not sure
    what sql 92 standard has to do with anything...

    I do not know of any articles that disscuse this topic.

    My 2cents hope it helps

    Jason...


    "artois_uo" <artois_uo@hotmail.com> a écrit dans le message news:
    3ddbd204$1@tnews.web.devx.com...
    >
    >
    > Your new project manager (pm) want to have our whole product line

    supporting
    > all the database available on the market. So the client can chose

    whatever
    > he want or already use. He insist using odbc and brague about sql 92

    standard...
    >
    > I admit some sql syntax is similar like the select but no real standard

    exists
    > because they all implement their own syntax and proprietary feature. (Not
    > to mention pl/sql vs transact-sql ;-) )
    >
    > I worked with Oralce, sql server and MySql and all the product we

    developed
    > was for only one Db and we used all the feature (sp,trigger, ...) we

    needed
    > without caring about other DB compatibility. But since your new pm got

    this
    > ideas to use portable sql, how can we achieve this or is it possible ?

    Personally
    > i disagree with him. But since he is the boss...
    >
    > If someone can lead me to an article, web site or have any suggestion on
    > how to implement portable sql, i will appreciate it. Or if someone have

    all
    > the reason to discard this ideas, it`s also welcome (like date format

    nightmare,
    > sp language...) .
    >
    >
    >
    >
    >




  3. #3
    ralph Guest

    Re: Sql for multiple vendor database server ...


    "artois_uo" <artois_uo@hotmail.com> wrote:
    >
    >
    >Your new project manager (pm) want to have our whole product line supporting
    > all the database available on the market. So the client can chose whatever
    >he want or already use. He insist using odbc and brague about sql 92 standard...
    >
    >I admit some sql syntax is similar like the select but no real standard

    exists
    >because they all implement their own syntax and proprietary feature. (Not
    >to mention pl/sql vs transact-sql ;-) )
    >
    >I worked with Oralce, sql server and MySql and all the product we developed
    >was for only one Db and we used all the feature (sp,trigger, ...) we needed
    >without caring about other DB compatibility. But since your new pm got this
    >ideas to use portable sql, how can we achieve this or is it possible ?

    Personally
    >i disagree with him. But since he is the boss...
    >
    >If someone can lead me to an article, web site or have any suggestion on
    >how to implement portable sql, i will appreciate it. Or if someone have

    all
    >the reason to discard this ideas, it`s also welcome (like date format nightmare,
    >sp language...) .
    >


    I have to agree with you. For 20 years now we have been promised "portable
    sequel" and I have yet to see it yet. Things have gotten better, but there
    are always enough glitches and gotcha's to make a managled mess of things
    in short order (date formats, DISTINCT, ...).

    On top of all this are the problems with how the databases themselves invoke
    various schema's - triggers, sequencing/autonum, storage procedures/packages,
    etc. ... (A serious program that doesn't use "stored procedures/pre-compiled
    scripts" is ..., well not a serious program.)

    What I have been doing (always working with clients which had a minimum of
    3 (Access, SQLServer, Oracle, usually) is to create a robust group of "middle-layer"
    components and then abuse polymorphism to the limit. <g>

    Dim mAccM as IADOMgr
    Dim mOraM as IADOMgr
    Set mAccM = New AccessMgr
    Set mOraM = New OracleMgr
    ...



Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center