In this example, you assume that the web server returns at least two values in the result set.

Run the following SQL script to create a table with the intent of storing the values of the result set:

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

It is assumed that the keywords, Attribute and Value, are defined in the HTTP response header fields. A Body attribute contains the body of the message, which is an HTML document.

Run the following SQL script to create a web client procedure:

CREATE PROCEDURE test( IN parm CHAR(128) ) 
    URL 'HTTP://localhost/test'
    TYPE 'HTTP';

The procedure type is HTTP, so this procedure requests a table as a result set.

Result sets can be inserted into the StoredResults table as follows:

INSERT INTO StoredResults SELECT * FROM test('Storing into a table') 
    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 Value
    FROM test('Calling test for the Status Code')
    WITH (Attribute LONG VARCHAR, Value LONG VARCHAR) 
    WHERE Attribute = 'Status';

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

• Web client SQL statements
• Accessing variables in result sets