Re: Serial value in a Stored Procedure
gtmad@smi.mn.org (Madavan) writes:
>Hello everyone,
>Is there a way in a stored procedure to get the value of the
>serial field last inserted (like SQLCA.SQLERRD[2] in 4GL).
>I want to do something like
>create procedure ...
>.
>.
>.
>insert into master_table values (0,.....) ## insert a serial field
>let ser_val = ? ## How to get the serial value inserted before?
>.
>.
>insert into detail_table values (ser_val,....)>.
>.
>.
>end procedure;
We recently figured out how to do this with a trigger that sets a
global variable that the stored procedure knows about:
CREATE TRIGGER i_tableINSERT ON table
REFERENCING NEW AS new
FOR EACH ROW
(EXECUTE PROCEDURE get_serial (new.serial_column));
CREATE PROCEDURE get_serial(p_serial INTEGER){-----------------------------------------------------------------------
Assigns the value passed into the procedure to global pg_serial.
This is designed to be a triggered procedure whose parameter value is
the value assigned to the serial column upon insert to a table.
-----------------------------------------------------------------------}
DEFINE GLOBAL pg_serial INTEGER DEFAULT 0;
LET pg_serial = p_serial;
END PROCEDURE;
CREATE PROCEDURE ins_table(...)
RETURNING ...;
...
DEFINE GLOBAL pg_serial INTEGER DEFAULT 0;
...
INSERT INTO table(serial_column, ...)
VALUES (0, ...)
-- get the serial value from the insert trigger
LET p_serial_column = pg_serial;
...
Two notes:
1) This has been submitted for the next issue of Tech Notes (watch the
skies!)
2) A future version of the engine (6.0, I believe) will provide access
to the entire SQLCA structure.
================
Dennis J. Pimple
Informix CSE / Denver
303-850-0210
>Thanks in advance.
>+---------------------------+--------------------------+
>| G.T.Madavan, | e-mail: gtmad@smi.mn.org |
>| Systems Migration Inc., | Phone : (612) 890-0040 |
>| Burnsville, MN, U.S.A | Fax : (612) 890-0966 |
>+---------------------------+--------------------------+
>Disclaimer: The author's opinions are his own, and not
>necessarily those of Systems Migration Inc.