Since I'm in the middle of moving to a new city and need to get organized, I thought I'd roll a little application using SQL Anywhere, PowerBuilder, and Amazon Web Services to help organize my media collection. In doing so, I'm going to check out some cool features in SQL Anywhere, in particular, consuming Web Services.
Calling Web Services out of your favorite development tool is nothing new. But who knew you could call them out of your favorite database? This opens up a whole world of programming fun. You might be wondering why you'd want to do this. Aside from the obvious - you'd like to access pre-canned code over the Internet - there's a clear advantage to storing your business logic server-side. With that said, let's get started.
To complete this project, you'll need a few things:
- SQL Anywhere 10.0.1 or higher. This is a free developer edition: http://eshop.sybase.com/eshop/try_buy.
- PowerBuilder version 10 or higher. This is free to try: http://eshop.sybase.com/eshop/try_buy
- Amazon Web Services access key (free; instructions are in the article)
- An Internet connection
- Some time to learn!
Creating a Database
The first thing you need to do is create a database. This can be done in SQL Anywhere by opening Sybase Central and going to the Tools->SQL Anywhere 10->Create Database menu item. The Create Database wizard will open. Follow the prompts, accepting all the defaults. Name your database whatever you like. After you've finished, Sybase Central will automatically connect to your new database. You can see this by noticing an orange lightning bolt in the bottom right-hand corner of your system tray. If you move your mouse over it, you'll notice a tool tip with the same name you gave your database.
Creating Table Schema
Now that we have a database to work with, we need to add a table. To do that, use the Create Table wizard found on the left-hand pane under "Database Design Tasks." Again, as you go through each step, accept all the defaults. When you're through, you'll be presented with a screen where you can enter the names and types for each of the columns you want to add. Enter the following information: (Table 1)
When you're finished, your schema should look like Figure 1. Save it by pressing CTRL-S.
Creating an ODBC Connection
To connect to our database outside of Sybase Central we're going to need an ODBC connection. You can create one by going to Start->Programs->SQL Anywhere 10->ODBC Administrator. See Figure 2. Click on Add and choose SQL Anywhere 10 as your driver. Click Finish. Name the data source "Amazon" and set the login name and password to "DBA" and "sql" respectively. (These are the default login credentials for a SQL Anywhere 10.0.1 database.) Finally, tell the connection where your database lives by browsing to its file location. Click OK to save it.
Creating an Amazon Web Account
To access Amazon Web Services you're going to need an access key. Membership is free and can be arranged at www.amazon.com/gp/browse.html?node=3435361. While you're there you might glance at the documentation for calls to ItemSearch since this is the call we are going to make to bring bibliographical information back to our Media organizer. You can find the Amazon API documentation here: http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=19.
Consuming Web Services in SQL Anywhere 10
A SQL Anywhere database can consume Web Services. These can be standard Web Services available over the Internet, or they can be provided by SQL Anywhere databases as long as the Web Service isn't in the same database as the client procedure or function. To create them you must use a stored procedure or function.
The first thing you have to do when adding any stored procedure or function to your database is to start a SQL interpreter such as Interactive SQL. To open it, click Programs->SQL Anywhere 10->Interactive SQL. A Connect dialog will open, as shown in figure 3. Enter "Amazon" for the name of the ODBC connection.
You may now go about creating your stored procedure or function.
Use a function or stored procedure to create a Web Service call out of SQL Anywhere. If you're using a version of SQL Anywhere prior to version 10.0.1, the function name must be the same name as the SOAP operation you want to perform. For example, in our case we want to use the ItemLookup service, so the name of our function is ItemLookup. I might note that this restriction has been lifted in version 10.0.1. Now you can call the function whatever you like, but you must add a statement such as the following to the end of your function declaration:
Additionally, the names of any parameters also appear in tagnames in the SOAP request envelope. An important part of defining a SOAP stored procedure is specifying them correctly, as the SOAP server expects to see these names.
Finally, you'll need to know the URL, binding, and namespace of the service you want to access. Amazon E-Commerce Service (ECS) publishes its API through Web Services Description Language (WSDL) documents, which you can use to construct SOAP requests. Their WSDL is available at http://webservices.amazon.com/AWSECommerceService/AWSECommerceService.wsdl. A look at some WSDL might shed some light as to where these parameters come from (see code Listing 1). Note: portions of WSDL were intentionally deleted for clarity.
First, if you scan the listing to the service element you'll see that the SOAP address or endpoint is http://soap.amazon.com/onca/soap?Service=AWSECommerceService. This is the value of our URL. Secondly, when analyzing RPC style WSDL documents, namespaces are specified for each operation. In a doc style, the namespace is generally the targetNamespace that defines the top-level WSDL elements. So our namespace clause is http://webservices.amazon.com/AWSECommerceService/2007-04-04. Finally, if you look in that service element, there's a binding attribute that links us to the binding section of the WSDL:
<port name="AWSECommerceServicePort" binding="tns:AWSECommerceServiceBinding">
Here, you can determine that the style=document and all operations specify use=literal. So for our Web client function we need to specify type 'SOAP:DOC'.
The complete SQL Anywhere example code is shown in Listing 2.
Parsing the XML Response
After you make an outbound SOAP request, the server will return an XML document. You'll probably need to parse this document to make use of the information inside it. Here's where OpenXML can come in handy. The openxml procedure is used in the FROM clause of a query to generate a result set from an XML document. Openxml uses a subset of the XPath query language to select nodes from an XML document. If you're not familiar with XPath you might think of it as a grep for an XML document. In other words, it's an easy way to pick off XML nodes.
An example XML response is shown in code Listing 3. What you need to know is how to return a particular XML node. As mentioned above, this can be done with the XPath query language. It may be helpful to know that "//" represents descendant nodes, the '*' represents any namespace such that //*:'ItemAttributes' finds all such nodes. The WITH clause further selects on child elements based on the root from the XPATH argument (//*:'ItemAttributes'). For example, if we wanted to extract the title, we could use this statement *:Title/text().
You may be wondering how to look at the raw HTTP responses received. This information can be useful for debugging. If you're using SQL Anywhere 10.0.1 build number 3484 or higher you can use the database server command line option -zoc <filename> or set the server option WebClientLogFile. The other alternative is to use a logging tool such as TcpTrace.
Listing 4 is the response to the ItemLookup request using TcpTrace.
Creating a GUI in PowerBuilder
Now that we have our back-end built, we can write a front-end GUI using a RAD development tool such as PowerBuilder. To start things off, open a new workspace in PowerBuilder and name it Amazon. Now, create a new target by choosing File->New->Target and choose application. Finally, create a main window by choosing File->New->PB Object and choose window. We now have all the elements needed for a basic project setup and simply need to add code to tie them together. To do this, you will have to add the code shown in Listing 5 to your application object's open event.
With that in place we can go about adding elements to our main window. A single line edit will be provided to allow the user to enter a new UPC. The information gathered here will be used in the call to our stored procedure that will populate a datawindow containing bibliographical information. To view the latter, add a datawindow control. Finally, to fire the whole process off, drop down a command button. The finished layout is shown in Figure 4.
The workhouse of the GUI is the datawindow, since it's going to do all the work of invoking the stored procedure we created earlier. To create it, go to File->New->Datawindow->FreeForm. When prompted for your data source, make sure you choose Stored Procedure. From there, you'll get to choose the function or procedure you want to invoke. In our case, choose the ItemLookupWrapper procedure. Complete the creation of the object by stepping through and accepting all the defaults until you complete the process by clicking the Finish button.
Making the Call
Having put all the work of calling the Amazon Web Service in a stored procedure on the database side, calling it out of PowerBuilder is trivial. Other than the database connection and the creation of the datawindow object, you only need two lines of code! The first is a call to set your transaction object and the other retrieves whatever UPC the user has entered.
The first call can be put in the open event of your main window and is simply:
The second call is needed to invoke the stored procedure and can be put in the clicked event of a cb_ok button like this:
Figure 5 shows the finished product. Key in a UPC and with the help of PowerBuilder, SQL Anywhere and Amazon Web Services you have just rolled your own media organizer!
Calling Web Services out of SQL Anywhere opens up a whole new world of programming fun. Coupled with this arsenal and an excellent RAD development tool such as PowerBuilder, you can be up and coding your own Web Service clients in no time at all. I encourage you to start exploring!
In this segment we looked at consuming Web Services from SQL Anywhere. In the next article in this series, we'll look at exposing them. In doing so, we will go about building our own mini-version of an Amazon-like Web Service using the data we collected in this part.
- Amazon API Documentation http://developer.amazonwebservices.com/connect/kbcategory.jspa?categoryID=19.
- SQL Anywhere Documentation www.ianywhere.com/developer/product_manuals/sqlanywhere.
- SQL Anywhere Developer Edition http://eshop.sybase.com/eshop/try_buy.
- PowerBuilder Evaluation Copy http://eshop.sybase.com/eshop/try_buy.
- Inside SOAP www.xml.com/pub/a/2000/02/09/feature/index.html.