XPE Tutorials - Tutorial 3 - Accessing an SQL database

• Getting started
  • Tutorial 1 - Basic XPE Pipeline
  • Tutorial 2 - Passing Parameters to Pipelines
  • Tutorial 3 - Accessing an SQL database
  • Tutorial 4 - POSTing XML to an XPE Pipeline
  • Tutorial 5 - Creating a simple form

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.

We will be creating the following XPE pipeline:

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: mysql. 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. (a version 'mysql-connector-java-3.1.12-bin.jar' is also included in the lib directory of the worked examples for tutorial 1.)

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, then place the file 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:

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

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.

In this tutorial we will create two xslt files to manipulate the XML stream. The first will take our HTTP request and extract the required parameters (username/password) and create a business instance element. The second xslt file will take the business instance data and create the SQL required to interogate the database. While the first xslt is not strictly required (since we can formulate the required sql from the http request xml), it is generally a good idea to formulate a business instance as the first stage of a pipeline. We will see some of the benefits of this approach in a later tutorial.

Lets register the first xslt transformation in the xpipedef.xml file:

Now lets create the xslt file (ROOT/tutorial/xslt/prepareRequest.xsl) that will be invoked by this pipeline:

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 xml similar to:

This shows that we have extracted the username/password from the HTTP parameters and created a data instance containing just the data relevent for the business problem we are trying to solve.

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 ROOT/tutorial/xslt directory. This file will extract the username and password from the authentication element and format an sql query.

The file should look something like:

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

The next step is to add this file to the pipeline in the xpipedef file, which should now look like:

Lets again 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:

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 to include the SQL filter:

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:

1. create table authentication (username varchar(256), password varchar(256));
2. 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 6 - 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.xsl similar to:

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 having a namespace of http://www.xml.org/xpipe/exception.

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:

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.