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."
Won't one of the microsoft acronyms work: ODBC, ADO, DAO?
http://www.unixodbc.org/
Read... Enjoy...
ODBC provides a platform independent API. You can use it with any database that provides ODBC drivers.
That's not a Troll it was suposed to be funny...
But back to topic... This is still young but has potential:
http://www.orcane.net/freeodbc++/
-G
Look at Perl's DBI module. It works with tons of databases. However, each database needs its own driver or DBD module.
It might prove useful to look at how and what they did.
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
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
SQL Relay does this (site looks like it is down at the moment so I'm pointing to the google cache). It is a persistent database connection pooling, proxying and load balancing system for Unix and Linux supporting ODBC, Oracle, MySQL, mSQL, PostgreSQL, Sybase, MS SQL Server, IBM DB2, Interbase, Lago and SQLite with APIs for C, C++, Perl, Perl-DBD, Python, Python-DB, Zope, PHP, Ruby, Ruby-DBD and Java, command line clients, a GUI configuration tool and extensive documentation. The APIs support advanced database operations such as bind variables, multi-row fetches, client side result set caching and suspended transactions. It is ideal for speeding up database-driven web-based applications, accessing databases from unsupported platforms, migrating between databases, distributing access to replicated databases and throttling database access.
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.
Just another "Cubible(sic) Joe" 2 17 3061
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.
11*43+456^2
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.
More importantly, he's looking for c/c++ libraries and not Perl.
I saw an ad in DDJ claiming that qt can be used for multiple databases.. It also works on multiple platforms.
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.
Only 'flamers' flame!
The Gnome Guys were working on a ORBit based SQL abstraction layer. Haven't looked at it in a while.
Open Source Identity Management: FreeIPA.org
Oracle, ODBC and DB2 Template Library
I've used it many times in the past (talking to Oracle, MS Access, SQL Server, etc).
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.
There aint no pancake so thin it doesn't have two sides.
There is a component of J2EE that uses SQL-Like commands and compiles them into the target database platform. With C++, you would have to write your own translator. You are looking for another layer of inderection, but you might not find it with c++ since it is not very post compile dynamic.
http://www.gnome-db.org/ The libgda library component provides a database-generic C API. It has providers for MySQL, PostgreSQL, Oracle, FreeTDS (which is used for Microsoft SQL Server and Sybase), ODBC, DB2, MDBTOOLS (Microsoft Access), and XML. Libgda works on Linux, Unix, and Windows. Note: libgda does not require GNOME, it only requires GLib found at http://www.gtk.org/ and libxml2. Libgda is used as the basis for System.Data.OleDb on the Mono platform available at http://www.go-mono.com/ado-net.html Libgda provides the database-generic API for GNOME-DB with inspiration based on ADO/OLE-DB. The Libgda Manual is at: http://www.gnome-db.org/docs/libgda/index.html Excerpt from the web page: The GNOME-DB project aims to provide a free unified data access architecture to the GNOME project. GNOME-DB is useful for any application that accesses persistent data (not only databases, but data), since it now contains a pretty good data management API. GNOME-DB consists of the following components: gnome-db: Front-end for database administrators. libgnomedb: Database Widget Library. These widgets are integrated with the latest versions of glade. libgda: data abstraction layer. It can manage data stored in databases or XML files and it can be used by non-GNOME applications.
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.
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.
-I like my women like I like my tea: green-
What kind of programmer would be writing database software and not know about ODBC..?
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:You might try having a look at the DTL which aims to make ODBC recordsets look like an STL container. portably...
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.
There is a project named UESQLC that is a precompiler that translates SQL-92 embedded code, to whatever SQL dialect and API the backend DBMS could have. It currently has support for PostgreSQL, ODBC and Oracle, but backends are defined using SGML documents, so the precompiler is easy to extend simply adding new documents. The only host language supported now is C++, but the author (me :) is adding support for Java as host language.
The features are great, but the product does not appear to exist anymore. Was it open source? If so, is there a mirror?
I've written an SQL DB access library that exposes an JDBC-like C++ interface. It's called GQL (Generic SQL Library). It currently has drivers for MySQL, PostgreSQL and SQLite.
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.
Ex vitio sapiens aleno emendat suum
Use Borland Kylix for C++. It has a robust database engine called the BDE (Borland database Engine) which abstracts the differences between the different databases as long as you stick with SQL 92/99. It compiles happily on both Linux and Windows.. it makes use of a concept called database aliases which can be setup using the BDE Administrator..using this you can completely change the database - say, from SQL Server, you could move to Oracle for instance without even having to recompile your application.
Highly recommended.
Probably a relation of the guy here who programs in GWBASIC and doesn't know that you can compile it with QBASIC.
No sharp objects, I'm a programmer!
You can try the OTL which can be found here .
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.
SELECT * FROM User WHERE Clue > 0
0 rows returned
You can try the OTL which can be found here http://members.fortunecity.com/skuchin/home.htm.
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.
SELECT * FROM User WHERE Clue > 0
0 rows returned