The Design of Software (CLOSED)

A public forum for discussing the design of software, from the user interface to the code architecture. Now closed.

The "Design of Software" discussion group has been merged with the main Joel on Software discussion group.

The archives will remain online indefinitely.

Database abstraction layer

I must be doing this the hard way. 

I have a Windows (C++) app that can use Access, MS SQL or MySQL as the backend.  I used ADO as my connection/abstraction layer.  However, I find that I have all kinds of if(mySQL), if(access), if(MSSQL) throughout the code because many little chunks of SQL have to be massaged differently. 

This sucks!  Am I missing something obvious???  There has to be a better way...
Doug Send private email
Thursday, July 20, 2006

Why are you connecting to 3 different db's?

Create stored procs that map to the DAL layer.

Shoot me an email if you need more help.
D in PHX Send private email
Thursday, July 20, 2006
I didn' think MySQL supported stored procs (I haven't looked at it in about 2 years, so maybe that has changed).

I'm also not too strong in DDL, so creating the stored procs in an empty database would take some work, but maybe that is the magic bullet I'm looking for. 

Unforunately, I was thinking of adding support for SQLite, and I KNOW it doesn't support stored procs.
Doug Send private email
Thursday, July 20, 2006
I haven't tried it but if you were using .NET then might be relevent (I don't know how far it goes at masking provider-specific differences).

Otherwise, there's a paragraph somewhere which says,

  "When you are writing code to be used across .NET Framework data providers, pay special attention to any syntax you are using that is provider-specific. For example, SQL syntax will differ depending on the data source you are accessing. If you change to a different .NET Framework data provider, your code will be easier to maintain if you isolate your SQL commands as string constants."
Christopher Wells Send private email
Thursday, July 20, 2006

Not sure about SP's in MySQL.

As for trying to script all the SP's in SQL server, you can do that thru the tools, so if you can create it you should be able to manage it that way.

Depending on how you are using the databases, there may or may not be a way to generalize your code to work in multiple db's.


Even if the generalized code would work, depending on the usage and load of the db's, your application may benefit from you having seperate, specific code bases depending on the database platform.

Although there is such a thing as ANSI SQL, there are many things that the db's can do that are outside of ANSI SQL.
D in PHX Send private email
Friday, July 21, 2006
I think you need to separate your DAL from the rest of your business logic.  You need some sort of interface there so that your business logic can talk to the DAL layer of each type of database.  I am assuming that you only need to talk to one type of database at a time.  I am thinking of something similar to the adapter pattern.  I am pretty sure that you don't want to interweve the different implementations of your DAL (such as you were suggesting with IFs or Switch statements).  I can only imagine how hard that would be to test and debug.
Joshua Volz Send private email
Friday, July 21, 2006
The way that I do it is to wrap all SQL generation behind classes; that way there isn't any SQL text in the code at all.  There are specific versions of each SQL class (with common base code) for each database.  That code takes care of the differences without any need for additional logic spread throughout the codebase.
Almost H. Anonymous Send private email
Friday, July 21, 2006
My method is to have a separate Data Access Object for each database engine containing the code which is specific to that particular DBMS. This means I have a separate class for MySQL, PostgreSQL and Oracle. When a business object wants to communicate with the database it sends basic instructions to the DAO, and the DAO does whatever is necessary for that particular DBMS.

The whole point of this is that the business object does not know, nor need to know, anything about the DBMS internals. Only the DAO has that knowledge. This also means that should I need to connect to another DBMS all I have to do is create a new class file, then change the variable which identifies which class file to use when instantiating the DAO. This gives me the ability to switch from one DBMS to another simply by changing a single variable and not masses of code.

Some people may have a separate DAO for each table, but the are morons. A single DAO for each DBMS is all that is necessary.
Tony Marston Send private email
Friday, July 21, 2006
This is more like a general problem: you have a number of different implementations, and you need an abstraction.

It should be possible for you to build a database-neutral interface that you would use in your application and to have another, database-dependent layer where you provide concrete implementations based on that interface. (Then you plug an implementation at runtime.)

The Bridge (or something like that) design pattern describes it.
Vladimir Klisic Send private email
Friday, July 21, 2006
I thinks something modeled after Java's JDBC would work here. The interface to the database is aways the same where the driver determines the database specifiec code underneith.

You only need to work with a few basic classes at the business level...

Friday, July 21, 2006
I have created something similar to how JDBC (and many others) do it, and it's way hard to create on your own.

Your if-then-else is simple, but it is not pretty.

If you can, try to use Polymorphism. Create a Parent class and descend the classes of each database from it. This way you don't need to repeat yourself too much, you can separate the SQLs in specific files which will be easier to edit, and it will be consistent.

I use Polymorphism for it and it works a treat, but even it may be overkill for you. :-)

class Common
class MySQLInterface < Common
class SQLiteInterface < Common
class SQLServerInterface < Common

Then each one can be used when necessary. As it will be used mostly in this one program of yours, it shouldn't be too difficult to create. And 3 databases is easier than to try to support 8 or more (which I do, partially at least).
Friday, July 21, 2006
Thank you all for your comments.  I've sort moved to a DB-specific class in another app, but couldn't quite see how to do it in this first app (although now that I've read the comments, it seems so obvious!).

Thanks again
Doug Send private email
Friday, July 21, 2006

One think I forgot to mention is typing.

If you are writing the app in C++, but the data can go to 1 of 3/4 databases, how will you manage the typing?
D in PHX Send private email
Friday, July 21, 2006
The way I have handled that in the past is to access databases via a DAL DLL that is dynamically loaded at runtime, based on configuration settings. All the application sees is a DLL that exports a set of “C” functions. The DLL does what is appropriate depending on the database. This method takes longer to code and is more difficult to test, but seems to work well.

I also use ADO, and I have found SQL can be quite portable between databases is you set certain conditions. For instance, you can obtain more portability using query parameters than you can with hard-coded SQL. And you should avoid BOOL/BIT datatypes, opting instead for tinyint -- the reason being that some databases accept only TRUE/FALSE for bool, while other require 1 or 0.

There are issues with JOIN, but if you stick to the most common syntax, you will be okay.

You will never obtain the highest performance using ADO for all your data access, but the ability to have portable data access is very compelling.

One good thing about the DAL DLL method is if you do decide to use native database libraries, the application will never know the difference.
MBJ Send private email
Friday, July 21, 2006

This topic is archived. No further replies will be accepted.

Other recent topics Other recent topics
Powered by FogBugz