Leveraging pureXML in a Flex microblogging application, Part 1: Enabling Web services with DB2 pureXML

Set up the database for the sample microblogging app with IBM DB2 Express-C and IBM Data Studio

The pureXML capabilities of IBM DB2 allow you to store XML natively in a database without modification, while Adobe Flex applications can read XML directly and populate Flex user interfaces. In this three-part article series, you will create a microblogging application that takes advantage of pureXML, Web services, and Adobe Flex; and even allows you to publish your microblogging updates on Twitter.

24 Jan 2012 - As a followup to reader comments, the author made two revisions. In Prerequisites, you are advised to use the Full Client edition of Data Studio 3.1. to create Web Services. Following Listing 3, the DB2 9.7.5 changes namespace rules section briefly discusses changes to namespace rules in DB2 9.7.5 that require you to change the code in Listing 3.

Joe Lennon, Software developer, Freelance

Joe Lennon photoJoe Lennon is a 24-year-old software developer from Cork, Ireland. Joe is author of the forthcoming Apress book Beginning CouchDB, and has contributed several technical articles and tutorials to IBM developerWorks. In his spare time, Joe likes to play football (soccer), tinker with gadgets and work on his Xbox 360 gamer score.



24 January 2012 (First published 06 October 2009)

Also available in Russian Japanese Portuguese

About this series

Frequently used acronyms

  • API: Application program interface
  • DVD: Digital Versatile Disc or Digital Video Disc
  • HTML: Hypertext Markup Language
  • HTTP: Hypertext Transfer Protocol
  • IDE: Integrated Development Environment
  • JRE: Java™ Runtime Environment
  • MXML: Minimal XML
  • REST: REpresentational State Transfer
  • RSS: Really Simple Syndication
  • SDK: Software Development Kit
  • SQL: Structured Query Language
  • URL: Uniform Resource Locator
  • XML: Extensible Markup Language

Part 1 teaches you about Web services and how you can enable them using DB2 pureXML as you configure DB2® Express-C 9.7 on a Microsoft® Windows® system. In this article you will create a database, in particular, the database behind the microblogging sample application. Next, you will discover how to expose the data in the microblog database as a Web service using pureXML. Finally you will see how to use a Web browser to test that the Web service works.

In Part 2 you will learn about Adobe® Flex and how it thrives on data exposed as XML through Web services. You will install the Flex SDK in a Windows environment, and learn the basics of MXML and ActionScript, and how to compile them to form a Flex application. You will then see how to build the microblogging application user interface using Flex components, and how to test it with some sample XML data. Once you complete the user interface, you will then integrate it with the DB2 pureXML Web service created in Part 1.

Flex is a great method for creating Rich Internet Applications, but what if you want to present different views of your data? In Part 3 you will learn how to use your pureXML Web services to publish your microblogging entries to an HTML page. Syndication is an important aspect of any social media application, so you will also learn to publish your microblog entries as an RSS Feed. Finally, you will learn how to integrate your microblogging application with Twitter, allowing your updates to be published on Twitter and on your own application.

By the end of this series, you will have the tools and knowledge required to build a feature-rich microblogging application that you can easily expand or modify to suit you or your organization's requirements.


Prerequisites

You need to install the DB2 database server—the Express-C edition of DB2. For information on how to download and install DB2 Express-C, see Resources at the end of this article. You will also use IBM® Data Studio and WebSphere® Application Server Community Edition (hereafter referred to as Community Edition) to deploy the services on a Web server. Note: If you use Data Studio 3.1 to create Web Services, be sure to use the "Full Client" edition and not the "Administrator Client" or "Web Console" editions. See Resources for download information.


Introduction to DB2 pureXML

IBM DB2 is a relational database management system that runs on a wide range of devices and operating systems. It is available in several editions, from the entry-level Express-C database right up to the Enterprise Server Edition. In this article, you will use the Express-C edition, since it is simple to install and configure and it is available as a free download. DB2 Express-C is quite similar in concept to Oracle Database XE (Express Edition) and Microsoft SQL Server Express. Unlike its competition, however, DB2 Express-C does not include restrictions on the number of database users or the size of the database.

