branching in create service statement

[Baron]

I am trying to build a new webserive in Sybase 10, whose structure of output parameters depends on the value of input parameters:

CREATE SERVICE S1 TYPE 'XML' AUTHORIZATION OFF USER DBA AS
begin
if (parameter1='A') then
call myprocedure1(:parameter1, :parameter2);
else
call myprocedure2(:parameter1, :parameter2);
end if;
end;

This syntax is not accepted in Sybase.

The question is how can I make this branching in the call of the service (the point is because myprocedure1 and myprocedure2 return different number and type of parameters, and I need to have one single service but the answer of the service should have different fields depending on parameter1)

Thanks in advance for any help.

[VolkerBarth]

AFAIK, the sql-statement within the CREATE SERVICE statement can either by a SELECT statement or a CALL-statement but no statement block (as in your case).

I would guess you can

• either use one single procedure that contains the logic both of myprocedure1 and myprocedure2, as one stored procedure generally can return different result sets based on different input parameters,

• or you could call a third procedure in the CREATE SERVICE statement that contains the logic to switch to the according procedure (i.e. it would basically have the block you wrote as its statement block)...

[Baron]

In one stored procedure I can define one RESULT statement while creating the procedure and this RESULT defines the name, number and type of output parameters of the procedure. The point is I need to have a dynamic list of RESULTS depending on the input-parameter: Example: CREATE PROCEDURE myprocedure1(in parameter char(1), in parameter2 char(1)) RESULT (firstname char(5), address char(10)) BEGIN....END;

CREATE PROCEDURE myprocedure1(in parameter char(1), in parameter2 char(1)) RESULT (surname char(5), salary char(10)) BEGIN....END;

The problem is not in controlling the output values, but in controlling the structure of the outputs(in other words, if I merge the 2 above procedures then I will get the correct values for "surname" and "salary" but they will be titled as "firstname" and "address").

Even with a third switching procedure I must define one RESULT in this main procedure.

[VolkerBarth]

Are you sure you need a procedure with a declared RESULT SET clause?

For procedures with variable result sets as you have here, you do not declare a result set so their variable result sets need to be described at run time. However, I do not know whether web services require a pre-declared result set for procedures.

[Baron]

If I define the procedure without RESULT SET, and I call the procedure over the service then I get only one column as answer from the service, however the same procedure delivers the correct number of columns in case calling it from DBISQL! Which means, for the web services I must define a RESULT SET.

Moreover, if I deploy a third switching procedure, then the output-structure of the service will take always the structure of the service which is first literally defined in this switching procedure, (i.e. the if statement will redirect the execution to the correct procedure, but it considers only the structure of the first procedure (the procedure written in the first part of IF statement))!!

In this case, I would say that the webservice does not support dynamic output structure (SQL Anywhere 10), and I have to use separate services.

[VolkerBarth · Accepted solution]

OK, I just noticed the following quote from the v16 CREATE PROCEDURE doc topic that apparently fits to your test results:

Web services Web services rely on the RESULTS clause of the stored procedure to determine the number and types of the column in the result set. Web services do not support procedures that return multiple result sets, nor do they support variable result sets through the use of EXECUTE IMMEDIATE.

AFAIK, when a client expects a static result set from a procedure (like in this case) and there is no RESULT SET clause, SQL Anywhere describes that as a single value (IIRC, of type int). I guess that's the same when you use SELECT * from myprocedure()" without providing a WITH clause...

---

That being said, you might try to use a TYPE RAW web service as that may offer more flexibility (at the obvious cost of more coding effort) - but again, that's just a guess.

[Baron · Accepted solution]

Thank your for help. I could solve it after separating the webservice, so that I have two different webservices instead of one.