on 2018 Jul 17 12:50 PM
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
Request clarification before answering.
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.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.
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.
I have added a comment for the documentation team to the page: http://dcx.sap.com/index.html#sqla170/en/html/3be475ec6c5f10149a70de3d57f416de.html
User | Count |
---|---|
50 | |
9 | |
8 | |
6 | |
5 | |
5 | |
5 | |
5 | |
4 | |
4 |
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.