Sql for multiple vendor database server ...

[artois_uo]

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

[Jason hawryluk]

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

[ralph]

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