Database conversion
Some History
In the old days getting data out (or in) of an iSeries database resorted to exporting CSV files or the like. A while ago I worked for a company that provided insurance administration facilities to diverse set of insurance brokers. Each broker had one or many insurers that underwrote the products they were selling. This project started out with the requirement of dumping DB2 data from the iSeries running the insurance application to provide the insurers data for their internal reporting analysis. The catch was that depending on the individual insurer, data needed to be massaged from the native application format to that required by the insurer. Some wanted CSV, some wanted XML, some wanted summary data, and some wanted detail.
Again, in those days manipulating XML with RPG was an arcane art. Also having had some experience with the (appalling) speed of manipulating ASCII data on an iSeries and given that there was a LOT of data to process, I decided that rather than trying to build an iSeries solution with RPG, a language more amenable to manipulating XML and CSV and other PC type data formats was the answer. PHP or Java suggested themselves as a natural fit since they could run on any given platform, and thus I could execute on the iSeries or on Intel architecture as run time dictated. The skill set of people in that organisation (along with some performance benchmarks) settled the debate with PHP as the answer.
All good and well, the application ended up being able to run on iSeries using native DB2 data access or Windows using ODBC. As an aside, the Intel runtimes were some 60% faster than running native on the iSeries. All was well, but then the world changed as it so often does; some clever insurer wanted us to populate a MS SQL database, and very soon after that one of the big brokerages wanted us to populate a MySQL database for MIS purposes. CSV dump from DB2 and import to the target database proved too slow, and besides the data still needed manipulation before it was imported to the target. This manipulation already existed in the PHP application, all that was needed was the ability to write into that target database.
I found the PHP PDO set of data access routines to have too many differences between DB formats to be generically useable without a wrapper. Also, around that time, our iSeries was being upgraded (hardware and OS version), and I was fairly confident that runtimes on the new machine would be vastly improved over what I was seeing on the Intel server. I thus wanted to keep the application executable on the iSeries and looked for a way to get the iSeries to write directly to MS SQL Server. This led me to JDBC and (give that the existing application was not Java) also to a JDBC bridge. And I discovered php-java-bridge. This was brilliant, I could run it on an iSeries, I could talk to it with PHP, and I could talk to any database for which a JDBC driver existed. Out of this grew a PHP class that was able to read and write between DB2, MS SQL, and MySQL.
Over the next many years the class grew to be able to talk to DB2 Universal DB, DB2 400, MySQL, Oracle, PostgreSQL, Progress, SQL Lite, and Microsoft SQL Server.
In its final incarnation, this is no longer (only) a PHP class (although I have kept that class current), but is a compiled C library that can and does execute on Windows or Linux, or the iSeries. It is able to read and write data between any of these Databases and has been used in a whole lot of differing applications (both PHP and C) including a full database replication application.
All of the bits that comprise the class / library are freely available, however given the time (over 14 years) that I have personally spent putting them together and refining the quirks of individual databases to provide a seamless interface, I am unlikely to release this as Open Source, and will monetize it as and where I can.
For the Technical minds
To facilitate calling php-java-bridge from C I reworte the "client side" interface. The original client class shipped from sourceforge relied on reflecting an arbitrary Java class as a PHP class, so for example one could write:
$i = new Java("java.math.BigInteger", "6");
and treat $i as any other PHP class to reference its properties and methods e.g.
$i->add($i2);
echo $i->toString();
and so on.
This is would have entailed a lot of fancy footwork to make C try and emulate a new arbitrary class, and in all events, the reflected Java classes in PHP seemed to have issues in terms of garbage collection: Specifically, multiple instantiations of a PHP class that itself instantiated Java classes caused memory leaks. This I could live with (it was not a significant leak), however I also found that the server side of the bridge would in certain circumstances not release the real Java class that was instantiated via the bridge until the PHP script terminated. This is a big problem when you need to drop a JDBC connection in the middle of a long running script!
My re-written client interface exposes a set of plain functions that do not suffer from any of the issues above, and still give full access to Java functionality from PHP or C. Thus the code snippet above becomes (in C just for variety):
javaObject i= constructJobject(jvm, &anException,"java.lang.Long", "%d", 6);
char *ans = getJstring(jvm, i);
printf("%s", ans);
or in PHP
$i = constructJobject($this->jvm, $anException, "java.lang.Long", "%d", 6);
$ans = getJstring($this->jvm, $i);
echo $ans;
Albeit I am reluctant to publish the unified database access class or library I have no such worries about the Java Bridge functionality. I have offered my efforts on the client interface to the php-java-bridge people, but it seems that the project is no longer in active development. Nevertheless the server side of the bridge is stable, and if this lot can be of use to you, the functional PHP client side include, as well as the C client library source code with documentation on usage is available in the download area.
Money talk
If you discover a need or use for a library that can talk across many different database platforms, I am willing to sell object code of both the library, and code that will actually do the transform for you. I am not very keen to release PHP code; that is not copy protectable in any real fashion. However if you are in a PHP environment where such functionality would be useful and you have some $$$ to spend, I am willing to discuss.