Dimuthu’s Blog

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.

WSDL2PHP makes the development of web service providers and consumers quick and easy. I wrote a 2 minutes guide on developing web services providers sometimes ago. So lets concentrate on developing web service consumers here.

Where is WSDL2PHP?

WSDL2PHP script is included in the WSF/PHP packs. You can find the wsdl2php.php script inside the ’scripts’ directory of any source or binary package. Or you can use the online wsd2php tool hosted in WSF/PHP web services DEMO Site.

How to Run the Script?

Here is the command

/scripts/wsdl2php.php mywsdl.wsdl > myclient.php

The Code is Generated. How Can I add My Code There?

It is simple. Just search for the comment “//TODO”.
Check for an example here.

You have to write custom code for 2 occasions per operation.

1. To Provide Input Parameters
2. To Handle output parameters.

An Example?

Here is the code snippet corresponding to the simpleAdd request for our demo WSDL.

    $input = new simpleAdd();
    //TODO: fill in the class fields of $input to match your business logic

    // call the operation
    $response = $proxy->simpleAdd($input);
    //TODO: Implement business logic to consume $response, which is of type simpleAddResponse

Here is how after I filled my logic in place of TODO comments.

    $input = new simpleAdd();
    //DONE: fill in the class fields of $input to match your business logic

    //-------my code----
    $input->param0 = 2;
    $input->param1 = 3;
    //------------------

    // call the operation
    $response = $proxy->simpleAdd($input);
    //DONE: Implement business logic to consume $response, which is of type simpleAddResponse

    //--------my code-----
    echo $response->return;
    //--------------------

Building and Traversing XML are regular tasks for any Web Service developer. We may use DOM, AXIOM or even simple String manipulation functions to do that. Normally this takes a lot of time and effort. And if we are coding in ‘C’ it become more tedious as string manipulation in ‘C’ is not that straight forward.

What ADB does is it generates a set of ‘C’ functions specially for our XML Schema to build and traverse the XML.
Say you have the following XML Schema. (You need to have it inside a WSDL to generate the code).

        <xs:schema targetNamespace="http://dimuthu.org/adb/demo/2008/sept">
            <!-- demonstrating element-->
            <xs:element name="myDemo">
                <xs:complexType>
                    <xs:sequence>
                        <xs:element name="demo1" type="xs:int"/>
                        <xs:element name="demo2" type="xs:string"/>
                    </xs:sequence>
                </xs:complexType>
            </xs:element>
        <xs:schema/>

For this particular element there will be one adb type (adb_myDemo_t), one source (adb_myDemo.c) and one header (adb_myDemo.h) are generated. Don’t ever look at the source file (unless you want to hack it) just go through the header, it will have all the function you need to manipulate your ADB.

It will have following set of functions,

To create and free the object

adb_myDemo_create
adb_myDemo_free


Getters and Setters to manipulate Data

adb_myDemo_get_demo1
adb_myDemo_set_demo1
adb_myDemo_get_demo2
adb_myDemo_set_demo2


Build and Parse XML

adb_myDemo_serialize
adb_myDemo_deserialize

First we will check how to build a simple XML using an ADB object.

    adb_myDemo_t *mydemo = adb_myDemo_create(env);
    adb_myDemo_set_demo1(mydemo, env, 3); /* some arbitrary value */
    adb_myDemo_set_demo2(mydemo, env, "some arbitrary string");
    axiom_t *xml = adb_myDemo_serialize(mydemo, env, NULL, NULL, AXIS2_TRUE, NULL, NULL);

So now we have the AXIOM representation of the XML which we just build using ADB.

<ns0:myDemo xmlns:ns0="http://dimuthu.org/adb/demo/2008/sept">
    <ns0:demo1>3</ns0:demo1>
    <ns0:demo2>some arbitrary string</ns0:demo2>
</ns0:myDemo>

