Here I'll be building a pair of stored procedures that call a Web Service out on the Internet for multiple rows of data that I'll present in a DataWindow.

I'm using SQL Anywhere 10.0.1.3619, but it should be possible using version 9.0.2 with only a couple of changes.

My Toolbox:

• PocketBuilder 2.5
• SQL Anywhere 10.0.1
• Internet access
• My trusty HP-5550 PocketPC
• A "Just Do It" attitude

Procedure Overview:
a.  Find a Web Service to exploit
b.  Write a pair of stored procedures
c.  Create a DataWindow to call the stored procedure
d)  Create an application using that DataWindow

The Web Service to Exploit
Poking around on the Internet, I found a set of Web Services at www.ecocoma.com/shipping_webservice.aspx that calculate shipping rates for all the major carriers. These Web Services had an API that was understandable and a "trial user" capability necessary to my experiment.

From quick look at the eCoComa examples and their documentation it was easy to recognize the important items:

• Namespace: http://service.ecocoma.com/shipping/ups
• Endpoint: http://service.ecocoma.com/shipping/ups.asmx
• SOAP Operation (or 'action'): GetUPSRate
• The argument list used for the SOAP call
  - Subscription information (we are using the trial)
  - Originating postal code (zipcode in the US)
  - Originating country
  - Destination postal code
  - Destination country
  - Package weight
  - Package value
  
• and the "look" of the SOAP response

I am a big proponent of the "let's just try it" school, so the first thing I tried was a simple stored procedure that made the SOAP call then looked at what ended up in the log file. (I wasn't ready to parse the payload into a result set - first things first). I piggybacked on an existing database file, but I could have even used a blank (yes, blank!) database file. This technique doesn't add or use any user tables in the database, so it's safe.

I added the HTTP logging switch (-zoc) to the start line so I could examine the traffic back and forth. I could turn off logging once I was satisfied with the operation.

Using ODBC Administrator - set the "Start line:" to:

dbeng10.exe -zoc d:\sa10_ws.log

The Two Stored Procedures
Opening up iSQL and using Chance's article as inspiration, I made some guesses and created the first stored procedure GetUPSRate:

call sa_make_object( 'function', 'GetUPSRate');

alter function GetUPSRate(
      KeyID char(20),
      DomainID char(20),
      origPostal char(20), origCountry char(20),
      destPostal char(20), destCountry char(20),
      Weight char(10),
      Value char(10) )
      RETURNS LONG VARCHAR
      url 'http://service.ecocoma.com/shipping/ups.asmx'
      namespace 'http://service.ecocoma.com/shipping/ups'
      type 'SOAP:DOC'
      set 'SOAP(operation=GetUPSRate)'
;