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

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

asked 14 Jul '20, 13:53

Siger Matt
3.3k●56●72●101
accept rate: 15%

edited 16 Jul '20, 05:28

Volker Barth
40.2k●361●550●822

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;

(14 Jul '20, 14:02) Breck Carter

flat view

One Answer:

active answers oldest answers newest answers popular answers

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.

permanent link

answered 14 Jul '20, 14:46

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 14 Jul '20, 14:57

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

(14 Jul '20, 14:51) Siger Matt

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

(14 Jul '20, 14:59) Siger Matt

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

(14 Jul '20, 15:00) Siger Matt

Replies hidden

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.

(14 Jul '20, 15:46) Volker Barth

flat view

Your answer

toggle preview

community wiki:

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

*italic* or _italic_
**bold** or __bold__
link:[text](http://url.com/ "title")
image?![alt text](/path/img.jpg "title")
numbered list: 1. Foo 2. Bar
to add a line break simply add two spaces to where you would like the new line to be.
basic HTML tags are also supported

learn more about Markdown

Question tags:

sa-12 ×438
sa-17 ×246
webclient ×48
http ×33
call ×3

question asked: 14 Jul '20, 13:53

question was seen: 957 times

last updated: 16 Jul '20, 05:28

SAP SQL Anywhere

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

Follow this question

Related questions