« Surreal letdown? | Main | Spirited Away, annd The Duel »
May 23, 2003
PHP, ODBC, and everything in between
For about the last two years I've been threatening to rewrite large portions of ext/odbc (the Unified ODBC module) for PHP. Frighteningly enough, I've started. The basic goal is to provide a more modern interface, reflecting interface functionality found in some of the more popular database extensions (i.e. MySQL, pgsql), and a series of other improvements.
Work progresses and I believe the new functionality options have all been hammered out to the point of being static. The goal is to have this finished for inclusion in PHP5, as it readily breaks backwards compatibility completely. The problem with this is I've no real indicators of how many people use the ODBC extension anymore, nor does anyone really give me feedback when requested. I take the silence as being acceptance of the proposed changes, and will implement features/functionality that I deem necessary.
Essentially I've only added a few functions, but have drastically changed the underlying code to do this. For example you can now gain access to the odbc_environment, providing a larger amount of control to an ODBC developer. The first step was to upgrade from ODBC v2 to something more substantial (like ODBC v3.5). This transition is proving to be a bottleneck of sorts, but not the largest of them.
The largest bottleneck so far hasn't been a code issue, but rather my ODBC driver manager. When Apple introduced Jaguar they began to bundle a version of iODBC, but provided their own interface (Applications -> Utilities -> ODBC Manager) to it. Unfortunately, the Apple interface is poorly lacking in documentation, labeling, and ease of use. It seems as if the standard Apple development guidelines were thrown out, beaten with the ugly stick, abandoned, and left for dead when this gem was in the test and release phase. This becomes a problem when one is unable to diagnose why a DSN is not being found by the system.
Originally I had an installation of iODBC working with the Virtuoso database and was happily plugging along developing ODBC based test cases and systems. My upgrade to Jaguar bothered nothing. Eventually I decided to move to PostgreSQL to do some work on a more functional database. Exit usability, enter problems. I built the pgsqlodbc driver just fine for iODBC. After plugging in the appropriate values, I discover that iODBC cannot connect to pgsql installation. Thinking I built the binary wrong I recompile it and try again. The same result. More interesting is that the odbctest program cannot identify any DSN I've previously had entered now (???).
Further testing provides no solutions. I re-install the iODBC manager to discover that it too is still fubar, and does not recognize anything either. Now neither iODBC nor the Apple ODBC Manager seem to be working, leaving me high on dry on the development front. The iODBC help forum admins have suggested I've built PHP wrong, but when even their odbctest program doesn't work, I don't believe it's a PHP issue (I've also rebuilt numerous times since then). It seems others are having the same problems on the board too. Apple's help system seems to be devoid of any information regarding the ODBC Manager and how to make it work properly.
So I'm asking for help from those of who read this. I know OpenLink provides a driver to connect to pgsql back-ends, but charge a fee for this binary to which I can't/won't pay. I'd be willing to move to unixODBC, but have been stopped by the dreaded dlopen issue (and no I don't want to install dl_compat). I'd really like to know what has gone wrong here, and how to fix it.
Help.
Posted by Dan at May 23, 2003 09:19 PM
Comments
What makes PostgresSQL more functional than Virtuoso? I ask out of technical curiosity.
Re. your iODBC problems it is best you stick with the version provided and supported by OpenLink (Apple's bundle is as you have described, we tried to get them to bundle the SDK and the GUI Administrator component, but they simply aren't interested).
Posted by: Kingsley Idehen at May 24, 2003 08:58 PM
Virtuoso works extremely well for many pieces, but the demo license does not provide some functionality I would like. For example, multiple connection or even multiple user testing.
Virtuoso on OSX also seems to have (had) a series of issues with starting at bootstrap. It was impossible to enable this (despite having checked it off) as a default feature. Console logs show the result of running stating something to the tune of "you don't have permissions" to run due to some piece of the Virtuoso startup process. Forcing me to enable the root user, log out, log in as root, double click the startup script, log out, and log back in as me (no sudo didn't work). This got to be a constant problem and source of annoyance.
Finally I was having some issues with ODBC calls reverting to ODBC 2 calls. While I am not sure if this is/was Virtuoso or if this was my code, it is easier to step through the Postgre ODBC binary/source for debugging then it is to randomly guess what has gone wrong.
Despite the Apple ODBC Administrator sucking, it seems that I cannot even get the iODBC default SDK binaries working. So right now neither has provided much joy. I have posted on the iODBC support too...
Posted by: Dan at May 26, 2003 07:19 AM
Basically the Apple bundle ODBC Driver Manager for Jaguar, built by Apple using the iODBC 3.0.6 archive available from www.iodbc.org and integrated into their own ODBC Administrator does not work based on our testing at OpenLink Software.
I would thus recommend you use the OpenLink built and tested iODBC Driver Manager and Administrator which can be downloaded by following the links on www.iodbc.org or directly from the following URL:
http://oplweb.openlinksw.com/product/webmatrixsdk.asp
This will provide you with the prebuilt components for OS X such that you do not have to compile these yourself. They have also been built to use the same ODBC configuration files as the Apple ODBC Driver Manager/Administrator uses, thus any DSNs you have configured with these will be immdediately accessible using the OpenLink iODBC Driver Manager.
Further information on ODBC for the Mac can be obtained from the follwoing FAQ:
http://www.openlinksw.com/support/macosx-faq.htm
or
http://demo.openlinksw.com/macdemos
Note, that we have made Apple aware of these issues and are trying to work with them to resolve them in their next release.
HTH
Posted by: Hugh Williams at May 26, 2003 04:43 PM
what the fuck are you talking about?
Posted by: matt o'connell at May 27, 2003 12:47 AM
The information provided above should at least enable you to get the Virtuoso and any other ODBC Driver OpenLink provides working on Mac OS X again.
In order to get the Postgres Driver to work with the OpenLink iODBC you need to ensure that it is being linked against our Frameworked version of iODBC and not the version shipped by Apple.
In the iODBC sources available from www.iodbc.org, their are readme files on how to build iODBC on Mac OS X and also how we managed to build and test the MyODBC Driver.
The key requirement being to ensure that you are linking against our Frameworked version of iODBC using linker options of the following form:
cc ... -bundle -framework iODBCinst ...
Their is a file called README.MYODBC in the mac folder of the iODBC CVS archive on www.iodbc.org which provides detailed in structions on how to build the MyODBC Driver.
I would imagine for Postgres a similar process would have to be followed for this to work. If you provide the version/distribution of Postgres you are using then we can attempt to build this ourselves and provide specific instructions on how to build it.
Posted by: Hugh Williams at May 27, 2003 08:23 AM
I've been able to download the latest builds of iODBC, and there have been significant changes in the install from those that can be found on the www.iodbc.org download page. It might be wise to coordinate the two distributions.
This change presents a problem on another aspect which is, how to integrate the framework with PHP. I have a semi-solution ready to go, but is there a way to identify if someone is using the framework or the .so version easily?
Virtuoso is dead in the water. I downloaded a more recent copy, but the application complains that my license is invalid now. All attempts to have a new one sent have not been received. At the moment it's back to PostgreSQL as soon as I can get the driver to build with the framework.
Thank you Hugh for putting up some useful information. Small steps for now.
Posted by: Dan at May 29, 2003 09:20 AM