UniQuery - stored procedure with INSERT INTO..RETURNING

[stevel]

Using Firebird 2.5 with UniQuery component with SQLInsert property set to a stored procedure.

After an insert, I need to get the new rowid which is autogenerated by a Before Insert trigger on the table.

A. The SQLInsert property has:

Code: Select all

EXECUTE PROCEDURE TBLTEST_I(:TESTID, :TESTNM)  RETURNING_VALUES :NEWID

B. Insert Stored procedure:

Code: Select all

CREATE PROCEDURE TBLTEST_I(
  TESTID INTEGER,
  TESTNM VARCHAR(20) CHARACTER SET UTF8 COLLATE UTF8)
RETURNS(
  NEWID INTEGER)
AS
BEGIN
  INSERT INTO TBLTEST (
    TESTID,
    TESTNM)
  VALUES (
    :TESTID,
    :TESTNM) RETURNING TESTID INTO :NEWID;
END

1. Is the syntax for item A supported?

2. In code, how can I get the value returned by the stored procedure?

[stevel]

I found a way to handle my issue currently:

I am using a query ie UniQuery component to get the value of the generator in the OnBeforeInsert event:

Code: Select all

SELECT NEXT VALUE FOR mygenerator FROM RDB$DATABASE

I am then supplying this value to the INSERT stored procedure.

This way the inserted record has the new key value (before and) after the insert operation. Key conflict issue (because another user has used the generated value) is handled inside an exception handler - query the new generator value again use it for record insertion.


Thank you.

[AndreyZ]

Also you can use the solution that we described here: http://www.devart.com/forums/viewtopic.php?t=19905