XPE Tutorials - Tutorial 3 - Accessing an SQL database

Tutorial 3 - Accessing an SQL database

Description

This tutorial will show you how to access SQL databases from within an XPE pipeline. In doing so we will actually develop our first true XPE Web Service.

We will alter the previous pipeline by removing the hardcoded authentication test and instead authenticate by selecting records from a database table.

Step 1 - Prerequisites

Since we will need access to a database, we will need a JDBC accessible database installed.

If you already have a database installed - such as:

Oracle
MySql
HSQLDB

Then you can go straight to Step 2 - XPE SQL/Configuration. If not then you will need to download and install one. For these tutorials we recommend MYSQL. You may download MYSQL for free from: http://mysql.com. You will also need the mysql jdbc driver file that is compatible with the version of the mysql database you download. The driver (.jar) file is also available from the mysql site as a seperate download.

Once downloaded and installed, start the mysqld process and ensure that it is working correctly.

Step 2 - XPE SQL/Configuration

In order for XPE to connect and communicate with a database, the database drivers (JDBC) must be accessible to XPE, and the connection parameters (such as URL, username/password) must be specified. The jar file containing the JDBC drivers needs to be placed in the lib directory. For example if you are using MYSQL version ?, then place the file mysql-connector-java-3.1.12-bin.jar in the lib directory under your tutorial directory.

For XPE to recognise and communicate the the database server, the connection parameters need to be specified in a file called db.xml and placed in the xar/META-INF directory.

For a MYSQL database, this file should look something like:

<db:dbs xmlns:db="http://www.org.xml/pipe/xpe/dataSource" >
   <db:db>
      <db:dataSource name="tutorial" maxActive="3000" maxWait="20" >
         <db:connection driver="com.mysql.jdbc.Driver" url="jdbc:mysql://localhost:3306/test" username="root" password="root" />
      </db:dataSource>
   </db:db>
</db:dbs>

For an Oracle database, this file should look something like:

<db:dbs xmlns:db="http://www.org.xml/pipe/xpe/dataSource" >
   <db:db>
      <db:dataSource name="tutorial" maxActive="3000" maxWait="20" >
         <db:connection driver="oracle.jdbc.driver.OracleDriver" url="jdbc:oracle:thin:@localhost:1521:oradb" username="user" password="password" />
      </db:dataSource>
   </db:db>
</db:dbs>

Step 3 - Defining our new Service

Now lets begin by defining a URL mapping for our new database based authentication service, and creating the pipe definition.

Edit the urlPattern.xml file by adding a new URL mapping - /tutorial/tut3/dbauthenticate:

Next edit the xpipedef file and define the pipeline that provides this service.

Just copy and past the existing pipeline from tutorial 2 and change the references of authenticate to dbauthenticate and change the registered uri to tut3 (from tut2). You will also need to register the xnode of type "xslt/dbauthenticate" used in this pipeline. The xpipedef file should now include the following

Step 4 - Generating SQL

We now need to create the xsl file that generates our required sql to query our database. We have specified that this file be called dbSelect.xsl. Create this file in the xslt directory. This file will extract the username and password from the http request and format an sql query.

First of all copy the authenticate.xsl file from tutorial 2 and save it as dbSelect.xsl. Next add a new namespace definition at the begining of the file to define the XPE sql namespace: xmlns:sql="http://www.softtouchit.com/xpe/sql". Next remove the hardcoded test that matched the username to 'fred' and password to 'flintstone'. In its place add the xslt that will generate the sql.

The file should look something like:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
   <xsl:output method="xml" />
   <xsl:template match="http:httpRequest" >
      <xsl:choose>
         <xsl:when test="//http:params/http:param[@name='password'] and //http:params/http:param[@name='username']" >
            <xsl:variable name="username" >
               <xsl:value-of select="//http:params/http:param[@name='username']/@value" />
            </xsl:variable>
            <xsl:variable name="password" >
               <xsl:value-of select="//http:params/http:param[@name='password']/@value" />
            </xsl:variable>
            <sql:connect dataSource="tutorial" xmlns:sql="http://www.softtouchit.com/xpe/sql" >
               <sql:transaction>
                  <sql:query rowName="authentication" >
                     <sql:sql>
                        SELECT count(username) as recordCount from authentication where username='<xsl:value-of select="$username"/>' and password='<xsl:value-of select="$password"/>'
                     </sql:sql>
                  </sql:query>
               </sql:transaction>
            </sql:connect>
         </xsl:when>
         <xsl:otherwise>
            <result status="error" xmlns="" >
               <error>
                  username and password are required parameters.
               </error>
            </result>
         </xsl:otherwise>
      </xsl:choose>
   </xsl:template>
</xsl:stylesheet>

Note

As for tutorial1, please ensure that the stylesheet above declares the namespaces:

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:http="http://www.xml.org/pipe/HTTP"
xmlns:xpe="http://www.xml.org/pipe/xpe"
xmlns:sql="http://www.softtouchit.com/xpe/sql"