Although DB2 Express-C is the basic, production-level database in the DB2 product family, it does include support for pureXML—a feature of DB2 that allows you to store XML data natively in the database. The important aspect of pureXML is that it natively stores XML data in the database, rather than converting it or storing it as plain text. It does all of this in a transparent manner, so you do not need to worry about whether the working data is stored in traditional SQL data types or as XML. In addition, all XML data is protected by the same security and data integrity controls that protect the traditional relational data. This allows rapid development of applications that require either XML data, relational data, or a hybrid set of XML and relational data.


Introduction to DB2 Web services

Modern applications are often deployed using a service-oriented architecture (SOA) with Web services providing a standardized and platform/technology independent means of exposing business logic over the Internet or intranet. Web services are commonly made available using:

  • Simple Object Access Protocol (SOAP), an XML-based message broker,
  • Web Services Description Language (WSDL), a standardized XML interface description language
  • Universal Description Discovery and Integration (UDDI), a Web service directory framework

You can use IBM WebSphere Studio to create Web services that expose the data in a DB2 database. WebSphere Studio provides tools to create WSDL interfaces to DB2 data and to publish Web services directly to a UDDI registry. You can then deploy these Web services to the Web using IBM WebSphere Application Server. Fortunately, the DB2 Web service infrastructure takes care of all the Java Database Connectivity (JDBC) code required to interact with DB2 data.

The DB2 Web services Object Runtime Framework (WORF) enables automatic generation of WSDL interfaces for a set of database operations defined in a Document Access Definition Extension (DADx) file. This DADx file is deployed, along with the Apache Axis SOAP server, to a suitable application server, such as WebSphere Application Server. Once deployed, you can access the Web service using any client that can consume Web services. In Part 2 of this article series, you will use the Adobe Flex platform to consume some DB2 Web services.


Setting up the microblogging database

Once you complete the installation and the installer has closed, the DB2 First Steps application will launch. Before it opens, it might ask you to create a browser profile to enable JavaScript and access to local DB2 files. If you are asked to do so, press Create profile. The DB2 First Steps application looks like the screen capture in Figure 1.

Figure 1. DB2 First steps
Screen capture of First steps for DB2 Database for Linux, UNIX, and Windows

If you wish to set up the sample database, feel free to do so. You can use the "Create new database" button to launch a wizard for creating DB2 databases. You will not use this however, since you need to create an XML-enabled database, which cannot be created using the wizard. When you're finished with DB2 First Steps application, feel free to close it. Next you will create a DB2 database for your microblogging application.

To create a new DB2 database, you need to open the DB2 Command Editor. Navigate to Start>Programs>IBM DB2>DB2COPY1 (Default)>Command Line Tools>Command Editor and when it finishes loading, you should see the DB2 Command Editor, like the one in Figure 2.

Figure 2. DB2 Command Editor
Screen capture of DB2 Command Editor with editor and result areas

In the main editor area (the empty space below the toolbar with the Play and Stop buttons), enter the following statement and press Execute (the button with the play icon): create database mblog using codeset UTF-8 territory us.

This process can take a minute or so to complete, so be patient. When the database is created, you will see a "DB20000I The CREATE DATABASE command completed successfully" message in the result window. Now that you have created the database, tell the DB2 Command Editor to connect to it. Enter the following in the editor area and press Execute again: connect to mblog

If all goes well, you should get a message like "A JDBC connection to the target has succeeded," indicating that you are connected to the mblog database. Now, create a new table called status_updates, which will store the updates for the microblogging application in XML format. Enter the statement in Listing 1 into the editor window and press Execute.

Listing 1. Creating the status_updates table
create table status_updates (
    data        xml
)

You should receive a positive response that the SQL command completed successfully, as in Figure 3.

Figure 3. SQL command completed successfully
Screen capture of result for successfully completed SQL command

Now try to insert some XML data into the database. Enter the INSERT statement in Listing 2 into the editor window in the DB2 Command Editor.