Anyway ADB is easier when you want to navigate through an XML. (you can read data randomly as you preferred). Here we starting from the AXIOM representation of our XML.

      adb_myDemo_t *mydemo= adb_myDemo_create( env);
      adb_myDemo_deserialize(mydemo, env, &xml, NULL, AXIS2_FALSE))
      int demo1 = adb_myDemo_get_demo1(mydemo, env);
      printf("My Demo1 is: %d\\n", demo1);
      axis2_char_t *demo2 = adb_myDemo_get_demo2(mydemo, env);
      printf("My Demo2 is: %s\\n", demo2);

I have printed the Data contained in the XML with the help of ADB objects.

In ordre to download the Codegen tool with ADB you have to download the Axis2/Java package.

If you want to learn more about ADB, Just go through more Axis2/C Codegen / ADB resources which I have blogged few weeks ago.

If you have a shared hosting environment you will find it is not straight forward to install WSO2 WSF/PHP + PHP DataServices Library since you have very limited authority on your environment. Here in this post, I’m explaining the steps I followed in setting up my custom PHP, WSF/PHP Extension and DataServices library for http://ws.dimuthu.org (This is a shared hosting environment from dreamhost based on LAMP stack) which I’m using to expose my blog as a WebService.

Prerequisites:

1. You should be able to run PHP using mod_fastcgi (not using mod_php) in Apache. That allows you to configure your own php environment.
2. The server configurations managed by the hosting service should allow you to handle .php extension from your own  CGI executable (here it is php.cgi), If not, you can still try with setting some other extension (say .phq or .qhq, anything not .php) to be handled by your php-cgi. Anyway if your hosting service allow you to run CGI then most probably you have this permission.
3. You should have the access to the shell (using SSH) and should be able to use development libraries(libxml, libxsl, libopenssl) + build tools (make)
4. There can be some other restrictions that your hosting service has put on you, that I have not experienced with my hosting service. So please check your hosting service support and documentation for possibility of building your own PHP environment.

Install PHP:

I have extracted out most of the steps from dreamhost documentation on custom php.ini.

1. Download PHP and compile. You should at least enable –enable-fastcgi –enable-force-cgi-redirect in the configuration.

   ./configure --enable-fastcgi --enable-force-cgi-redirect --with-xsl --with-openssl --prefix=`pwd`/dist
   make
   make install

   Note: from here on, php source directory is referred as <php_source_directory>
   I have installed the php to <php_source_directory>/dist since we mostly don’t have permission to install it in to default location. (/usr/local). From here on I will be referring php installation directory as <php_install_dir>

2. create a directory called ‘cgi-bin’ to your web document root directory(<document_root_directory>) and copy the <php_source_directory>/dist/bin/php-cgi in to that. (before php version 5.2.1 there is no php-cgi generated, you can just copy the php to the cgi-bin directory). And rename the php-cgi to php.cgi.

   cp <php_install_direcory>/bin/php-cgi <document_root_directory>/cgi-bin/php.cgi

3. copy <php_source_direcory/php.ini-dist to the cgi-bin directory and rename it as php.ini, this can be used to configure your php environment.

   cp <php_source_directory/php.ini-dist <document_root_directory>/cgi-bin/php.ini

4. Create .htaccess file in the cgi-bin directory to allow the access to php-cgi and php.ini files.

   cat << EOF ><document_root_directory>/cgi-bin/.htaccess
   Options +ExecCGI -Indexes +FollowSymLinks
   <FilesMatch "php(.?)\\.(cgi|ini)$">
   Order Deny,Allow
   Deny from All
   Allow from env=REDIRECT_STATUS
   </FilesMatch>
   
   EOF

5. Create .htaccess file in the root directory redirect all the requests for .php extensions to go through our php-cgi.

   cat << EOF > <document_root_directory>/.htaccess
   AddHandler php-cgi .php
   Action php-cgi /cgi-bin/php.cgi
   EOF

   If your hosting service doesn’t allow to handle .php just rename the end of the first line to some other extension.

