Topic awaiting preservation: Coding to handle various databases

I'm doing a project at the moment (actually in vb.net) and want to do some research in this area. The principles are the same for any coding, php etc.

Basically I have an interface which gets records from a database etc via the relevant queries. What i'm trying to figure out is what is the best way to do the code so that you could relatively *easily* change the type of database if you wanted. I will be using a postgre database and might want ot change it to mysql or even a nastier JET (access) database.

Im just wonder if there is a method that people use for this? Is there another layer that passes back arrays of values etc. I've been looking into xml especially since vb.net has built in stuff for this - im just finding it hard to see how I could get benefit from using it.

Anyhow any thoughts from you all would be great thanks.

Commonly referred to as database abstraction -

http://www.gurusnetwork.com/tutorial/database_abstraction/

Basic princple, separate the code that's specific for each db, connection and so on from the rest of the non-specific code.
Then you write a layer (procedural or OOP) that holds methods to insert, update, delete in the ways the application needs. This layer calls the specific code needed to connect to the db in question. The common layer you write should be the only thing the rest of the db sees.

What you should think about in the db-specific code is to make sure that the data is returned in the same format regardless of db.

/Dan

{cell 260} {Blog}
-{ ?There are two major products that come out of Berkeley: LSD and UNIX. We don't believe this to be a coincidence. - Jeremy S. Anderson" }-

I guess thats what I was thinking, the problem lies where some databases support some features where others dont. For examble using LIMIT in your queries which I do quite often these days for mysql. How would you normally handle that? Have something in the specific DB layer that removes it from the query string?

I would think the other way around - the actual query string that gets fed to the DB should be created by that abstraction layer based on the criteria passed to it from the interface.

That way there is nothing to remove. The query is built however it needs to be built depending on the DB being used.

I've never done this myself....but that seems like the better approach overall.

Yeh I see what you mean, I guess the database functions in the abstraction could use LIMIT within them depending on the query... Mmm at this stage i will probably only use postgreSQL straight up but I like to leave it changable in the future. Will have to see what I can come up with.

Thanks for the help

There's only one problem in Computer Science that can't be solved by another layer of indirection - I'll leave you to figure it out...

Ohhhh =/

Yeah I've had to work with abstraction layers designed to remove the SQL from the equation.

It always sucks.



.:[ Never resist a perfect moment ]:.

I can imagine for normal stuff it would be pretty simple, but if you want to do joins and subqueries it could be a prick depending how different the databases are.

Now to figure out how to create reports in vb.net wihout bloody crystal reports