Supporting multiple database'

[TClancey]

Hi all.

I have an app that needs to connect to either - an Access database, MySql
database or MsSql database. It will be the end users decision which they
want to use.

I've done a lot of work with Access in the past, and a good deal with MySql
and realise that there are syntax issues between these two back ends. I
haven't done a great deal with MsSql yet, but I would imagine that syntax is
going to be an issue here as well.

I really want to avoid writing,
If ConnType = "Access" then DoSql("update ....")
If ConnType = "MySql" then DoSql("update ....")
If ConnType = "MsSql" then DoSql("update ....")
for every single Sql statement in the application, but can't think of an
easy way to parse the commands and check syntax.

Has anyone got any ideas?
Cheers,
Tull.

 

[Cor Ligthert [MVP]]

TCancey,

For this is the 2005 factory, however I have the idea that it is far from
ready.
http://www.vb-tips.com/dbpages.aspx?ID=8c3dc2d7-1232-4dd1-817e-22eaaebb2723

Cor

 

[Tom Shelton]

Hi all.

I have an app that needs to connect to either - an Access database, MySql
database or MsSql database. It will be the end users decision which they
want to use.

I've done a lot of work with Access in the past, and a good deal with MySql
and realise that there are syntax issues between these two back ends. I
haven't done a great deal with MsSql yet, but I would imagine that syntax is
going to be an issue here as well.

I really want to avoid writing,
If ConnType = "Access" then DoSql("update ....")
If ConnType = "MySql" then DoSql("update ....")
If ConnType = "MsSql" then DoSql("update ....")
for every single Sql statement in the application, but can't think of an
easy way to parse the commands and check syntax.

Has anyone got any ideas?
Cheers,
Tull.

You might want to look into an ORM tool, such as nhibernate, wilson or mapper,
llblgen, etc. I am mostly familiar with nhibernate, so I will comment on it -
but it allows you to support multiple databases without having to be overly
worried about the sql syntax of the backend datastore. It supports all of
those options, so it is simply a config file change to move to another db (as
long as you avoid db specific features).

Anyway, you might want to check it out and see if it will work for you.

 

[Chris Mullins [MVP]]

[Connecting to multiple databases]

I really want to avoid writing,
If ConnType = "Access" then DoSql("update ....")
If ConnType = "MySql" then DoSql("update ....")
If ConnType = "MsSql" then DoSql("update ....")

Well, that's really the best option we were able to come up with. We use all
stored procedures, and have ported each sproc to:
SQL Server 2000 / 2005 / MSDE / SQL Exresss (all the same file, at least)
Oracle 10g
MySQL
Postgres

We than have a datalayer (DLL) for each one that wraps the stored
procedures. Using some standard Interface tricks, we can minimize the code,
but it's still time consuming.