Extension:OdbcDatabase

The OdbcDatabase extension is intended for use with the External Data extension to add access to any data source via an ODBC driver. It has been developed on my own particular test platform (details below) and has not been tested yet on any other platforms.

It is significant to note that this extension in NOT intended to supply sufficient capability to allow the primary MediaWiki database to be hosted though an ODBC connection using. This extension provided limited functionality really only suitable to read-only uses, such as the External Data extension.

Configuration parameters
This extension is not configured directly. Rather, it adds a new database type for use in other features or extensions that select the type of database to use, such as the External Data extension.

The database type supplied by this extension is "odbc". In External Data, one might use this in the following directive:

$edgDBServerType['mydatabase'] = "odbc";

User rights
No special User Rights used.

History and Context
The OdbcDatabase extension was created to solve a very specific, but possibly widely encountered, problem. I needed a way to pull data from a SQL Server 2005 database and display it on wiki pages, which are hosted on MediaWiki running on RHEL 5. In my case, the pages represented elements of an Enterprise Architecture, and the database contained basic attributes about each element, like name, Owner, Architect, description, etc. It was nice to be able to generate a wiki page for each element of the Architecture, but I wanted a way to display those key characteristics from the source database when the page was visited without having to actually edit the page each time the data was updated in that database.

Early on, I found the External Data Extension, which looked to be exactly what I needed (and is, it turned out). However, getting External Data to communicate with SQL Server proved challenging.

Working Solution
My working solution consists of the following software components, with the current version numbers I have running listed.

The connectivity is as follows:

MediaWiki ==> External Data ==> OdbcDatabase ==> odbc_* ==> unixODBC ==> FreeTDS ==> SQL Server

The solution appears to work reliably when there is a moderate amount of data. I have been able to use complex JOIN syntax to select individual fields across multiple tables with success. I did find a problem retrieving a large number of large text fields using the  function. Returning 200 results with a 100-200 character description field caused no results to be returned. I am not sure where in the stack the failure occurred, but it hasn't been critical for my needs (yet).

Other Solutions Attempted
There are few other possible combinations, including:

Microsoft PHP Drivers
The PHP::SQLSRV module provides the functions, work great, but only work on Windows. I do not know if there is a SqlsrvDatabase extension that uses these function calls to extend implement the Database Class. I can't seem to find one.

If this suits you, you can download the drivers from Microsoft, but since there is no Database implementation, you wouldn't be able to use it for External Data anyway.

MssqlDatabase
This is an implementation of the Database Class provided by the MSSQLBackCompat Extension. This extension was written to allow the use of the  functions provided by PHP::Mssql module. It requires the use of FreeTDS to provide the actual driver implementation.

The connectivity would be:

External Data ==> MssqlDatabase ==> mssql_* ==> FreeTDS ==> SQL Server

I tried this solution given the environment above. I found I could connect to SQL Server using FreeTDS with no problems, but I could not get any data from the  functions using a test script. Needless to say, the MssqlDatabase extension also returned no data. I never figured out why.

Microsoft ODBC Drivers for Linux
Our friends at Microsoft have provided an ODBC driver implementation suitable for use on Linux and compatible with unixODBC. This allows the use of the  functions in PHP. Note that this method still requires this OdbcDatabase extension to be able to implement the Database Class. Connectivity would be:

External Data ==> OdbcDatabase ==> unixODBC ==> Microsoft ODBC Drivers for Linux ==> SQL Server

This is the solution I worked hardest on. In the end I wrote a test script to directly call the  functions. I invariably found that I received segmentation fault or zend_mm_heap corrupted, depending on the particular  function I invoked. Specifically,  would work fine and return all the tables, but would end with the "zend_mm_heap corrupted" error, and   would cause a segmentation fault.

After much research, I found that the following prevented all the errors in my test script:

$> USE_ZEND_ALLOC=0; php test.php

That's fine, but:


 * 1) I could not figure out how to set this environment variable for Apache/PHP/MediaWiki.
 * 2) It didn't really look like a very good idea in the first place. It turns off the ZEND memory management functions!

My suspicions, and they are only that, are:


 * There is a conflict between my 64-bit RHEL 5.6 environment and the possibly 32-bit Microsoft drivers causing a buffer overrun or some such problem.
 * There is a bug in, or incompatibility with, the Microsoft Driver where a result set or other data structure gets deallocated twice.

I could not figure it out. If you do figure it out, it means that this extension (OdbcDatabase) could be used with the Microsoft ODBC Drivers for Linux rather than the FreeTDS drivers. Not sure what that gains, but it sounds like a generally good improvement.