Supporting multiple database'

Collapse
This topic is closed.
X
X
Collapse
 
  • Page of 1
  • Time
  • Show
Clear All
new posts
Previous template Next
  • TClancey
    #1

    Supporting multiple database'

    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.


    Tags: None
    • Cor Ligthert [MVP]
      #2
      Re: Supporting multiple database'

      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


      "TClancey" <tull@idcodewar e.co.ukschreef in bericht
      news:Q9edncCLgY nq-QPYnZ2dnUVZ8sSr nZ2d@bt.com...
      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.
      >

        Comment

        • Tom Shelton
          #3
          Re: Supporting multiple database'

          On 2007-01-05, TClancey <tull@idcodewar e.co.ukwrote:
          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.

          --
          Tom Shelton

            Comment

            • Chris Mullins [MVP]
              #4
              Re: Supporting multiple database'

              "TClancey" <tull@idcodewar e.co.ukwrote:

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

              --
              Chris Mullins, MCSD.NET, MCPD:Enterprise , MVP C#
              http://www.coversant.net/blogs/cmullins



                Comment

                Previous template Next
                Working...