in the xsl:stylesheet element. The above is slightly incorrect due to a prementioned documentation formatting bug.

Note carefully that the datasource mentioned within the sql:connect element must match the the datasource specified in the db.xml file.

Before preceeding any further, lets build and deploy this application so that we can ensure we are on the right track. Assuming the deployment has given no errors, navigate to our service using the url: http://localhost:8188/xpe/tutorial/tut3/dbauthenticate?username=fred&password=flintstone

You should see returned some xml defining the required sql:

<sql:connect dataSource="tutorial" xmlns:sql="http://www.softtouchit.com/xpe/sql" >
   <sql:transaction>
      <sql:query rowName="authentication" >
         <sql:sql>
            SELECT count(username) as recordCount from authentication where username='fred' and password='flintstone'
         </sql:sql>
      </sql:query>
   </sql:transaction>
</sql:connect>

Step 5 - Accessing the database

The next step is to modify the pipline by adding the SQL filter that will communicate with our database. Simply alter the pipline definition:

Before deploying and testing the new service, we need to create an appropriate table in the database. Run the following sql against the database using your prefered sql client:

create table authentication (username varchar(256), password varchar(256));

Then populate the table with a sample user:

insert into authentication values('fred','flintstone');

We are now ready to deploy and test the service. You should by now be familiar with the process of deploying XPE services, if not refer back to tutorial 1. After deploying the application, you should be able to navigate to the url: http://localhost:8188/xpe/tutorial/tut3/dbauthenticate?username=fred&password=flintstone and get a response similar to the following (exact results may differ depending on the database being used):

depending on whether the username and password match the contents of the database table. Try different username/password combinations to assure yourself that the service is actually working as expected. If all seems as expected, then our Web Service is nearly complete. All we need to do now is create an XSL transformation that will interpret the output generated thus far and return a result similar to that of tutorial 2.

Step 5 - Final result

Our final xsl transformation will interpret the result of step 4 and generate a response similar to that of the previous tutorial. To do this create a new xsl transformation in the xslt directory called authenticateResult.xslsimilar to:

<xsl:stylesheet version="2.0" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" >
   <xsl:output method="xml" />
   <xsl:template match="ens:error" >
      <result xmlns="http://www.xmlpipe.org/xpe/manual" >
         <error>
            <xsl:copy-of select="//ens:error" xmlns:xsl="http://www.w3.org/1999/XSL/Transform" />
         </error>
      </result>
   </xsl:template>
   <xsl:template match="authentication" >
      <xsl:choose>
         <xsl:when test="./recordCount='1' or ./RECORDCOUNT='1'" >
            <result status="success" xmlns="http://www.xmlpipe.org/xpe/manual" />
         </xsl:when>
         <xsl:otherwise>
            <result status="error" xmlns="http://www.xmlpipe.org/xpe/manual" >
               <error>
                  Username not found or password is invalid.
               </error>
            </result>
         </xsl:otherwise>
      </xsl:choose>
   </xsl:template>
   <xsl:template match="result" >
      <xsl:copy-of select="." />
   </xsl:template>
</xsl:stylesheet>

Note

Again, please ensure that the stylesheet above declares the namespaces:

xmlns:xsl="http://www.w3.org/1999/XSL/Transform"
xmlns:http="http://www.xml.org/pipe/HTTP"
xmlns:xpe="http://www.xml.org/pipe/xpe"
xmlns:sql="http://www.softtouchit.com/xpe/sql"

in the xsl:stylesheet element. The above is slightly incorrect due to a prementioned documentation formatting bug.

This stylesheet does a number of things. First of all it checks if any exceptions where raised by XPE. This could happen for any number of reasons. For example if the database table does not exist or the sql is in invalid. If such an error is detected then the error will be formatted and returned in an error element.

If no error is detected, then a test is performed to see if the //authentication/recordCount element of the XML stream is equal to 0. If true then there are no matching records in the database for the specified username/password and the transformation generates an error result. Otherwise a successfull result is generated and returned in the http result.

Now edit the xpipedef.xml file to add a new xslt filter to the pipeline. Remember this is a two step process. First we register the new xslt file, and then we add the xslt xnode filter to the pipe definition.

The xpipedef.xml file should now look like:

Now build and deploy the application. Assuming there are no errors, navigate to the URL:

http://localhost:8188/xpe/tutorial/tut3/dbauthenticate?username=fred&password=flintstone .

You should get a response either like:

<result status="error" >
   <error>
      Username not found or password is invalid.
   </error>
</result>

If you entered an incorrect username or password, or:

If you entered the correct username and password, or

<result status="error" >
   <error>
      username and password are required parameters.
   </error>
</result>

if you enterered no username or password.

Summary

We have now completed our first truly usable XPE WEB Service. In the next tutorial we will expand the service further by showing how we can POST xml to the service instead of using a HTTP GET.

Any Problems?

If you have problems getting this tutorial to work look at the completed solution in the tutorials/solutions/tutorial3 directory It should have all files required to buld and run the tutorial.