MSSQL(Microsoft SQL) Data Services In PHP

PHP in most percentage is used with MySQL Server in LAMP or WAMP stacks. But there may be situations where PHP uses MSSQL databases in back-end. And PHP scripts can be used to make Web services exposing MSSQL Databases. That is enabled by the  Data Services capability in WSF/PHP 2.0.0.

Here are the steps to get the PHP Data Services working with MSSQL in windows. You can safelty skip the starting point if you have already have the necessory software installed.

• Download Apache Web Server, PHP and MSSQL server. (You can download the MSSQL server 2005 expess edition from here.
• Enable the php_mssql, php_pdo and php_pdo_mssql extension from the php.ini.
• If you have MSSQL server 2005, the default client DLL (ntwdblib.dll) use to connect to MSSQL server will not work. So you need to download a newer version of this dll. You can easily find this googling the name of the dll. (The dll version worked for me is 2000.80.194.0). Copy this dll to following directories.
  • <your_windows_drive>/windows/system32
  • <your_apache_installation>/apache2/bin
• To Demo purpose I have created a MSSQL database called “RESTFulSchool” and and table called ‘Subjects” with the following schema. If you already have a MSSQL database you can just continue with that.
  

  Column Name	Data Type
  subjectID	int
  subjectName	text
  subjectTeacher	text

  And make sure you fill some data into the table. You can get this done easily with MSSQL Server Management Studio downloadable from http://www.microsoft.com/sql/editions/express/default.mspx

• So here is my code to the Data Service. Note in the config variable I have given the information about my MSSQL connection.

  <?php
  
  //Including the Data Services library
  require_once("wso2/DataServices/DataService.php");
  
  //Including the connection information for my MSSQL Connection
  require_once("constants.php");
  
  // database configurations
  $config = array(
  		"db" => "mssql",
  		"username" => MSSQL_USERNAME
  		"password" => MSSQL_PASSWORD
  		"dbname" => "RESTfulSchool",
  		"dbhost" => MSSSQL_SERVER_NAME
  		);
  
  $output_format = array(
                      "resultElement" => "subjects",
                      "rowElement" => "subject",
                      "elements" => array(
                              "name" => "subjectName",
                              "teacher" => "subjectTeacher"));
  
  $sql = "SELECT subjectName, subjectTeacher FROM Subjects";
  
  $get_subjects_op = array("outputFormat" => $output_format, "sql" => $sql);
  
  $get_subjects_url = array("HTTPMethod" => "GET", "RESTLocation" => "subjects");
  
  // list of operations
  $operations = array(
                  "getSubjects" => $get_subjects_op,
                  );
  
  // list of rest url mappping (operation => url)
  $restmap = array(
                  "getSubjects" => $get_subjects_url,
                  );
  
  // creating DSService and reply
  $service = new DataService(array("config" => $config, "operations" => $operations, "RESTMapping"=>$restmap));
  $service->reply();
  
  ?>

• You just wrote a SOAP+REST Data Service to expose your MSSQL Data as a Web Service. Now Put this script (say school_service.php) in to the Apache web directory and access it using a browser with the following url.

  http://localhost//school_service.php/subjects

  If you have done every thing right, you should see a nice xml showing your data. Note that here we used REST interface to test our service. You don’t need to do any thing special to expose it as SOAP. Its SOAP endpoint would be simply

  http://localhost//school_service.php

Get the WSDL Generation working with MSSQL
If you try to retrieve the WSDL for the above service using this URL

http://localhost//school_service.php?wsdl

You will get an error message saying that the meta data retrieval is not supported for this particular driver (MSSQL Driver). For the time being, in order to get rid of this warning you have to disable showing warnings in your PHP programs. You can get it done by editing the php.ini with this entry

error_reporting = E_ALL & ~E_NOTICE & ~E_WARNING

But even with this fix, the schema types of the name and teacher entries will be shown as “xsd:anyType”. If you want to have a more specific type associated with them, Just change the $output_format variable to the following.

$output_format = array(
                    "resultElement" => "subjects",
                    "rowElement" => "subject",
		    "elements" => array(
			"name" => array("column" => "subjectName", "xsdType" => "xsd:string"),
			"teacher" => array("column" => "subjectTeacher", "xsdType" => "xsd:string")));

Here instead of giving a string to ‘teacher’ and ‘name’ entries, we give an array containing both column name and the the custom xsd type we want to appear in the WSDL.  Note that this fix is needed only for databases that doesn’t provide the meta data interface through the PHP PDO layer. For database like mysql wsdl generation works straight away.