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:

set 'SOAP(operation=ItemLookup)'

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.