Web Style Procedure - Select vs Call - Different Schema Than Expected

[former_SQLA_member1694868]

Starting from Breck's article here: Building a Link-Checker Inside SQL

I created (copied) a basic procedure to check the HTTP status of a webpage:

CREATE OR REPLACE PROCEDURE sxhttp_head_attributes (
    IN url          LONG VARCHAR
    )
RESULT (
   attribute  LONG VARCHAR,
   value      LONG VARCHAR )
    URL '!url' 
TYPE 'HTTP:HEAD';

I need to take different actions based on different response codes or an errors of that page, so I want to select the results of the procedure:

select * from sxhttp_head_attributes ('http://www.google.com');

This gives results in 12.0.1.3592.

In 17.0.6.2757 it errors out:

There was an error reading the results of the SQL statement.
The displayed results may be incorrect or incomplete.
sxhttp_head_attributes returned a result set with a different schema
than expected
SQLCODE=-866, ODBC 3 State="HY000"
Line 1, column 1

However calling the procedure instead of selecting from it works on both versions

call sxhttp_head_attributes ('http://www.google.com');

The trouble is I am not familiar with a way to use the call syntax of a procedure and take actions based on the results, as I would usually select from the procedure to evaluate the results.

So the question is why is there a difference in behavior and is it intended. The goal is to provide a way to support something like this (crude example) that will work on both versions:

BEGIN 
DECLARE STAT LONG VARCHAR;

SELECT Value into STAT from sxhttp_head_attributes ('http://www.google.com') where Attribute = 'Status';

select
CASE
WHEN STAT like '%200%' THEN 'Server Up'
WHEN STAT like '%500%' THEN 'Server Error'
ELSE 'Server Down'
END CASE as EasyStatus;
END

[Breck_Carter]

I don't know if it will help, but in The Time Before FROM there was such a thing as CURSOR FOR CALL.

Here's an example from my Ancient Template Library...

-- Singleton result set procedure call

BEGIN
   DROP PROCEDURE myProc;
   EXCEPTION WHEN OTHERS THEN
END;

create procedure  myProc @inputA int, @inputB int
as
begin
declare @myVar numeric(10,0)
select @myVar = @inputA + @inputB

select @myVar MYVAR
end
go

BEGIN
DECLARE @inputA NUMERIC ( 10, 0 );
DECLARE @inputB NUMERIC ( 10, 0 );
DECLARE @myVar  NUMERIC ( 10, 0 );
DECLARE c_fetch NO SCROLL CURSOR FOR CALL myProc ( @inputA, @inputB );
SET @inputA = 5;
SET @inputB = 6;
OPEN c_fetch;
FETCH c_fetch INTO @myVar;
CLOSE c_fetch;
SELECT STRING ( 'The answer is ', @myVar );
END;

[VolkerBarth · Accepted solution]

Is it possible the web client procedure really has a different schema with v17.0.9? IIRC there has been an additional column...

To quote the docs:

If you are using database created with version 17.0.1 or higher, then the result set contains a third column named Instance, of type INTEGER.

[former_SQLA_member1694868 · Accepted solution]

Sure enough! There is a third column with integer codes (1s and 2s on the result for http://www.google.com).

[former_SQLA_member1694868 · Accepted solution]

RESULT clause

The RESULT clause is required to use the procedure in a SELECT statement. The RESULT clause must return two columns. The first column contains HTTP response header, status, and response body attributes, while the second column contains the values for these attributes. The RESULT clause must specify two character data types. For example, VARCHAR or LONG VARCHAR. If the RESULT clause is not specified, the default column names are Attribute and Value and their data types are LONG VARCHAR. If you are using database created with version 17.0.1 or higher, then the result set contains a third column named Instance, of type INTEGER. If the result of calling the HTTP request returns more than one attribute with the same name, then the Instance value can be used to obtain the different attribute values.

Link to SAP Help Article

[former_SQLA_member1694868 · Accepted solution]

Going to have to start checking the SAP help instead of just DCX I suppose.

[VolkerBarth · Accepted solution]

Well, at least the newer What's New sections for 17.0.6 and above... 🙂

FWIW, that enhancement is also contained in the DCX docs as a comment.