At 10:57 PM 30/07/2005 -0400, you wrote:

>In this procedure the Update line works as expected--The row in the table is
>updated with the newly calculated values. The calculated values are stored
>in the named output params, however in the application the parameters all
>contain zeros.
> How can the exact same variables update the table correctly but return
>zeros to the app?

The input arguments and the output arguments represent two sets of
replaceable values. Input arguments accept values into one XSQLDA
structure, while the output to the return arguments is returned to the
client in a different XSQLDA structure.

I don't know whether this causes confusion in the particular data access
interface you use; but in any case, the most obvious source of your
problem is the logic of the statements following ELSE near the end of the
procedure. Your procedure unconditionally sets all but one of your output
arguments to zero when other processing is finished.

> Thank you very much for looking. I greatly appreciate your help :-)
> Mark :-)
> ALTER PROCEDURE SP_DICEMARCH (
>DIEKEY INTEGER,
>MARCHX INTEGER,
>MARCHY INTEGER)
>RETURNS (
>RESULT INTEGER,
>NEWMAPX INTEGER,
>NEWMAPY INTEGER,
>NEWX INTEGER,
>NEWY INTEGER)
>AS
>DECLARE VARIABLE OLDMAPX INTEGER;
>DECLARE VARIABLE OLDMAPY INTEGER;
>DECLARE VARIABLE OLDX INTEGER;
>DECLARE VARIABLE OLDY INTEGER;
>DECLARE VARIABLE MOVED INTEGER;
>DECLARE VARIABLE SPEED INTEGER;
>begin
>select MAPX,MAPY,PIXELX,PIXELY,MOVED,SPEED from DICE
>where PKEY=:diekey
>into :oldmapx,:oldmapy,:oldx,:oldy,:moved,:speed;
>if (:moved < :speed) then ********
>begin
>result = 1;
>newmapx = :oldmapx + :marchx; ********
>newmapy = :oldmapy + :marchy; ******
>newx = :oldx + (:marchx * 40); ******
>newy = :oldy + (:marchy * 40); ******
>update DICE set
>MAPX=:newmapx,
>MAPY=:newmapy,
>PIXELX=:newx,
>PIXELY=:newy,
>MOVED=MOVED+1
>where PKEY=:diekey;
>end
>else
>result = 0;
>newmapx = 0;
>newmapy = 0;
>newx = 0;
>newy = 0;
>suspend;
>end

Regardless of the logic, which is easily fixed, there are a few gotchas here.


This procedure should be called from the client using EXECUTE
PROCEDURE. It is not, structurally, a "selectable" procedure.

Although it is possible to use a SELECT call to make a procedure do
data-changing operations, it is definitely not recommended. What I see
here is a common misapprehension, that sticking a SUSPEND statement into an
executable SP magically turns it into a selectable procedure.

Remove the SUSPEND from this procedure and call it with EXECUTE
PROCEDURE. Have the client read the values in the *return arguments* after
execution, not the input arguments. And, once again, realise that the
values returned from SPs (whether selectable or executable) are not
committed values. They represent the state of your sets as seen by the
current (uncommitted) transaction and are in no way reliable outside that
transaction context.

Also note wrong syntax in your procedure code: you should not use the
colon prefix on variables (whether local or defined as arguments) in
procedural statements. They are required only in SQL statements. I have
starred the statements where the use of the colons is wrong.

./heLen