Listing 2. Inserting an XML status update
insert into status_updates values (
    '<update display="true">
        <date_created>2009-08-06T21:20:19</date_created>
        <text>Hello World!</text>
    </update>'
)

Now press Execute, and once again you should get a response that tells you the command completed successfully.

One of the problems with not entering this data in regular RDBMS columns is that no rules are defined—no data types, NOT NULL identifiers and so forth. You can just as easily insert an XML document that has no relevance whatsoever and it would be accepted. This is where an XML schema comes into play. You can use an XML schema to define an acceptable format for the XML data. You can then validate the data against this schema when you perform an insert statement.

To create an XML schema, simply open a plain text editor (like Notepad) and create a new file. The save location is important, and for the sake of simplicity, save the schema in the folder C:\xmlschemas\updates.xsd. If you choose a different location, be sure to reflect when registering your schema in a few moments. Enter the code from Listing 3 into your file and save it.

Listing 3. The XML schema - updates.xsd
<?xml version="1.0" encoding="UTF-8"?>
<xs:schema xmlns:xs="http://www.w3.org/2001/XMLSchema">
  <xs:element name="update">
    <xs:complexType>
      <xs:sequence>
        <xs:element name="date_created" type="dateTime"/>
        <xs:element name="text" type="normalizedString"/>
      </xs:sequence>
      <xs:attribute name="display" type="xs:boolean" default="true"/>
    </xs:complexType>
  </xs:element>
</xs:schema>

The schema in Listing 3 defines an element, update, which is a composite element, comprised of two sub-elements:

  • date_created - A dateTime element
  • text - A normalizedString element

A normalized string simply tells the XML processor to remove any unnecessary extra white space surrounding the string when processing the data.

DB2 9.7.5 changes namespace rules

To satisfy changes to namespace rules in DB2 9.7.5, you must add a prefix when you define XML schemas. The xs: prefix is required for the type attribute on the <xs:element> nodes in the XSD schema in DB2 9.7.5.

The revised <xs:element>s are:

 <xs:element name="date_created"type="xs:dateTime"/> 
 <xs:element name="text" type="xs:normalizedString"/>

If you use the code in Listing 3 in DB2 9.7.5, you will get the SQL State code 2200M.

Now that you have the XML schema stored in a file, you need to register the schema with DB2 so it knows how to validate the XML data when inserting the data into the database. In the editor area of the DB2 command editor, enter the statement in Listing 4.

Listing 4. Register the XML schema with DB2
register xmlschema 'http://mblog.example.com' from 
'C:/xmlschemas/updates.xsd' as mblog.status_update complete

If the XML schema is registered correctly, you will receive a response: "DB20000I The REGISTER XMLSCHEMA command completed successfully", as in Figure 4.

Figure 4. XML schema registered successfully
Screen capture of result for successfully registered XML schema

It is worth mentioning at this point that just because you define an XML schema and register it in the database does not mean that any XML data inserted is automatically validated against this schema. In other words, you need to manually validate the XML when you perform an INSERT command. Do this with the XMLVALIDATE function as in Listing 5.

Note: Alternatively you can create a database trigger to capture every INSERT on your table and tell it to validate the data as XML using the XMLVALIDATE function before performing the INSERT. For this article, you will just stick with validating it in your own INSERT statements.

