Extension:OdbcDatabase

From Linux Web Expert

MediaWiki extensions manual
File:OOjs UI icon advanced-invert.svg
OdbcDatabase
Release status: unmaintained
Implementation Database
Description Supplies a basic extension of the Database class to support access to Databases via an ODBC driver.
Author(s) Roger Cass (chiefgeek157talk)
Latest version 1.0 (2013-03-04)
MediaWiki 1.20+
PHP 5.3.2+
Database changes No
License Apache License 2.0
Download
<translate> Download extension</translate>
<translate> Git</translate> [?]:

README
Quarterly downloads Lua error in Module:Extension at line 172: bad argument #1 to 'inNamespace' (unrecognized namespace name 'skin').
Public wikis using Lua error in Module:Extension at line 172: bad argument #1 to 'inNamespace' (unrecognized namespace name 'skin').
Translate the OdbcDatabase extension if it is available at translatewiki.net

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.

Download

<translate> The extension can be retrieved directly from Git</translate> [?]:

  • <translate> Browse code</translate>
  • <translate> Some extensions have tags for stable releases.</translate>
  • <translate> Each branch is associated with a past MediaWiki release.</translate> <translate> There is also a "master" branch containing the latest alpha version (might require an alpha version of MediaWiki).</translate>

<translate> Extract the snapshot and place it in the <tvar name=name>extensions/OdbcDatabase/</tvar> directory of your MediaWiki installation.

If you are familiar with Git and have shell access to your server, you can also obtain the extension as follows: </translate>

cd extensions/ git clone https://gerrit.wikimedia.org/r/mediawiki/extensions/OdbcDatabase.git

Installation

  • <translate> [[<tvar name=2>Special:ExtensionDistributor/OdbcDatabase</tvar>|Download]] and move the extracted <tvar name=name>OdbcDatabase</tvar> folder to your <tvar name=ext>extensions/</tvar> directory.</translate>
    <translate> Developers and code contributors should install the extension [[<tvar name=git>Special:MyLanguage/Download from Git</tvar>|from Git]] instead, using:</translate>cd extensions/
    git clone https://gerrit.wikimedia.org/r/mediawiki/extensions/OdbcDatabase
  • <translate> Add the following code at the bottom of your <tvar name=1>LocalSettings.php </tvar> file:</translate> 
    wfLoadExtension( 'OdbcDatabase' );
  • File:OOjs UI icon check-constructive.svg <translate> Done</translate> – <translate> Navigate to <tvar name=special>Special:Version</tvar> on your wiki to verify that the extension is successfully installed.</translate>

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 tsql -C command: 

# tsql -C
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

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

{{#get_db_data:
db=mydb (name must match the one used in LocalSettings.ini)
|from=MyTable as t
|data=LastName=t.LAST_NAME,FirstName=t.FIRST_NAME }}

{{#external_value: LastName}}, {{#external_value: FirstName}}

You can troubleshoot your database connection by using the isql 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: 

   public static function getSoftwareLink() {

...should be changed to: 

   public function getSoftwareLink() {

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.

OdbcDatabase Successful Software Components
Component Version
Operating System RHEL 5.6
PHP 5.3.3
Database MySQL 5.0.84
MediaWiki 1.20.3
External Data 1.3.6
OdbcDatabase 1.0 alpha 1
unixODBC 2.3.1
FreeTDS 0.91.2-e15
SQL Sever 2005

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 {{#for_external_table:}} 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 sqlsrv_*, 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 mssql_* 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 mssql_* 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 odbc_* 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 odbc_* functions. I invariably found that I received segmentation fault or zend_mm_heap corrupted, depending on the particular odbc_* function I invoked. Specifically, odbc_tables would work fine and return all the tables, but would end with the "zend_mm_heap corrupted" error, and odbc_exec 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.

See also

Retrieved from "https://www.linuxwebexpert.com/wiki/index.php?title=Extension:OdbcDatabase&oldid=11841"