At 12:17 AM 1/11/2004 -0800, Daniel Miller wrote:

>Hi all.
>
>I'm trying to implement a stored procedure to perform updates on Debian
>Linux packaged FB1.5. Full text of procedure below:
>
>CREATE PROCEDURE UPDATE_ZIPCODE (ID INTEGER, ZIPCODE CHARACTER (10),
>STATE CHARACTER (2), CITY VARCHAR (30))
>RETURNS (NEWID INTEGER)
>AS
>BEGIN
> IF (id IS NOT NULL) THEN
> BEGIN
> UPDATE ZIPCODES SET zipcode=:zipcode, state=:state, city=:city WHERE
> id=:id;
> newid=id;
> END
> ELSE
> BEGIN
> newid = GEN_ID(gen_zipcode_id,1);
> INSERT INTO ZIPCODES (id, zipcode, city, state)
> VALUES (:newid, :zipcode, :city, :state);
> END
>END !!
>
>The idea is to have a single procedure to be used for both inserts &
>updates, and have it return the id of the record created/updated. I'm
>using ibWebAdmin as my interface. The problem comes when I try to test
>the procedure. Executing the following SQL brings these results:
>
>execute procedure update_zipcode (4,'89102','Las Vegas','NV')
>RETURNING_VALUES NEWID
> >Dynamic SQL Error SQL error code = -104 Token unknown - line 1, char
>63 RETURNING_VALUES
>
>execute procedure update_zipcode (4,'89102','Las Vegas','NV')
>arithmetic exception, numeric overflow, or string truncation
>
>I tried this in isql also - same results. What am I doing wrong?

RETURNING_VALUES belongs to PSQL - it's how you bring back return values in
variables when you execute one SP inside another. From a client, just call:

execute procedure update_zipcode (4,'89102','Las Vegas','NV')

and read the return value(s) in the XSQLDA structure. ISQL does this for
you: just try it and you should get the result.

Depends on your data access interface how you read it in your application,
e.g. in IBO, you read it in the Fields[] array of the TIB_Statement
object. Jaybird, ODBC, FIBPlus, etc. will each have its own way of
encapsulating what comes back.

./hb