Still in iSQL, running this second stored procedure will display six rows of data, three columns each. As you'd expect, the WITH statement mapped the XML nodes into columns with the names I wanted.

call GetUPSRateWrapperUS( 'SHP-M89872906F', '60506', '01776', '10.5' )

MailCode    Rate    Service
GND    9.63     UPS Ground
3DS    22.22    UPS 3 Day Select
2DA    32.88    UPS 2nd Day Air
1DP    71.34    UPS Next Day Air Saver
1DA    78.51    UPS Next Day Air (by 10:30AM)
1DM    112.28   UPS Next Day Air Early A.M. (by 8:00AM)

Looking through the stored procedures you can see that the database still has not affected any tables. We just have a couple of stored procedures doing all the work. The SQL Anywhere database engine is doing all the work with no other tables to select into or do anything with. (I think this is pretty cool)

The next step is to enhance the stored procedure to retrieve the shipping rate and stuff it into a PocketBuilder application.

The PocketBuilder DataWindow
Creating a DataWindow using the stored procedure, GetUPSRateWrapperUS, as the data source is simple. However, I'm told that using a stored procedure is one of those "less used features" in PocketBuilder (and PowerBuilder) so I'll briefly outline the necessary steps:

a.  Set the database profile to use the same ODBC profile as iSQL used.
b.  In the PocketBuilder IDE, select File New DataWindow Grid
c.  Select the "Stored Procedure" data source and "Retrieve on Preview"
d.  Select the specific "getupsratewrapperus" stored procedure.
e.  Select the defaults for the rest of the wizard.
f.  The preview will need the retrieval arguments. The "MemberKey" field will need the current value from the eCoComo Web site. See Figure 1
g.  Save this DataWindow as "dw_upsrate_us." (You can dress up the default DataWindow if you want otherwise you're finished.) See Figure 2

The Final PocketBuilder Application
Once I had the DataWindow running, wrapping an application around it for PocketBuilder was pretty easy. Creating a "template application" with the default database information does 90% of the work. All the default connection service objects are used, so there's no need to detail them here. The end result is that to populate the DataWindow we simply need to call the Retrieve() method passing in the arguments. In this example, all the data fields are generic edit masks that yield the code for the "Get Rates" button being:

dw_1.SetTransObject( SQLCA )
dw_1.Retrieve( em_service_key.text, &
      em_start_zip.text, &
em_dest_zip.text, &
      em_weight.text )

When deployed to the device, the data source file is a classic text DSN file with the simple contents:

[ODBC]
uid=DBA
pwd=sql
driver=dbodbc10.dll
enginename=WebServiceDemo
databasename= WebServiceDemo
databasefile=\sa10_webservices.db
start=\Program Files\SQLAny10\dbsrv10.exe -zoc \sa10_ws.log

These few steps, followed by some prettying up, gives us a PocketBuilder application running on the device that retrieves the UPS shipping rates for your parameters. See Figure 3

Conclusion
In this article I've tried to demonstrate the simple steps to retrieve and parse many rows of data from a SOAP Web Service.

This example was for UPS shipping rates, but the technique is generic, easy, and fun! The same general steps would be followed to figure out other Web Services with their argument lists and the returned payloads.

References
  •   D.Chance. "Consuming Amazon Web Services using SQL Anywhere 10.01." PowerBuilder Developer's Journal. September 2007.
  •   eCoComa Webservices. www.ecocoma.com/webservice.aspx.
  •   "System Procedures - openxml." SQL Anywhere Server - SQL Reference.
  •   "Importing XML using openxml." SQL Anywhere Server - SQL Usage.
  •   Publicly available Web Services. www.xmethods.net.