Coding for Multiple Databases in C/C++? 54
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."
Re:On Slashdot... (Score:1)
But back to topic... This is still young but has potential:
http://www.orcane.net/freeodbc++/
-G
ODBC (Score:4, Informative)
Read... Enjoy...
Re:ODBC (Score:2)
ODBC (Score:1, Redundant)
Perl DBI (Score:2)
It might prove useful to look at how and what they did.
Re:Perl DBI (Score:1, Insightful)
Re:Perl DBI (Score:2)
Re:Perl DBI (Score:3, Insightful)
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.
Re:Perl DBI (Score:2)
I'm hoping someone can recommend one, because it would simplify my life, too.
Re:Perl DBI (Score:2)
I am not sure most of the popular RDBMS are even a superset of the "standard". (Which standards year? They release a new one every now and then, and not all vendors conform.)
And, the standard seems wishy-washy on some things, like case-sensativity in compares, date formats, quotes around numbers, etc.
Plus, there are a lot of nice non-standard features out there that would be a bear to emulate, as a nearby message said.
A have not even seen an attempt to put a wrapper around SQL such that the DB engine can be swapped to another with little or no rewrite, unless you stick to the bare-bone basics (AKA, "lowest common demoninator"). If you are gonna do that, then you might as well just use simple SQL statements to begin with and chuck the wrapper. (But it might take multiple simple statements and more CPU to do what one complex one may accompslish.)
It would be nice to see a cleanup or better replacement for SQL. But it is too entrenched at this point.
also (Score:2)
RE: Coding for Multiple Databases in C/C++ (Score:4, Informative)
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 (Score:4, Insightful)
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.
Re:Multiple DB's (Score:3, Interesting)
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.
Re:Multiple DB's (Score:1)
I worked for years with the Borland Database Engine, and quite honestly its very slow. ODBC isn't much quicker...sometimes its slower. But basicly by having different db sections does alow me to handle different databases problerly. And you are correct in that you do have to rewrite the whole backend, but when its done the performance is much better and you have a beter product.
Re:Multiple DB's (Score:1)
#ifdef
\\ Solaris
#ifdef
\\ AIX
#ifdef
\\ HP-UX
etc...
Is no way to code, no way to be portable, and no way to maintain.
You really tweaked the shit out of everything! But now your life is hell.
SQL Relay (Score:3, Informative)
Re:Does not exist anymore (Score:1)
It does exist and is actively being developed. www.firstworks.com runs off a home box on a dsl line. His dns entry seems to have disappeared. Keep checking it should come back up.
Go to egroups.yahoo.com, you will find a mailing list you can join or just peruse the messages for sqlrelay.
They also have a project on sourceforge, but it is not kept up to date. You can download version 0.21 from there, but 0.32 is the latest version. Sorry, don't know of any mirrors.
SQLRelay is an excelent database pool daemon, but it does not run on windows which was one the requirments listed by the originator's question.
You might be able to compile the client code on Win32 to connect to an SQLRelay daemon running on *nix, but the server code relays too much on fork() for straight compilation on Win32 to be possible.
And isn't SQL a standard? (Score:5, Interesting)
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? (Score:1, Interesting)
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:And isn't SQL a standard? (Score:1)
Aren't indentity columns what you're looking for?
RogueWave? (Score:4, Informative)
Re:RogueWave? (Score:3, Interesting)
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!
Your own calls. (Score:5, Insightful)
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.
Re:Your own calls. (Score:1)
Although your basic SQL syntax is transportable, you've started with a database that implements only a subset of the syntax (MySQL).
By coding the SQL yourself (via a database API within the application layer) you can use different implementations for Oracle, SQL Server, etc. This means you'll be able to not only use the valid SQL that wasn't supported in MySQL, but you'll also be able to take advantage of various inconsistently implemented extensions. Sure, it's a drag to use non-standard code, but in the end it'll save you from having to write far more code in the application layer.
Additionally, you may find that your application runs fine in MySQL - but that the same logic results in deadlocks, etc in other databases - because you are already taking advantage of extensions to the ANSI SQL spec in MySQL (either implicitely or explicitely).
So:
- update joins
- delete joins
- limited response from selects
- creating sequence numbers
- outer joins
- date functions
- transaction management
- etc
Are all somewhat optional, but are very much worth pursuing in almost any database application.
qt does this (Score:1)
Re:qt does this (Score:1)
build a db layer (Score:3, Interesting)
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.
Something from GNOME (Score:2)
Oracle, ODBC and DB2 Template Library (Score:1)
I've used it many times in the past (talking to Oracle, MS Access, SQL Server, etc).
Data Access objects and the Bridge pattern (Score:1)
the bridge pattern is generally the best way to design device drivers, db drivers, etc. what you have to do is implement it so it makes sense to your data model.
Its called J2EE (Score:1)
GNOME-DB (Score:1)
gnome-db's libgda (Score:2)
It's worth noting that libgda can be used by non-GNOME applications.
It's also worth mentioning that libdga has a C API. I have no idea if someone has written a C++ wrapper for it.
Oh, and finally, I noticed that libgda had a release on July 8, 2002, moving toward a 0.9 beta, so it looks like it's still actively developed.
use JDBC with C++ (Score:3, Interesting)
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.
ODBC (Score:2, Insightful)
Re:GWBASIC (Score:1)
Qbf does this for Odbc, mysql and soon postgres (Score:1)
Pros:
- Completely database independent, including using an internal dictionary.
- Can update database tables automatically.
- Provides a nice entity/relation-view of the database.
- XML import/export support.
Cons:Database Template Library (Score:1, Informative)
How simple is your data model? (Score:1)
This problem is not one of transport, its one of SQL capabilities. The issue is not how you get there (i.e., ODBC vs. JDBC vs. RogueWave et. al.). The issue is what you want to do once you get there.
Consider MySQL and Postgres vs MS SQL, Oracle and Sybase. MS SQL and Sybase are basically the same. In MySQL and Postgres you don't have stored procedures or nested queries. MySQL support for transactions depends on the version. MS SQL et. al. support all of these things. So, for example, in MySQL, you cant do the following: update (select * from t_1, t_2 where complex condition) as A set A.xxx=where another condition.
I believe the best advice is to build a data abstraction layer for your application (i.e., a base class with virtual member functions for each operation) and to build a subclass for each different database you want to support that's able to exploit all the advantages of each database.
I've had success with this approach. This is the kind flavors and variations issue that C++ (and other OO languages) are good for.
Re:How simple is your data model? (Score:1)
GQL (Score:1)
ODBC caveats and warnings (Score:1)
ODBC is fine for light duty queries (~100's, maybe 1000's of rows). On the order of 10K rows things begin to get dicey. When you get to 100K - 1M+ rows, go out for coffee. Twice.
Beyond performace, ODBC is a cumbersome beast to use in a C++ application. Clearly it's a case of a heavily over-engineered solution without a good understanding of the problem. Writing an ODBC driver is even worse (I've written several) -- the spec is the size of a phone book. Bottom line, stay away from it if at all possible, unless you're relying on some other abstraction layer that keeps the ODBC API away from you.
I have used Rogue Wave's DBTools.h++ (now called SourcePro DB) package and found it to be terrific, provided that they have a "native" (i.e. non-ODBC) library for all of the databases on your requirements list. They do not have a mySQL native library, but you may wish to consider using SourcePro DB anyway and writing your own mySQL native library. Rogue Wave sells their source code (or at least they used to), so you can purchase it for, let's say, the Informix library and use it as a guide for your own implementation.
If you don't have any CapEx or budget is otherwise an issue, then don't discount SQLAPI++. Based on what you asked for, the only thing it doesn't appear to do that you need is SQL Server on Linux - it looks like it does everything else. Consider writing that piece. (Perhaps if you contribute the code back to the SQLAPI folks they'll waive the shareware fee and even help you with the effort.) You may luck out and not even have to, since SQLAPI++ supports Sybase. Microsoft SQL Server is essentially Sybase -- they bought the System 10 codebase. Admittedly over the years the two products have diverged, but it's quite possible that they both still support the same networking layer. Worth a try before you start writing code.
Lastly and in a different direction, I have evaluated the "write in java compile with gcj" option, and have not found gcj sufficiently mature for this to be done purely. If you can get away with it, I've had good success writing quite performant database code in Java using JDBC and then hooking it up to the C++ app via JNI. By parameterizing the database connectivity in a properties file, you can thus switch databases just by changing a text file. I've used this technique successfully when I had to connect to MySQL, Oracle and SQL Server all with the same app, and it only took a day or two to write all of the code.
Borland Kylix for C++ (Score:1)
Highly recommended.
SQL Template Library (Score:1)
It is a C++ wrapper class for some to fht more popular DBs. It includes most of what you want;Oracle, MySql, DB2, ODBC, and I think Informix. I use it for Oracle conectivity. I have not had any problems with it. If anything, Uswe has saved me tons of work learning and re-learning the conectivity schemes of the various databases.
It is free for non-commercial use/ and or evaluation. If you need to license it I am sure the work saved would more than make up for the licensing fees.
SQL Template Libaray -- with URL (Score:1)
It is a C++ wrapper class for some of the more popular DBs. It includes most of what you want; Oracle, MySql, DB2, ODBC, and I think Informix. I use it for Oracle conectivity. I have not had any problems with it. If anything, using the template library has saved me tons of work, and by extension time, learning and re-learning the conectivity schemes of the various databases.
It is free for non-commercial use/ and or evaluation. If you need to license it I am sure the work saved would more than make up for the licensing fees.