Guide | Dimuthu's Blog

October 4th, 2008MSSQL(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.

More »

DataServices, REST, Tutorial/Guide, WSDL, web services, wsf/php 2 Comments »

September 21st, 2008WSDL2PHP 2 Minutes Introduction

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.

To Provide Input Parameters
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;
    //--------------------

More »

2 minutes guide, Tutorial/Guide, WSDL, web services, wsf/php, wso2, xml schema 13 Comments »

September 3rd, 2008Axis2/C ADB 2 Minutes Introduction

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

Column Name	Data Type
subjectID	int
subjectName	text
subjectTeacher	text

Dimuthu’s Blog

October 4th, 2008MSSQL(Microsoft SQL) Data Services In PHP

September 21st, 2008WSDL2PHP 2 Minutes Introduction

September 3rd, 2008Axis2/C ADB 2 Minutes Introduction