How do I connect MediaWiki on UNIX and Linux to SQL Server?

Use the SQL Server ODBC driver to connect MediaWiki to Microsoft SQL Server. You can then access data stored in SQL Server from MediaWiki on Linux and UNIX systems.

The SQL Server ODBC driver is available for 32-bit and 64-bit Linux and UNIX (AIX, HP-UX, and Solaris) platforms.

Prerequisite software

• MediaWiki
• MediaWiki External Data and ODBCDatabase extensions
• Apache
• PHP and PHP ODBC
• SQL Server ODBC driver

How to access SQL Server from MediaWiki on UNIX and Linux

These are the components that you need:

Step 1: Verify that you can retrieve SQL Server data from PHP outside of MediaWiki

The MediaWiki driver for ODBC databases uses PHP's Unified ODBC extension. Before attempting to retrieve your SQL Server data from MediaWiki, you need to verify that you can retrieve you data from PHP by using Unified ODBC.

1. Follow the steps in our PHP tutorial. This shows you how to retrieve SQL Server data from a PHP script by using Unified ODBC. Note that you won't be able to retrieve SQL Server data from MediaWiki until you have done this.

Step 2: Retrieve SQL Server data from MediaWiki

1. On your MediaWiki machine, download and install the External Data MediaWiki extension.

   Use git to do this. For example:

   $ cd /tmp
   $ git clone https://gerrit.wikimedia.org/r/p/mediawiki/extensions/ExternalData.git

2. Copy the PHP ExternalData subdirectory to the MediaWiki extensions directory:

   $ mv ExternalData /var/www/html/mediawiki-1.23.5/extensions

3. On your MediaWiki machine, download and install the OdbcDatabase MediaWiki extension:

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

4. Copy the OdbcDatabase subdirectory to the MediaWiki extensions directory:

   $ mv OdbcDatabase /var/www/html/mediawiki-1.23.5/extensions

5. In the MediaWiki settings file (settings.php), add a new database that uses this MediaWiki driver and specifies the SQL Server connection details:

   $ cd /var/www/html/mediawiki-1.23.5
   $ vi LocalSettings.php

6. Add these lines to the bottom of LocalSettings.php:

   require_once "$IP/extensions/OdbcDatabase/OdbcDatabase.php";
   include_once "$IP/extensions/ExternalData/ExternalData.php";
   $edgDBServerType  ['mydb'] = "odbc";
   $edgDBServer      ['mydb'] = "my_odbc_dsn";
   $edgDBName        ['mydb'] = "UNUSED";
   $edgDBUser        ['mydb'] = "my_sql_server_user";
   $edgDBPass        ['mydb'] = "my_sql_server_password";
   $edgDBTablePrefix ['mydb'] = "dbo";

   Replace the $edgDBServer, $edgDBUser, and $edgDBPass values with your SQL Server ODBC data source, user name, and password. (Use the same user name and password values that you specified in the data source. You need to specify these values again because the Unified ODBC extension is an ODBC 2 application that uses SQLConnect. A user name and password are mandatory attributes for SQLConnect.)

7. Open mediawiki_dir/extensions/OdbcDatabase/OdbcDatabase.body.php in a text editor.
8. Locate the following line in the fetchRow() function:

   } else if ( $this->mRownum <= $this->mAffectedRows ) {

   Change the less than or equal to condition to less than.

   } else if ( $this->mRownum < $this->mAffectedRows ) {

   This change means that fetchRow() only fails if it's called less times than the number of rows reported by the driver. $this->mAffectedRows is populated by the Unified ODBC call, odbc_num_rows. The SQL Server ODBC driver supports odbc_num_rows, and returns the number of rows in the result set to this call. Without this change, ODBCDatabase fails with an error if:

   • There are rows to return and the ODBC driver supports odbc_num_rows.
   • There are no rows to return.
9. If you're using MediaWiki 1.21 or later, locate the following line in OdbcDatabase.body.php:

   public static function getSoftwareLink() {

   Remove static from the function declaration:

   public function getSoftwareLink() {

10. Create a new MediaWiki page that uses the #get_db_data External Data function to retrieve data from your SQL Server database. This example retrieves data from the AdventureWorks database (and we therefore configured our SQL Server ODBC data source in odbc.ini to connect to this database by including the line Database = AdventureWorks):

    AdventureWorks data

    {{#get_db_data:
    db=mydb
    |from=Person.Contact as t
    |where=t.ContactID<=10
    |data=Title=t.Title, FirstName=t.FirstName, LastName=t.LastName, EmailAddress=t.EmailAddress, Phone=t.Phone}}
    {| class="wikitable"
    ! Title
    ! FirstName
    ! LastName
    ! EmailAddress
    ! Phone{{#for_external_table:<nowiki/>
    {{!}}-
    {{!}} {{{Title}}}
    {{!}} {{{FirstName}}}
    {{!}} {{{LastName}}}
    {{!}} {{{EmailAddress}}}
    {{!}} {{{Phone}}} }}
    |}

    The database ID you supply to #get_db_data must be the label you used to identify the SQL Server data source in LocalSettings.php, in the example it's mydb.

Further information

• ODBC on Linux and UNIX platforms
• 64-bit ODBC