6. Check the necessary permissions levels in files.

   chmod 644 <document_root_directory>/.htaccess
   chmod 755 <document_root_directory>/cgi-bin
   chmod 644 <document_root_directory>/cgi-bin/.htaccess
   chmod 644 <document_root_directory>/cgi-bin/php.ini
   chmod 755 <document_root_directory>/cgi-bin/php.cgi

That’s it. Now your .php files should be run on your own php environment. Just to check whether it is working or not create a phpinfo() file.

cat <<EOF > phpinfo.php
<?php
phpinfo();
?>
EOF

And go to the http://yourdoman.com/phpinfo.php and verify that it is your custom php environment .

Installing WSF/PHP

1. Download WSF/PHP latest version and extract it to any of your preferred directory(<wsf_php_source_directory>).
2. Set the PATH environment variable to search for your custom php installation first.

export PATH=<php_install_dir>/bin:$PATH

3. From the same shell go to the <wsf_php_source_directory> and compile it.

   ./configure
   make
   make install

4. Now check where you have installed wsf/php. it is in fact the extension dir shown by the php-config command.

   php-config --extension-dir

   I will refer this directory as <php_extension_directory>

5. Open the <document_root_directory>/cgi-bin/php.ini an set the extension directory, enable wsf extension and set the include path to the wsf scripts directory.cat <<EOF >> <wsf_php_source_directory>/cgi-bin/php.ini

   cat <<EOF >> <wsf_php_source_directory>/cgi-bin/php.ini
   extension_dir=<php_extension_directory>
   extension=wsf.so
   include_path=".:<wsf_php_source_directory>/scripts"
   
   EOF

   If you found that you can only put relative directories in your php.ini (that happens when you don’t even have the read access to your root directory ‘/’), follow the workaround mentioned in here, http://phpwebservices.blogspot.com/2008/08/installing-wsfphp-in-third-party.html

That’s all. Just check the phpinfo (created earlier section) whether your configurations and the wsf extension are loaded correctly. In order to test the functionality copy the wsf/php samples to the web document root and access them from the browser. Go to each sample and verify they function as expected.

cp /samples <wsf_php_source_directory>/ -R

And go to http://yourdomain.com/samples and click on each samples. If this not working most probably your localhost to ip mapping is wrong, most probably this problem is there in your shared host. Just go to the samples directory and change the endpoint to the domain name in place of ‘localhost’ in each client and try it again.

Installing PHP DataServices:

1. Download and unpack PHP DataServices. <php_data_services_directory>
2. Update the include_path entry in php.ini to DataServices libraries directory as well. So the <wsf_php_source_directory>/cgi-bin/php.ini  file now has the following entry.

   include_path=".:<wsf_php_source_directory>/scripts:<php_data_services_directory>/lib"

That’ all for the DataService installation. Please read <php_data_services_directory>/README to run the samples. Anyway if you are sure you did followed these simple steps correctly, go on with your DataService  development. Specially if you have wordpress database, you may start straight away with the making wordpress a DataService.

With PHP DataServices it is just a matter of putting a little configuration php file to make your database available as a web service. I only needed few minutes to make a simple web service from my blog after figuring out my wordpress database structure, http://wpbits.wordpress.com/2007/08/08/a-look-inside-the-wordpress-database/. In this guide, I m exposing the title, date and the content of each of my blog for my service, But you can extend this more the way you prefer.

