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

Comments

ODBC

[pci]

http://www.unixodbc.org/
Read... Enjoy...

RE: Coding for Multiple Databases in C/C++

[allouette]

afaik there is very little out there that is truly db agnostic

i have coded in Windoze using ODBC, it's not hard and there are some good books on the subject to help you through. i am not aware of what there is available across Linux platforms that is ODBC like

The key with ODBC is understanding the two-phase process in communicating with your DB. There are in fact two ODBC drivers, the one that extracts the commands required from the langauge you are coding in and t'other actually communicating that command to the DB engine.

i haven't looked at SQLAPI, but ODBC avoids embedded SQL statements and i suspect embedded SQL statements is what you want to go away from.

Your other problem is the features available from each DB may be very different. I has to work across MSSQL and Access and (initially) any other db. This 'any other db' was rapidly restricted to those that fit the SQL standards. (Note DB2 does not)

I can't this minute recall whether SQL92 has been superceded by SQL 97 (or whether i am getting confused between standards in my old age) but it is worth chasing the standards up so that you can identify standard and non-standard features especially in your existing code. Your existing code may not be agnostic

The way your db is treated in your existing code may / may not work for other db's in exactly the same way.

Good luck

Agnostic via standards?

HTH

Multiple DB's

[Darkstorm]

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.

And isn't SQL a standard?

[ratboy666]

It strikes me that SQL is a standard. Yes, the TRANSPORT of the SQL changes according to the database, but the SQL is, well... SQL.

So, that's the way I designed my database connector. Standardize the transport, and send SQL. Of course you keep the SQL you are using to the common base (what is it now, AFAIK SQL92, but I am not going to bother to look it up).

Otherwise, the use of SQL itself doesn't make sense. Look at an analogy: you are putting a scripting language into your application, and you choose an ANSI standard. Say (to be obscure), REXX. Now, different REXX vendors have different ways of linking in your scripts, so you loose faith. Instead of replacing REXX, you decide to write a Perl to REXX translator. Well... you go and BUY this thing from someone else. The claim is that this is a "universal" solution, because no matter HOW the REXX transport changes or someone modifies the ANSI standard language, you won't have to change your code. On the other hand, the translator itself is incomplete, and the vendor changes it 3 times a year forever (this actually happens, I have lost count on the number of times Microsoft has rolled out a new database connector!).

You should look at what SQL you intend on using, and check if the various vendors support that subset. Maybe ensure that you are using ANSI SQL. If the SQL database vendors AREN'T support ANSI SQL, take them to task! You shouldn't have to be buying additional software to make up a lack of support for the standard. And when this is true, the actual transport issue can be easily solved by your existing shim.

Ratboy.

RogueWave?

[photon317]

RogueWave's SourcePro DB is basically what you're looking for, and RogueWave is fairly well known in the C++ comminity. The only caveat for you is that it directly doesn't support MySQL. It does however support Oracle, Sybase, MS SQL, Informix, and DB2. It also supports generic ODBC on the back, and I'm pretty sure there's an ODBC interface for MySQL out there - so you could use it that way.

Your own calls.

[martin]

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.