Compiling PHP with MSSQL Server's Native ODBC Driver for Linux as a PDO Driver

Last month, MS announced the preview release of SQL Server ODBC Driver for Linux, a 64-bit binary driver for Red Hat Enterprise Linux 5. This is good news for companies using heterogenous platforms e.g. LAMP stack running a PHP application that connects to both MySQL and MSSQL Servers. Meaning, these companies no longer have to use third-party drivers such as FreeTDS that MS doesn't support. Then a few days ago, MS released version 1 of the driver. I immediately downloaded the driver and recompiled PHP with it as a PDO  (PHP Data Object) ODBC driver :-) 


Below are the steps on how to build PHP using the SQL Server ODBC Driver as a PDO driver:

Install the SQL Server ODBC Driver for Linux
* follow the instructions here

Recompile PHP
$> ./configure --with-pdo-odbc=unixODBC,/usr/local --with-unixODBC=/usr/local ...
$> make
$> make install


Configure ODBC
/usr/local/etc/odbcsys.ini
[SQL Server Native Client 11.0]
Description=Microsoft SQL Server ODBC Driver V1.0 for Linux
Driver=/opt/microsoft/sqlncli/lib64/libsqlncli-11.0.so.1720.0
UsageCount=1


/usr/local/etc/odbc.ini
[MSSQLServer]
Driver = SQL Server Native Client 11.0
Description = Sample Database
Trace   = Yes
Server  =
Port    = 1433
Database=

Test MSSQL Connection


mssqltest.php
<?php
    putenv('ODBCSYSINI=/usr/local/etc');
    putenv('ODBCINI=/usr/local/etc/odbc.ini');
    $username = "";
    $password = "";
    //ODBC
    $connection = odbc_connect("MSSQLServer", 
                               $username, 
                               $password
                              );
    $connection = odbc_connect("MSSQLServer", $username, $password);
    echo var_dump($connection);
   //PDO
   try {
     $dbh = new PDO("odbc:MSSQLServer",
                    "$username",
                    "$password"
                   );
   } catch (PDOException $exception) {
     echo $exception->getMessage();
     exit;
   }
   echo var_dump($dbh);
   unset($dbh);
?>


$> php ./mssqltest.php


Gotchas and Performance
Failed to get DB handle: SQLSTATE[IM002] SQLConnect: 0 [unixODBC][Driver Manager]Data source name not found, and no default driver specified

Solution: Review your ODBC and ENV Settings

Failed to get DB handle: SQLSTATE[28000] SQLConnect: 18456 [unixODBC][Microsoft][SQL Server Native Client 11.0][SQL Server]Login failed for user ''

Solution: Review your MSSQL Credentials

Failed to get DB handle: SQLSTATE[HYT00] SQLConnect: 0 [unixODBC][Microsoft][SQL Server Native Client 11.0]Login timeout expired

Solution: Enable TCP/IP on your MSSQL Server:

1. Run the command netstat -an to verify if there's a listener on TCP:1433
2. If there's none, run your SQL Server Configuration Manager, then go to SQL Server Network Configuration > Protocols, then set TCP/IP to Enabled
3. Restart your SQL Server

I'll keep you guys posted (better yet, keep me posted) about the performance improvement or if there are any gotchas on the driver's version 1.

Creating ANT Tasks Using Javascript

When we're tasked to add functionality to our ANT scripts, we usually check ant-contrib or extend Ant using Java or write scripts using Apache BSF or JSR 223 supported languages such as Ruby, Groovy, Jython, Beanshell, Judoscript and Javascript! 


Ant's Javascript-support can be useful for creating simple tasks that needs conditional and loop statements, and string, arithmetic, regex, etc. functions. Not to mention that you can also import Java classes within the javascript code. So, let's give it a try. 


Below is an annotated example on how to use javascript for creating Ant tasks:
<project name="MyProject" basedir=".">
  <target name="javascriptTarget">
    <script language="javascript">
      <![CDATA[
  importClass(java.io.File);


        //convert properties into javascript variables
  var hostname = MyProject.getProperty("hostname");


        //convert properties in dot notation into javascript variables
  var databaseTable = MyProject.getProperty("database.table");


        //set properties
        MyProject.setProperty("MyPropertyName", "some value");


       //create and run task
  echo = MyProject.createTask("echo");
  echo.setMessage("Using properties: " + databaseTable + ", " + MyPropertyName);
  echo.perform();


       //run other target
       project.executeTarget("typicalAntTarget");


       echo.setMessage("Done");
       echo.perform();

  ]]>
   </script>
  </target>

  <target name="typicalAntTarget">
    < echo message="Hello, ${MyPropertyName}" / > 
  </target>
</project>

Pre-requisites

The code will work out-of-the-box using Oracle's JDK distribution but if you're using OpenJDK, you need to install the following packages:
shell> yum install java java-1.6.0-openjdk-devel rhino bsf xml-commons-apis jakarta-commons-logging jsr223

WordCamp Seattle

I will be presenting at WordCamp Seattle next month and the title of my presentation is "Strategies for Using and Integrating WordPress CMS to PHP MVC Frameworks"

The event will be held on April 16, 2011 at Cascade Ballroom, Haggett Hall, University of Washington, Seatte, WA and is also posted at Seattle 2.0. See you guys there!