1. Download and install WSF/PHP 1.3.2 and PHP Data Services Library. If WSF/PHP 2.0 released by the time you are reading this, (it is to be released in this week), you only need to install WSF/PHP, since the DataServices library is packed with WSF/PHP from 2.0.
2. Drop the following file (Say WordpressService.php) in to any of your web server document directories.

   <?php
   
   // Make sure you put the DataService.php in your include path
   require_once("wso2/DataServices/DataService.php");
   
   // database configuraitons
   // you have to set your database configurations in here..
   // These entries can be copy and past from the wp-config.php in your wordpress installation
   
   $config = array(
   "db" => "mysql",
   "username"=>DB_USER,
   "password"=> DB_PASSWORD,
   "dbname"=>DB_NAME,
   "dbhost"=>DB_HOST);
   // output format, plese check the API from http://wso2.org/wiki/display/wsfphp/API+for+Data+Services+Revised
   $outputFormat = array("resultElement" => "Posts",
   "rowElement" => "post",
   "elements" => array( "title" => "post_title",
   "content" => "post_content",
   "date" => "post_date"));
   
   // sql statment to execute, note that I assume the table prefix is wp_ (so the table name is wp_posts)
   // just check $table_prefix variable in the wp-config.php of your wordpress installation
   $sql="select post_title, post_content, post_date from wp_posts where post_status='published'";
   
   // operations is consist of inputFormat (optional), outputFormat(required), sql(sql), input_mapping(optional)
   $operations = array("getPosts"=>array("outputFormat"=>$outputFormat, "sql"=>$sql));
   $my_data_service = new DataService(array("config"=>$config,"operations"=>$operations));
   
   $my_data_service->reply();
   ?>

3. It is all. Just access the above file from a web browser, you see your service is hosted. Here is the endpoint for my service. http://ws.dimuthu.org/blog/WordpressService.php. Since I m using WSF/PHP latest svn, I m able to retrieve the wsdl automatically from http://ws.dimuthu.org/blog/WordpressService.php?wsdl. Please wait for WSF/PHP 2.0 release for ?wsdl feature.
4. To verify whether your service deployed correctly, you may need to write a simple test client. Yea I too wrote one. Since I have the wsdl generated, I just needed to generate the code for the client from the wsdl using wsdl2php tool. There is an online version of the tool in the wsf/php demo site. Here is the generated code for my client, http://labs.wso2.org/wsf/php/wsdl2phptool.php?wsdl_url=http%3A%2F%2Fws.dimuthu.org%2Fblog%2FWordpressService.php%3Fwsdl. I added the following code to the TODO section in handling response,

       if(is_array($response->post)) {
           foreach($response->post as $post) {
   
               echo "<h2>".$post->title . " - ".$post->date."</h2>";
               echo "<p>";
               echo $post->content;
               echo "</p>";
   
               echo "<hr/>";
           }
       }

   Check my Web Service client for the above service here, http://ws.dimuthu.org/blog/WordpressClient.php.

Now I can call for my blog from any web service enabled platform.

We use the term ‘DataService’ when we expose a database as a web service. WSO2 has a separate product for the DataService space which was earlier shipped bundled with WSAS. There you write a configuration xml explaining what data to be exposed in your database and drop it in the WSAS java app server and you get the DataServices deployed without much effort. Couple of months ago we published PHP DataServices library that allow you do deploy Your DataService within a popular LAMP or WAMP stack. With the WSF/PHP 2.0.0 release, you can have the PHP DataService library bundled with the release, hopefully within next couple of weeks.

One of the main problem DataService developers face is how to design an elegant web service API that will provide the requested information from a row database straightaway. Because when we normalize a database, the very closely related data are spread in different tables just keeping the association with foreign keys. So in order to retrieve the useful information database developers have to write multiple queries to the database. Is that same with DataServices?, Do you have to write multiple web service requests to return some useful data?

No. You don’t. You have Nested Queries to save you. With nested queries you can configure your DataServices, so all the relevent information can be extracted and send as a single SOAP message. I will take a demonstration to explain this.

Say you are working for a company that manufacture or sell electrical equipments, say TVs. And your boss is asking you to prepare a whole list of customers who ordered good from them. He want order information (like order date, status of the order) plus the name of the customers in the list.

Here is the table structure that you have.
Orders

Customers

From this you have to find a list of orders with order date, status (available in Orders table) and ordered customer information available in Customers table. As you may seen you have to query both the tables to get the required data.

First you have to decide a service operation that you going to expose as a web service. Operation is uniquely identified by its name. We will give our operation the name “getOrders”. In fact in our PHP Data Service API we treat a Operation same as a query. In general operation/query has following properties associated with it. Note for the complete API please check this Oxygen Tank Page.
Properties of Data Service Operation/Query

