Result Set Retrieval from a Web Service

Web service procedures of type HTTP return all the information about a response in a two-column result set. This result set includes the response status, header information and body. The first column, is named Attribute and the second is named Value. Both are of data type LONG VARCHAR.

The result set has one row for each of the response header fields, and a row for the HTTP status line (Status attribute) and a row for the response body (Body attribute).

The following example represents a typical response:

Create the following web service stored procedure to use as an example.

CREATE OR REPLACE PROCEDURE SybaseWebPage() 
URL 'http://www.sybase.com/mobilize'
TYPE 'HTTP';

Execute the following SELECT query to obtain the response from the web service as a result set.

SELECT * FROM SybaseWebPage() 
    WITH (Attribute LONG VARCHAR, Value LONG VARCHAR);

Because the web service procedure does not describe the shape of the result set, the WITH clause is required to define a temporary view.

The results of a query can be stored in a table. Execute the following SQL statement to create a table to contain the values of the result set.

CREATE TABLE StoredResults(
     Attribute LONG VARCHAR,
     Value     LONG VARCHAR
);

The result set can be inserted into the StoredResults table as follows:

INSERT INTO StoredResults 
    SELECT * FROM SybaseWebPage() 
    WITH (Attribute LONG VARCHAR, Value LONG VARCHAR);

You can add clauses according to the usual syntax of the SELECT statement. For example, if you want only a specific row of the result set you can add a WHERE clause to limit the results of the SELECT to only one row.

SELECT * FROM SybaseWebPage() 
    WITH (Attribute LONG VARCHAR, Value LONG VARCHAR) 
    WHERE Attribute = 'Status';

This SELECT statement retrieves only the status information from the result set. It can be used to verify that the call was successful.