Slashdot Mirror
Coding for Multiple Databases in C/C++?
scorp1us asks: "I'm working on a project which was coded in C/C++ to use a MySQL database. I've since been ordered to make it work with other databases as well. I found one that was close to what I want, SQLAPI++, but it is not database agnostic. You end up using the same function calls but you also end up having SQL for each database. I'm looking for a product that looks like DirectX, but for databases (DirectX emulates features in software if no hardware acceleration is present.) PHP's ADOdb is what I want, but I need it in C/C++. Has anyone seen something like this? My last requirment that it must work for MySQL, MS SQL server, and Informix, and work under Win32 and Linux."
Dude, he's looking for something above SQL itself! Different servers have slightly different SQL syntaxes, thus he needs something more abstract.
I've been working on some projects that will access multiple db servers and took a different approach to it. Since most db servers have different advanced commands, I decided to write specificly for each while making everything else the same. Basicly I create a different project for each db and using conditional defines I keep all access to the database in its own files. So if talking to ms sql I have a complete set of functionality that deals with ms sql. For oracle I have a different set of functionality, and so on for each server. I end up with a different program for each db server.
If you are never going to have a large set of users hitting the sql server at once then it should be ok to go generic, but if the user base is going to be larger then I don't see how you can avoid using db server specific commands to increase performance and minimise overworking the server.
I'm sure there are other easier ways to pull it off but I like quality and feel the extra work is justified to make it run well on every server I choose to support. Hope this gives you some help in figuring out what you want to do.
If ignorance is bliss, the world is full of blissful people
Do the database abtraction yourself (I'll tell you why later..).
the code should be compartmentalised so there's a switch somewhere (either runtime of compile time) thats says - for this database do that to get the data I need in the datastructure (or put the data into the ddb).
Now as to why....
most RDBMS's have wonder extentions to SQL and difference ways of doing this. In order to optimise a certain query or insert/update you'll have to to mangle the SQL accordingly. Also some make heavy used of stored procedures for optimising techniques and others have no idea of a SP.
It's like porting to code from one language to another - SQL isn't generic enough IMHO to make you RDBMS perform at a consistent rate of knots.
Also many Big Iron RDBMS (Oracle, DB2) assume you access to a Database Administor who can monitor the database and keep things ticking over. Others, eg SQL-Server, don't assume this (which can or cannot be helpful) etc etc.
Bing Bing. Mod that up.
Here's the deal. No matter what abstraction you use, you have to code for the lowest common denominator. This means that even if a DB supports subselects, you can't use it. Ditto for features like MySQL's "LIMIT" and such. Transactions? Forget em. You will also need to use only a limited set of data types.
This also means that you will have to emulate most advanced features yourself (which can DRASTICALLY complicate your project.)
A side effect is also that you won't be able to take advantage of many of the performance enhancing features of different databases, meaning that your code may run THOUSANDS of times slower depending on the DB, schema, etc. than if you supported a limited set of databases directly in your code.
I guess it all depends what your needs are, and what tradeoff's you can handle. But you do need to ask yourself the question: Is it truely worth it? Many of the large application vendors require a specific DB for all the issues described above and by other people.
What kind of programmer would be writing database software and not know about ODBC..?