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 is 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.

Setting up unixODBC and FreeTDS
The OdbcDatabase extension was developed to use on top of the PHP:ODBC, unixODBC, and FreeTDS for accessing Microsoft SQL Server. PHP:ODBC needs to be installed. FreeTDS need to be installed, but requires no special configuration in /etc/freetds.ini. All configuration is done through unixODBC parameters (see FreeTDS attributes available through odbc.ini).

The key configuration parameters are as follows:

/etc/odbcisnt.ini (part of unixODBC) [FreeTDS] Description=FreeTDS driver Driver=/usr/lib64/libtdsodbc.so.0

/etc/odbc.ini (part of unixODBC) [mysqlserverdb] Description            = Database via ODBC and FreeTDS driver Driver                 = FreeTDS (must match driver name in odbcinst.ini) Trace                  = No Server                  = (DNS name of IP address of server) Port                   = 1433 (for SQL Server) TDS_Version            = 8.0 (for SQL Server, passed to FreeTDS) ClientCharset          = UTF-8 (important to ensure nvarchar fields are translated correctly, passed to FreeTDS) Database               = MyDBName (the name of the database on the server) To determine what version to specify above for a sucessful connection, use the  command:

Compile-time settings (established with the "configure" script) Version: freetds v0.91 freetds.conf directory: /etc/freetds MS db-lib source compatibility: no       Sybase binary compatibility: yes Thread safety: yes iconv library: yes TDS version: 4.2 iODBC: no                          unixodbc: yes SSPI "trusted" logins: no                          Kerberos: no
 * 1) tsql -C

If your output looked like this, then you would specify a TDS_Version of 4.2.

LocalSettings.ini (as needed for the ExternalData extension) $edgDBServerType ['mydb'] = "odbc"; (Type must be 'odbc' for the OdbcDatabase extension) $edgDBServer     ['mydb'] = "mysqlserverdb"; (Must match name in odbc.ini) $edgDBName       ['mydb'] = "UNUSED"; (Required by ExternalData extension, but not used since DB name is included in the odbc.ini entry) $edgDBUser       ['mydb'] = "dbuser"; (DB username) $edgDBPass       ['mydb'] = "dbpass"; (DB password) $edgDBTablePrefix ['mydb'] = "dbo"; (Not necessary, but convenient)

To use 'mydb' on a wiki page for the ExternalData extension, specify something like the following:

My Wiki Page , You can troubleshoot your database connection by using the  command: $ isql -v mysqlserverdb dbuser dbpass If your settings are correct, you should be able to connect to the database and perform SQL queries.

Possible code changes
If you're using MediaWiki 1.22 or later (and possibly 1.21 as well), you will most likely need to make a small change to the file OdbcDatabase.body.php. Around line 318, the declaration: ...should be changed to:

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.