<orders>
  <order>
    order 1 details
  </order>
  <order>
    next order details
  </order>

  <order>
    etc
  </order>
</orders>

array("Order_number" => "ORDERNUMBER",
      "Order_date" => "ORDERDATE",
      "Status" => "STATUS");

<order>
  <Order_number>
    3
  </Order_number>
  <Order_date>
    2008-06-09
  </Order_date>

  <Status>
    Shipped
  </Status>
</order>

getCustomer(customerId)

SELECT o.ORDERNUMBER,o.ORDERDATE, o.STATUS,o.CUSTOMERNUMBER FROM ds.Orders o

So as I described in the queries section you have to write the whole thing for the getCustomer operation as well.

$getCustomer = array(
    "inputFormat" => array("customerNumber" => "INTEGER"),
    "outputFormat" => array(

            "resultElement" => "Customers",
            "rowElement" => "Customer",
            "elements" => array("name" => "CUSTOMERNAME")
            ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?",
    "inputMapping" => array("CUSTOMERNUMBER" => "customerNumber")

    );

And here is the getOrders operation/query that actually use the getCustomer as a part of it.

$getOrders = array(
    "outputFormat" => array(
        "resultElement" => "Orders",
        "rowElement" => "Order",
        "elements" =>  array(

            "Order_number" => "ORDERNUMBER",
            "Order_date" => "ORDERDATE",
            "Status" => "STATUS"),
         "queries" =>  array($getCustomer)
         ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?"
    );

Then we will to define the operations array which we are going feed to the DataService class. Note that now we have two operations getOrders and getCustomer. From that I used getCustomer to help the building of getOrders. And I don’t want to publish getCustomer as a separate operation. Simply the boss doesn’t ask for that operation . So my operation map is like this,

$operations = array("getOrders" => $getOrders);

This operation map will be given as a constructor argument of DataService class. If you are familiar with WSF/PHP WSService class, there is no much different between these two classes. You can give all the options available in the WSService class to the DataService class. Say you want to encrypt the messages, just use the “policy” and the “securityToken” options. So with DataService class you have the luxury of using WS-Security as well as WS-Reliable Messaging for free.

To finish this post I will put the complete code for the above scenario. You can check the same service option in action in the WSF/PHP Demo site (Try the demo4).

<?php
// include the DataService library.
// NOTE: this path is valid only from wsf/php 2.0.0
require_once("wso2/DataServices/DataService.php");

// set the configurations
$config = array("db" => "mysql",
   "username" => "dimuthu",
   "password" => "thiswillnotbeasecretanymore",
   "dbname" => "ds",
   "dbhost" => "localhost");

// getCustomer Query - this used by the getOrders query/operation
$getCustomer = array(
    "inputFormat" => array("customerNumber" => "INTEGER"),
    "outputFormat" => array(

            "resultElement" => "Customers",
            "rowElement" => "Customer",
            "elements" => array("name" => "CUSTOMERNAME")
            ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?",
    "inputMapping" => array("CUSTOMERNUMBER" => "customerNumber")

    );

// The operation/query getOrders - Read the complete blog to see what 
// each of these option stand for
$getOrders = array(
    "outputFormat" => array(
        "resultElement" => "Orders",
        "rowElement" => "Order",
        "elements" =>  array(

            "Order_number" => "ORDERNUMBER",
            "Order_date" => "ORDERDATE",
            "Status" => "STATUS"),
         "queries" =>  array($getCustomer)
         ),
    "sql" => "select c.CUSTOMERNAME from ds.Customers c where c.CUSTOMERNUMBER = ?"
    );

// operation array
$operations = array("getOrders" => $getOrders);

// create the DataService object and reply
$ds = new DataService(array("operations" => $operations,
          "config" => $config,
          "serviceName" => NextedQuerySample));

$ds->reply();

?>