procedure with inout parameter that call a web service

[Former Member]

Hi, The procedure that call a procedure with parameter inout does work correctly because the parameter inout return the value ok.

But the procedure that call a web service with parameter inout does not work correctly because the parameter inout does not return the value.

/////////////////////////////////////
// SERVER SIDE
/////////////////////////////////////

/////////////////////////////////////
// p_test_param_srv: procedure with 1 IN parameter and 1 INOUT parameter
/////////////////////////////////////

CREATE PROCEDURE "dba"."p_test_param_srv"(IN al_number NUMERIC(12), INOUT al_total NUMERIC(12) )
RESULT( num_mens NUMERIC(12) )
BEGIN

    SET al_total = al_number * 2 ;

    SELECT 1 ;

END

/////////////////////////////////////
//  FROM interactive SQL: call procedure p_test_param_srv 
/////////////////////////////////////

CREATE VARIABLE al_number NUMERIC(12) ;
CREATE VARIABLE al_total NUMERIC(12) ;

SET al_number = 25 ;

CALL "dba"."p_test_param_srv"(al_number,al_total) ;

SELECT al_total ;

//////////

Results --->  al_total = 50     OK !!!

/////////////////////////////////////
//  ws_test_param: web service that call a procedure with 1 IN parameter and 1 INOUT parameter
/////////////////////////////////////

CREATE SERVICE "ws_test_param" 
    TYPE 'RAW' AUTHORIZATION OFF SECURE OFF URL PATH OFF USER "dba"  AS
call "p_test_param_srv"(:number,:total)

/////////////////////////////////////


/////////////////////////////////////
// CLIENT SIDE 
/////////////////////////////////////

/////////////////////////////////////
// p_test_param_cli: procedure for client web service with 1 IN parameter and 1 INOUT parameter
/////////////////////////////////////

CREATE PROCEDURE "dba"."p_test_param_cli_ws"( in "number" numeric(12),inout "total" numeric(12) ) 
url 'http://localhost:8088/webservice_rep_local/ws_test_param'
type 'HTTP:POST'

/////////////////////////////////////
//  FROM interactive SQL: call procedure p_test_param_cli 
/////////////////////////////////////

CREATE VARIABLE al_number NUMERIC(12) ;
CREATE VARIABLE al_total NUMERIC(12) ;

SET al_number = 25 ;

CALL "dba"."p_test_param_cli_ws"(al_number,al_total) ;

SELECT al_total ;

//////////

Results --->  al_total = NULL     

/////////////////////////////////////

[MarkCulp]

This is expected. OUT and INOUT parameters are only supported when using SOAP web procedures, and then only if the parameter value is of a string type. See the documentation for more info.

[VolkerBarth]

Mark, where exactly within the cited page or other pages can I find that information? - All I've noted so far has been that quote on the SOAPHEADER clause description, which applies to the SOAP format only:

A SOAP header can be declared as a static constant, or can be dynamically set using the parameter substitution mechanism (declaring IN, OUT, or INOUT parameters for hd1, hd2, and so on). A web service procedure can define one or more IN mode substitution parameters, and a single INOUT or OUT substitution parameter.

[MarkCulp]

I would agree that the documentation is lacking on this topic.

But frankly I'm not sure what it even means to have an INOUT or OUT parameter on a web/HTTP procedure call. It is easy to understand how an IN parameter gets passed from the client caller across an HTTP request to the receiver/server (i.e. use the well understood standard of sending the value as a GET or POST parameter), but how would an "out" parameter value (final parameter value of the called procedure on the server) be returned from the server back to the client? There is no 'standard' for doing this. IMHO a better method that works in most (if not all cases) - and one that I have used often - is to write a wrapper function around the actual web service procedure that does 'select * into #temp from web_proc(...)' and then post process the values in #temp - i.e. returned header and body values - to extract the information needed and set/return whatever information necessary to the caller.

I am not a SOAP expert - I have never used it (it was an extremely complicated 'standard' with no one way of doing things - therefore no standard at all - and thus was a fad that I am glad to see has fizzled out) - but AFAIK in the case of a SOAP web procedure the single OUT/INOUT parameter in the client procedure receives the SOAP body from the response from the server.

I have added a comment to the documentation page.

HTH

[VolkerBarth]

Thanks for the clarification as to the docs, and yes, I certainly agree with your further thoughts.

I do use SOAP web service functions/procedures but with IN parameters only:)