Question Re: Web Client Procedure Returns a third undocumented column INSTANCE in Technology Q&A

Web Client Procedure Returns a third undocumented column INSTANCE

thomas_duemesnil — Tue, 17 Jul 2018 11:50:00 GMT

I have changed database file to 17.0.9.4803 from 16.0.0.2344. After that change my Web Client Procedures no longer worked. The login proecedure can be seen as an example.

CREATE OR REPLACE PROCEDURE "dba"."alfresco_login"(
in "HostURL" varchar(256),
in "u" varchar(256),
in "pw" varchar(256)
)
result("Attribute" long varchar, "Value" long varchar)
url '!HostURL/alfresco/service/api/login'
type 'HTTP:GET'
header 'User-Agent:SATest';

I get an error Message SQLCODE -866 returned a result set with a different schema.

To take a look at the default result set I have declared the Procedure without the result clause and instead of

select * from dba.alfresco_login(...)

I used

call dba.alfresco_login(...)

This showed a third column INSTANCE that I was not aware of before.

In the documentation CREATE PROCEDURE statement [Web service] the paragraph for RESULT only indicates 2 Columns.

I fixed my procedures with the following result clause

CREATE OR REPLACE PROCEDURE "dba"."alfresco_login"(
in "HostURL" varchar(256),
in "u" varchar(256),
in "pw" varchar(256)
)
result("Attribute" long varchar, "Value" long varchar, "Instance" integer)
url '!HostURL/alfresco/service/api/login'
type 'HTTP:GET'
header 'User-Agent:SATest';

Is this Documented and I can't find it?

HTH Thomas

Re: Web Client Procedure Returns a third undocumented column INSTANCE

MarkCulp — Tue, 17 Jul 2018 13:24:36 GMT

IIRC the Instance column was added to the result set in 17.0.1 if you are using a 17.0.1 (or newer) database. The purpose of the column is to allow support of having multiple attribute values with the same name. This change is documented on this page (scroll down to Web service changes), and you can read more about it here.

Re: Web Client Procedure Returns a third undocumented column INSTANCE

thomas_duemesnil — Wed, 18 Jul 2018 01:07:00 GMT

Thanks for the hints.

But the Documentation of Create Procedure should be update to include this third column.

I see in the Sample select * from Proc() with (attr.....) is this style the better approach to call a webclient function so I avoid the Result clause in the procedure ?

Re: Web Client Procedure Returns a third undocumented column INSTANCE

VolkerBarth — Wed, 18 Jul 2018 04:33:24 GMT

is this style the better approach [...]

IMHO, if you already know the result set when creating the procedure, it's better to specify the RESULT clause within the procedure's declaration - otherwise you will need to specify it in every query you select over the procedure...

And if the procedure's result set does change (as it is here), I guess you will need do adapt it either in the procedure's declaration or in the query using the procedure - and then again, I would prefer the former.

Omitting the RESULT clause is certainly appropriate in cases where the result set's schema may vary between calls, but I guess that's not possible with web client procedures.

Re: Web Client Procedure Returns a third undocumented column INSTANCE

MarkCulp — Wed, 18 Jul 2018 08:46:50 GMT

I have added a comment for the documentation team to the page: http://dcx.sap.com/index.html#sqla170/en/html/3be475ec6c5f10149a70de3d57f416de.html