Helen Borrie wrote:

>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
>
>

Thank you for the reply.

As I noted - I tried this in ISQL already. I tried it again - same
arithmetic exception. I tried running a select procedure from ISQL (and
ibWebAdmin) - no problem. So I tried creating a new procedure like this
one for another table - and it worked! So it appears to be some kind of
corruption, either in that particular procedure or in the table (I've
seen this before, particularly when dealing with views - which is one
reason I have replaced all my views with stored procedures). So I ran a
sweep/data repair cycle, and tried again - and now it's working.

For some reason, my metadata keeps getting corrupted - particularly when
I make numerous changes to a given table or accessory. I dunno why.

Daniel