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

asked 17 Jul, 11:50

Thomas%20Duemesnil's gravatar image

Thomas Dueme...
2.6k283763
accept rate: 16%

edited 18 Jul, 04:33

Volker%20Barth's gravatar image

Volker Barth
32.2k328474688


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.

permanent link

answered 17 Jul, 13:24

Mark%20Culp's gravatar image

Mark Culp
23.7k9134279
accept rate: 40%

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 ?

(18 Jul, 01:07) Thomas Dueme...
Replies hidden
1

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.

(18 Jul, 04:33) Volker Barth
1

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

(18 Jul, 08:46) Mark Culp
Comment Text Removed
Your answer
toggle preview

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

Question tags:

×106
×84
×26

question asked: 17 Jul, 11:50

question was seen: 1,694 times

last updated: 18 Jul, 17:05