Re: Returning serial value from SP, not using SQLCA
In article <4tj0ev$aof@cssun.mathcs.emory.edu>, ssouthwe@pcshs.com says...
>
>Does anyone know if there is a way to return, from a stored procedure which
>inserts a row with a serial column, the VALUE of the serial number, without
>doing a second select? sqlca.sqlerrd[2] should contain the value, but the
sqlca
>structure is unknown to stored procedures. They use the on exception statement
>to return dbstatuses, but you can only return the sqlcode and isamerror, and a
>good insert (sqlcode = 0) won't raise an error.
>
>This procedure is being executed by a Delphi app, which needs to display the
>generated serial value on the user's form, and which cannot access the SQLCA
>structure directly. We were relying on being able to get the value as the
>return value of the SP.
>
>--
> "And has thou slain the jabberwock? . . ."
>
>+-----------------------------+---------------------+
>| Schuyler L. Southwell | PCS Health Systems |
>| Lead Database Administrator | 9501 E. Shea Blvd |
>+-----------------------------+ Scottsdale, AZ |
>| ssouthwe@pcshs.com | 85260 6719 |
>+-----------------------------+---------------------+
>| Voice: 602-661-3071 | FAX: 606-391-4850 |
>+-----------------------------+---------------------+
For Online v. 6.0 or later you can write
DEFINE serial_val INT;
...
INSERT INTO table_t (serial_col, ...) VALUES (0, ...); LET serial_val = DBINFO('sqlca.sqlerrd1');
RETURN serial_val;
For previous Online's version you must create trigger like this
CREATE TRIGGER on_table_t INSERT ON table_t REFERENCING NEW AS rec
FOR EACH ROW (EXECUTE PROCEDURE set_serial (rec.serial_col));
CREATE PROCEDURE set_serial (index INT)
DEFINE GLOBAL last_serial INT DEFAULT 0; LET last_serial = index;
END PROCEDURE;
and write next code
DEFINE GLOBAL last_serial INT DEFAULT 0;
DEFINE serial_val INT;
...
INSERT INTO table_t (serial_col, ...) VALUES (0, ...); LET serial_val = last_serial;
RETURN serial_val;
Svikhnoushin Andrew.