Listing 5. Validate XML data against the XML schema
insert into status_updates values (
    xmlvalidate(xmlparse(document('
    <update display="true">
        <date_created>2009-08-09T21:09:10</date_created>
        <text>This should validate!</text>
    </update>
    ')) according to xmlschema id mblog.status_update)
)

Because the XML in Listing 5 is valid according to your schema, you should receive a response that the SQL command completed successfully. Now see what happens if you try to insert some invalid data. In Listing 5, change the text between the <date_created> and </date_created> tags to This is not a valid datetime. Now try to execute the statement again. This time around, you should get an error message "SQL16105N Incorrect XML data". DB2 then provides some useful information about the error and a suggested path for correcting the issue—in this case, "Correct the XML document and try the operation again." Now that you have validated XML data in your database, look at how you can query that data.

You can query XML data stored in a DB2 9 database using two languages: SQL (SQL/XML) and XQuery. You can even use hybrid queries that combine SQL and XQuery expressions. First, perform an SQL query to get back the contents of your status_updates table. In the DB2 Command Editor, enter the following SQL statement: select data from status_updates.

When you press Execute, Command Editor will switch to the Query Results tab view and you will see a tabular structure, with the column Data and a field with the text XML and a button with three dots. This button is highlighted in the box in Figure 5.

Figure 5. DB2 Command Editor Query Results View
Screen capture of the DB2 Command Editor Query Results View

Click on this button with the three dots to bring up the XML Document Viewer, as in Figure 6. In the Tree View tab, the XML data is presented in a tree structure, and you should be able to see the attribute display, and the two sub-elements date_created and text, which are expanded to reveal their values.

Figure 6. DB2 XML document viewer
Screen capture of the DB2 XML document viewer displaying values for date_created and text elements

The main issue with using SQL statements to retrieve XML data is that you cannot use filter clauses like the WHERE clause to filter the XML data. You also cannot perform aggregation or joins on this data using SQL. You can however, use SQL with XML extensions (SQL/XML) to perform these types of queries.

As you might expect, SQL/XML is intended as a means of extending the SQL query language to work with XML data in ways that traditional SQL expressions cannot. For example, you can use SQL/XML to search your table for rows where a particular XML element stored in an XML column has a particular value. For example, suppose you want to perform a count of the rows where the XML data column has an element text with the value This should validate!. If this were stored in a typical VARCHAR column in your DB2 table, you might use the following SQL statement in Listing 6.

Listing 6. SQL statement
select count(*) from status_updates
where text = 'This should validate!'

But because you store the text data in an XML document stored in an XML column, you cannot use this statement. Instead, you can use the SQL/XML statement in Listing 7.

Listing 7. Using SQL/XML to filter XML data
select count(*) from status_updates
where xmlexists('$u/update[text="This should validate!"]'
passing status_updates.data as "u")

Running this should return a result of 1 in the Query Results window, as in Figure 7.

Figure 7. Result of the SQL/XML statement
Screen capture of result for the SQL/XML statement

If you want to perform a simple test on this query, try changing the text to something incorrect and execute the query again. This time around you should get a result of zero. Now use SQL/XML to return the elements of your XML documents in the same way as a traditional SQL table might be represented. To do this, use the XMLTABLE function. Enter the statement in Listing 8 into the Command Editor window.

Listing 8. Using XMLTABLE to format SQL/XML queries
select t.date_created, t.text from status_updates su, 
xmltable('$u/update' passing su.data as "u"
columns date_created varchar(100) path 'date_created',
  text varchar(500) path 'text') as t

Executing this query should return a result like the one in Figure 8. On the Query Results tab, you see the values for the DATE_CREATED and TEXT elements.

Figure 8. Result of XMLTABLE query
Screen capture of the result of XMLTABLE query with values for DATE_CREATED and TEXT elements

Finally, look at how you might use XQuery to retrieve data from your table. Again, in the command editor window, enter the following statement: xquery db2-fn:xmlcolumn('STATUS_UPDATES.DATA').

It's important the table and column are in uppercase, or the query will not work. Press Execute to return the query result in the command results area, as in Figure 9.

Figure 9. Result of XQuery statement
Screen capture of the result of the XQuery statement for date_created and text elements

Before I wrap up this section, use XQuery to filter your XML data and to return a specific element only. In this case, you will return just the text element, and this will be filtered by the date_created element (see Listing 9).

Listing 9. Filtering XML using XQuery
xquery
for $y in db2-fn:xmlcolumn('STATUS_UPDATES.DATA')/update
where $y/date_created="2009-08-09T21:09:10"
return $y/text

You can see the expected result of this query in Figure 10.

Figure 10. Result of filtering XQuery statement
Screen capture of result of filtering XQuery statement for text elements

So, now that you have your microblogging database set up, and you have gained some experience inserting and retrieving XML data, you can move forward and create some Web services that work with this data.


Exposing the microblogging data using Web services

To create your Web services, you will use IBM Data Studio, and the WebSphere Application Server Community Edition to deploy these services on a Web server. First, install the Community Edition. Go to http://www.ibm.com/developerworks/downloads/ws/wasce/ and click Download now to download the setup archive. You will need to sign in with your IBM ID to download the package. Download the correct version for your operating system (I installed this on Windows for the purposes of this article).

When the installer loads, press Next to move to the License Agreement screen. Read the license and select I accept the terms of the License Agreement and press Next to continue. Accept the default install directory and press Next. Review the summary of the installation and click Install to commence the installation. When the setup is complete, press Done to exit the installer. At this point, that's all you need to do with the Community Edition. Data Studio will take care of launching the Application Server for you later when you deploy your Web services.

Now that the Community Edition is installed, install Data Studio (see Resources for a download link. Download the IDE package as highlighted in Figure 11. This package includes all the features required to create and deploy Web services.

Figure 11. Download Data Studio IDE package
Screen capture of URL to Download Data Studio IDE package

To access the Downloads area, you will need to log in with your IBM ID. On the download page, select the relevant offering for your operating system (Windows was used in this article) and click Continue. You might be asked for some details describing how you intend to use the software. Once you complete those details, on the next screen check the box next to IBM Data Studio (IDE) Install and press the Download Now button to begin the download process. The file is 687 megabytes, so you will need a high-speed Internet connection (or a lot of patience!).

When the download completes, extract the archive and run the setup.exe file to launch the Data Studio installer. From the splash screen, select the setup language and press OK to enter the Launchpad application.

On the next screen, feel free to browse through the information provided in each of the sections on the left hand side, and when you are ready, click Install Product to run the Installation Manager. On the Install Packages screen, accept the default selections and press Next to continue (see Figure 12).

Figure 12. IBM Installation Manager - Install Packages
Screen capture of IBM Installation Manager - Install Packages

You will now be asked to read the License Agreements. When you are ready to continue, select the option I accept the terms in the license agreements and click Next. On the Location screen, accept the default options and press Next to move on. On the screen that follows, accept the default (Create a new package group) option and installation directory and click Next. You will then be given the opportunity to choose to extend an existing Eclipse IDE installation if one is available. Leave the options as they are and press Next.

In the Features section, you will first be asked for the Translations you wish to install. English will be selected by default and cannot be unselected. If you want to install additional languages, feel free to check the ones you want here. Otherwise, just click Next. Now you will be given the option to select which features to install. Again, leave this as is and press Next to continue. At last, you have reached the Installation Summary screen in Figure 13. You can review your selections here and go back and change any items if required. When you are ready, press Install to start the installation procedure.

Figure 13. IBM Installation Manager - Summary
Screen capture of IBM Installation Manager - Summary with selected packages

When the installation is complete, you will see a screen, like the one in Figure 14, that indicates a successful installation. Press Finish to start IBM Data Studio.

Figure 14. IBM Installation Manager - Installation Complete
Screen capture of IBM Installation Manager - Installation Complete

When Data Studio launches, it might ask you to select a workspace. Select the option Use this as the default and do not ask again and press OK (see Figure 15).

Figure 15. IBM Data Studio - Workspace Launcher
Screen capture of IBM Data Studio - Workspace Launcher

The first screen you will see in Data Studio is the Welcome screen. Click on the arrow icon in the top center of this screen (highlighted in Figure 16) to close this screen and enter the main development area.

Figure 16. IBM Data Studio - Welcome Screen
Screen capture of IBM Data Studio - Welcome Screen

In the bottom left area of the main Data Studio screen, you will see the Data Source Explorer, which is currently showing the available Database Connections. If you followed this article from the beginning, the MBLOG database should appear here. Right-click on the MBLOG connection to bring up its context menu, and select Connect. This will bring up a new window, with the database connection properties, and you will be asked to enter a user name. Enter your database username and password here (the one you created the status_updates table). Select the check box Save password and press Test Connection. If the connection is working correctly, you should see a message "Ping succeeded!" as in Figure 17.

Figure 17. Configuring your database connection
Screen capture of the Driver Properties window afte you configure and test the database connection

Press OK to close the Properties window. Data Studio will now initiate a connection to the MBLOG database and it should expand the database connection with two new options: Instance and MBLOG. Click the expand icon to the left of the MBLOG option to show the different object types in the database (Buffer Pools, Groups, and so on). Now that you have established a connection to your database, you need to create a new development project.

Select File>New>Data Development Project to open the New Project dialog window. You will be asked for a Project name, enter Microblog and press Next. On the next screen, you will be asked to select an existing database connection or create a new one. Select the MBLOG connection and press Finish to close the dialog (see Figure 18).

Figure 18. Select database connection
Screen capture of the Select connection window with MBLOB selected

You will now be returned to the main Data Studio workspace. In the top left, you will notice that Microblog is now an option in the Data Project Explorer pane. Click the expand icon to the left of this option to show the different parts of the project (PL/SQL packages, SQL Scripts, and so on).

Now you will create an SQL script that will simply query the status_updates table and return the XML data stored in each row's data column. From the options that you just expanded, right-click the SQL Scripts option, and select New>SQL or XQuery Script. This will open a dialog window, asking for the name of the script and how you wish to edit it. Enter getAllUpdates as the name and press Finish.

The file getAllUpdates.sql will be created and opened in the main editor window. Type the following statement in this window: select data from "MBLOG".status_updates

Select File -> Save to save the file. If you want to test the SQL statement, press F5 to execute the script. The results will show in the SQL Results tab at the bottom of the screen.

Next, create a stored procedure which you will use to insert a new row in your database table. In the Data Project Explorer pane in the top left of the window, right-click Stored Procedures beneath the Microblog project. Select New>Stored Procedure to open the dialog window for creating a new Stored Procedure. Give it a Name of insert_update and select PL/SQL from the Language drop-down and press Finish (see Figure 19).

Figure 19. New stored procedure
Screen capture showing new stored procedure, insert_update

On the Parameters screen, click the Add button to add a new parameter. From the pop-up window, leave the Parameter mode as In, and give it a name text. It should have the PL/SQL type VARCHAR with a length of 500. Press Add to add the parameter to the procedure.

The INSERT_UPDATE procedure will be saved in the Stored Procedures section and will be open in the Editor window. Modify this code so that it is the same as the code in Listing 10. Make sure to replace MBLOG with the schema where your status_updates table is stored.

Listing 10. INSERT_UPDATE stored procedure code
CREATE OR REPLACE PROCEDURE INSERT_UPDATE(IN c_text VARCHAR(500), IN display VARCHAR(5))
LANGUAGE SQL
BEGIN
    INSERT INTO "MBLOG".status_updates(data) VALUES(
    XMLDOCUMENT(XMLELEMENT(
        name "update",
        XMLATTRIBUTES(display as "display"),
        XMLFOREST(
        replace(varchar_format(current timestamp, 'YYYY-MM-DD HH24:MI:SS'), 
' ', 'T') as "date_created",
        text as "text"
)
    ))
    );
    RETURN 1;
END

Now you need to deploy your stored procedure to the database so you can test it. Right-click the INSERT_PROCEDURE option in the Data Project Explorer pane and select Deploy. From the Target database, select Use current database and ensure the correct Target schema is selected. Press Finish to compile the procedure. In Data Source Explorer, navigate down through your schema to find Stored Procedures and the INSERT_UPDATE procedure that you just compiled. Right-click the procedure and click Run. This will open the Specify Parameter Values dialog box. In the c_text parameter enter the value Testing procedure and in the c_display parameter enter true. Press OK to run the procedure. If it goes according to plan, you should see a "Run completed" message. Now switch back to your getAllUpdates SQL script and press F5 once more. This time around you should see the newly added row that you just created using your stored procedure.

You are now ready to create some Web services. In Data Project Explorer, right-click Web Services and select New Web Service. This will open the New Web Service dialog box in Figure 20. Make sure the Microblog project is selected and enter StatusUpdates as the name, and change the Namespace URI to urn:updates.

Figure 20. New Web service
Screen capture of of the Edit Web Service window with a new Web service

Now you will add your SQL script, getAllUpdates.sql, to your Web service. From Data Project Explorer, select the SQL script getAllUpdates.sql and drop it into the StatusUpdates Web service. This will create a getAllUpdates operation within the StatusUpdates Web service. Next, grab the Stored Procedure INSERT_UPDATE and drop that to the same place. You should now have two operations in your StatusUpdates Web service: getAllUpdates and INSERT_UPDATE. (see Figure 21)

Figure 21. StatusUpdates Web Service operations
Screen capture of StatusUpdates Web Service operations

Your next step is to create a Web server instance on which you can deploy the newly created Web service. In Data Studio, select Window>Show View>Other and from the Show View dialog box, select Server and then Servers. Now press OK, and a Servers tab will open on your workspace (see Figure 22).

Figure 22. Show servers view
Screen capture of Show view for servers

The Servers tab should now be active at the bottom of the Data Studio window. From here, right-click in the empty white area and select New>Server to open the New Server wizard. In the first screen of this wizard, accept the default options as listed in Figure 23.

Figure 23. Define a New Server window
Screen capture of Define a New Server window with defalult options selected

The default options for the server are:

  • host name: localhost
  • server type: IBM WASCE v2.1 Server
  • name: IBM WASCE v2.1 Server at localhost

Press Next. In the New Server - New IBM WASCE v2.1 Runtime window, you will be asked to select the location of your JRE installation and your WASCE installation. Select the appropriate values here and press Next (see Figure 24).

Figure 24. Select JRE and WASCE
Screen capture of selected locations for JRE and WASCE

The selected values are:

  • JRE: jdk
  • Application Server installation Directory: C:\Program Files\IBM\WebSphere\AppServerCommunityEdition

For the remaining screens in the New Server dialog, just accept the defaults and press Next. When you reach the Add and Remove Projects screen, press Finish. Now test that the Web server is valid by starting it. Right-click the instance that you just added to the Servers tab and choose the Start option. This might take a couple of minutes so be patient. When it has completed, the Console tab will close. On the Servers tab, you will see the Server has a state of "Started" as in Figure 25.

Figure 25. Web server started
Screen capture of the Server tab with the Web server started

Now that your Web service is defined and a Web server instance is up and running, you can deploy the Web service to the Web server. From Data Project Explorer, right-click the StatusUpdates Web service and select Build and deploy. This will open the Deploy Web Service wizard. In this screen, select the Server radio button and ensure that the v2.1 Server at localhost is selected in the drop-down list. Ensure that Register database connection with Web server is checked, and that the REST and SOAP over HTTP message protocols are checked also. When you are ready, make sure your selections match up with those shown in Figure 26 and press the Finish button to deploy the Web service.

Figure 26. Deploy Web Service dialog
Screen capture of Deploy Web Service dialog

The selected deployment values are:

  • Web server, Type: WebSphere Application Server Community Edition, version 2 (all releases)
  • Web server, Server: IBM WASCE v2.1 Server at localhost
  • Data handler, Data handler: JDBC
  • Data handler, Register database connection with Web server: selected
  • Message protocols, REST (Web access): selected
  • Message protocols, SOAP over HTTP: selected
  • Parameters, Name: artifact.contextRoot and Value: MicroblogStatusUpdates
  • Parameters, Name: artifact.soapEngine and Value: APACHE_AXIS_1_4
  • Parameters, Name: artifact.soapEngineDirectory and Value: <Use .jar files installed on Web server>

Congratulations, if you don't get any error messages, your Web service has been successfully deployed! In the next section you will make sure that it is working as expected. If you have problems deploying the Web service, make sure that you use the correct schema names in your SQL script and Stored Procedure—these are the most common causes of a deployment failure.


Testing the microblog Web service

Now that your Web service is deployed, test that it works. Data Studio includes a built in utility for testing Web services, but in this section you will use the Web browser to verify that the Web services work. The main reason for this is that, in Part 2 of this series, you will connect to these Web services from an Adobe Flex application, so you want to be absolutely sure that you are able to run these APIs from outside of Data Studio.

I find that Firefox works best for displaying XML data, so try to use that if possible. Other browsers might render the data a bit strangely or make you download them to your disk first. With your Web service deployed, navigate to the following URL: http://localhost:8080/MicroblogStatusUpdates/rest/StatusUpdates/getAllUpdates

If your Web service works correctly, you should get a response like the one in Figure 27.

Figure 27. Response from the getAllUpdates method of your Web service
Screen capture of response from the getAllUpdates method of your Web service

Now try your other method to insert some data. Enter the following URL: http://localhost:8080/MicroblogStatusUpdates/rest/StatusUpdates/insertUpdate?C_TEXT=It%20Works!&C_DISPLAY=true to return a response like the one in Figure 28.

Figure 28. Response from the insertUpdate method of your Web service
Screen capture of response from the insertUpdate method of your Web service

Now check that the row was actually inserted by navigating to the URL for the getAllUpdates method once more. At the bottom of the response you should see an element for the row you just entered. If all is working correctly, that's it. You have created a fully working Web service.


Summary

In this, the first article of the series, you learned about IBM DB2 pureXML and Web services. You installed DB2 Express-C and created a database for the sample microblog application. You learned how to interact with XML data in the DB2 database using SQL, SQL/XML and XQuery statements. Next, you installed the Community Edition and IBM Data Studio. Then you created an SQL script and Stored Procedure to access the status_updates data and add new data to it. You then learned how to expose these as methods in a Web service and how to deploy it to a Community Edition (WASCE) server. Finally, you saw how to test these methods using RESTful HTTP GET requests in your Web browser.

In Part 2, you will build on what you have learned. With Adobe Flex, you'll create an attractive user interface front-end that employs the Web service created in this article. You will learn everything you need to get a Flex application up and running and pulling the data in from the Web service, right from installing the Flex SDK to developing the user interface and back-end code.

Resources

Learn

Get products and technologies

  • DB2 Express-C: Get a free version of the IBM DB2 database server, an excellent foundation for application development for small and medium business.
  • WebSphere Application Server Community Edition (the Community Edition): Download this lightweight Java EE application server built on Apache Geronimo technology.
  • Get IBM Data Studio: Get the IDE package with the basic set of required tools for database development and administration for IBM Data Server used in this article.
  • IBM product evaluation versions: Download and get your hands on application development tools and middleware products from DB2®, Lotus®, Rational®, Tivoli®, and WebSphere®.

Discuss

Comments

developerWorks: Sign in

Required fields are indicated with an asterisk (*).


Need an IBM ID?
Forgot your IBM ID?


Forgot your password?
Change your password

By clicking Submit, you agree to the developerWorks terms of use.

 


The first time you sign into developerWorks, a profile is created for you. Information in your profile (your name, country/region, and company name) is displayed to the public and will accompany any content you post, unless you opt to hide your company name. You may update your IBM account at any time.

All information submitted is secure.

Choose your display name



The first time you sign in to developerWorks, a profile is created for you, so you need to choose a display name. Your display name accompanies the content you post on developerWorks.

Please choose a display name between 3-31 characters. Your display name must be unique in the developerWorks community and should not be your email address for privacy reasons.

Required fields are indicated with an asterisk (*).

(Must be between 3 – 31 characters.)

By clicking Submit, you agree to the developerWorks terms of use.

 


All information submitted is secure.

Dig deeper into XML on developerWorks


static.content.url=http://www.ibm.com/developerworks/js/artrating/
SITE_ID=1
Zone=XML, Information Management, Open source
ArticleID=432902
ArticleTitle=Leveraging pureXML in a Flex microblogging application, Part 1: Enabling Web services with DB2 pureXML
publish-date=01242012