Use the SQL Server ODBC driver to connect Drupal to Microsoft SQL Server. You can then access data stored in SQL Server from Drupal 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.
These are the components that you need:
The Drupal driver for ODBC databases uses PHP's PDO_ODBC driver. Before attempting to retrieve your SQL Server data from Drupal, you need verify that you can retrieve you data from PHP by using PDO_ODBC.
Please refer to this Easysoft article for additional information.
You need to use git to obtain this driver:
$ cd /tmp $ git clone --branch master http://git.drupal.org/sandbox/pstewart/2010758.git drupal_odbc_driver
Note that at the time of writing, this web page for this driver states that prepared statements are not working. Looking at the driver's bug report page, this is related to PDO_ODBC's behaviour when the underlying driver does not support SQLDescribeParam. The SQL Server ODBC driver does support this ODBC API and we did not find any issues with prepared statements during testing.
$ cd drupal_odbc_driver $ mv odbc /var/www/html/includes/database
$ cd /var/www/html/sites/default $ vi settings.php
$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'mydb', 'username' => 'mydrupaladmin', 'password' => 'password', 'host' => 'mysite', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), );
$databases = array ( 'default' => array ( 'default' => array ( 'database' => 'mydb', 'username' => 'mydrupaladmin', 'password' => 'password', 'host' => 'mysite', 'port' => '', 'driver' => 'mysql', 'prefix' => '', ), ), 'external' => array ( 'default' => array ( 'odbc_driver' => '{Easysoft ODBC-SQL Server}', 'database' => 'mssqldatabase', 'username' => 'mssqluser', 'password' => 'password', 'host' => 'mssqlhost:mssqlport', 'port' => '', 'driver' => 'odbc', 'prefix' => '', ), ), );
Replace:
Create a Drupal module that uses and retrieves data from the new database:
cd
into the new directory and create two files named mssql.info and mssql.module:
$ cd /var/www/html/sites/all/modules/contrib $ mkdir mssql $ cd mssql $ touch mssql.info mssql.module
name = SQL Server Drupal ODBC Demo description = "Retrieve remote SQL Server data from Drupal on Linux and UNIX." core = 7.x files[] = mssql.module
<?php function mssql_menu(){ $items['mssql'] = array( 'page callback' => 'mssql_page', 'access arguments' => array('access content'), ); return $items; } function mssql_page(){ // Switch to the SQL Server database db_set_active('external'); // Retrieve and display AdventureWorks data $result = db_query('select Title, FirstName, LastName, EmailAddress, Phone from Person.Contact where ContactID <= :id', array(':id' => 10))->fetchAll(); $output = "<table>"; foreach($result as $item) { $output = $output . "<tr><td>" . $item->Title . "</td><td>" . $item->FirstName . "</td><td>" . $item->LastName . "</td><td>" . "</td><td>" . $item->EmailAddress . "</td><td>" . "</td><td>" . $item->Phone . "</td></tr>" ; } $output= $output . "</table>"; // Switch back to the default database. Otherwise Drupal will send SQL to SQL Server which the database // either will not understand or which relates to tables that will not exist in SQL Server db_set_active(); return $output; }