Extension:OdbcDatabase
 | This extension is currently not actively maintained! Although it may still work, any bug reports or feature requests will more than likely be ignored. If you are interested in taking on the task of developing and maintaining this extension, you can request repository ownership. As a courtesy, you may want to contact the author. You should also remove this template and list yourself as maintaining the extension in the page's {{Extension}} infobox. |
 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 | README |
| Quarterly downloads | 0 |
| 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
[edit]The extension can be retrieved directly from Git [?]:
- Browse code
- Some extensions have tags for stable releases.
- Browse tags
- Select the tag
- Click "snapshot"
- Each branch is associated with a past MediaWiki release. There is also a "master" branch containing the latest alpha version (might require an alpha version of MediaWiki).
- Browse branches
- Select a branch name
- Click "Continue"
Extract the snapshot and place it in the extensions/OdbcDatabase/ 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:
cd extensions/
git clone https://gerrit.wikimedia.org/r/mediawiki/extensions/OdbcDatabase.git
Installation
[edit]- Download and move the extracted
OdbcDatabasefolder to yourextensions/directory.
Developers and code contributors should install the extension from Git instead, using:cd extensions/
git clone https://gerrit.wikimedia.org/r/mediawiki/extensions/OdbcDatabase - Add the following code at the bottom of your LocalSettings.php file:
wfLoadExtension( 'OdbcDatabase' );
Done – Navigate to Special:Version on your wiki to verify that the extension is successfully installed.
Configuration parameters
[edit]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
[edit]No special User Rights used.
Setting up unixODBC and FreeTDS
[edit]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
[edit]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
[edit]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
[edit]My working solution consists of the following software components, with the current version numbers I have running listed.
| 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
[edit]There are few other possible combinations, including:
Microsoft PHP Drivers
[edit]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
[edit]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
[edit]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:
- I could not figure out how to set this environment variable for Apache/PHP/MediaWiki.
- 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.