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

Re:Multiple DB's

[foobar104]

We have a product that's basically built like that. We have the database code in a small number of mutually exclusive DSOs, one for Informix, one for Oracle, and one for Sybase.

It was really a terrible idea, in retrospect. Last summer, it took us five months to write the Sybase database key. Five months! Just to re-implement code that was already there in the other DSOs!

I think we might have been a lot happier if we'd chosen ODBC from the start, but I'm just guessing about that, because I don't really know much about ODBC. I'm just thinking that it must have been better than what we decided 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.

Re:And isn't SQL a standard?

[Anonymous Coward]

If only that were true.... Having just come off a project porting code developed to run against an Oracle DB to MS SQL Server 6.1 (of all things). One thing I've learnt is that SQL is not the same on all RDBMSs. We have always used RogueWave to give us database independance and manage object relational mappings, but once we actually tried running against a different db we ended up spending two months porting our code. (& believe me we're not sloppy coders )

From the top of my head, a list of a few things that we had to change:

1) Oracle has nifty things called Sequences for generating unique numbers SQL Server has no such thing.

2) Oracle 7 uses non standard syntax for outer joins. SQL server does support ANSI outer join syntax but you are limited in how many other tables a table can outer join against. This forced us to completely rewrite a pretty complex query.

3) SQL functions such as for string manipulation or date time manipulation were quite different on each platform. ( Though I dont think this is covered by the ANSI standard).

4)Some column types such as FLOATs behaved differently.

5) On Oracle you can have pre & post update triggers, on SQL server on post update. (Which served us right for having triggers, but still a painful change anyway).

6)Transaction handling!!! Oracle will implicitly begin a new transaction when you start a new session or finish an existing transaction. If you are not careful SQL Server will run every command in its own transaction. We had to carefully revisit transaction handling & explicitly control it. What a nightmare.

7)Error handling differed on the two platforms. Oracle gave helpful error numbers which allowed you to decide how to behave in different error conditions. SQL Server was a lot less helpful.

8) Systems tables obviously varied. Code that called system tables was fairly well encapsulated but some rework was unavoidable.

Anyway I could go on. The stored procedure languages are totally different for example.

But you need a lot more than basic SQL92 to do database development. There is a huge amount that is not covered by the standard that a DB wrapper library should help with. Thats why we bought RogueWave, it didn't help very much. And there are parts of the standard which are not well implemented by RDBMSs eg outer joins & FLOAT columns.

I cant recommend any DB wrapping libraries, just share my tale of woe. I've done plenty of ODBC programming too & that also sucks.

Re:RogueWave?

[lprimak]

RogueWave SourcePro is an excellent product,
highly recommended (although a bit expensive)
They support connection pooling, threads,
completely DB-agnostic & runtime DB back-end switching, Envelope-Letter (non-pointer) memory management. Can't live w/o it!

build a db layer

[josepha48]

I have seen this here before and have worked on several systems that use something like this.

You need to code a db layer. The db layer should handle all the interaction with each database. In one case we used our db layer against db2, oracle, sybase, and another one I think called vsam(?).

The way you would do this is you would have a function to update, delete and insert to each table, and also get and reads. This would get ALL the fields of a table or you could get fancy and find a way to specify the fields of the table.

Something like #include
dbget(usertable, key); or dbread(usertable, key);

Yes something that simeple works. It gets all the fields in the table. If however you don't mind using vargs then you could have it take an sprintf format of dbget(usertable, key, fieldname1, fieldname2) and then get only those fields. This to me would be idea.

Ideally you would have a data dictionary that would store ALL the info on every table and all the fields and their sizes and types. Then using this you could have a perl script or C/C++ program (I'd do a script language a it may be easier IMHO) to read the data dictionary and output the necessary table.dat files that the programmers can then include and generate the necessary sql for the insert, update, delete, etc of the table data, also it would generate the necessary C/C++ API calls like the dbget() fns and the dbread() fns.

This is what most places I have been to do. They way that you update or add means that you need to set the fields in the included table.dat file to what you want and then you call the dbupdate(table); function and it uses the dat data.

In these include files you would have structure declarations that would define the layout of each table. Then the programmer would just need to declare a structure userstable mytable; and then user the db functions. This gives much flexability.

I hope this helps as a starting point.

use JDBC with C++

[aminorex]

With gcj, you can compile JDBC from java to
native code that can be transparently called
by C++ -- transparent modulo the run-time setup
call in your program's main(), that is.

Works slick. Use the mingw32 target of gcc 3.2
for Win32 platforms.