How do you return a value from a Firebird 2.5 Execute Block fin the Insert SQL?

[gregors613]

I have Firebird 2.5 with a table

Code: Select all

CREATE TABLE TABLE01 (
  FIELD01  INTEGER,
  FIELD02  INTEGER
);


CREATE SEQUENCE GEN_TABLE01_ID;

ALTER SEQUENCE GEN_TABLE01_ID RESTART WITH 0;

SET TERM ^ ;

CREATE TRIGGER TABLE01_BI FOR TABLE01
ACTIVE BEFORE  POSITION 0
AS
BEGIN
    if (NEW.FIELD01  is NULL) then BEGIN
        NEW.FIELD01  = NEXT VALUE FOR GEN_TABLE01_ID;
    END END^

SET TERM ; ^

My TUniQuery has the SQL field set to

Code: Select all

select * from TABLE01

and the insert sql set to

Code: Select all

execute block( FIELD02 Integer = :FIELD02 )
returns ( FIELD01 Integer )
as
begin
  insert into table01 ( FIELD02 ) VALUES ( :FIELD02 ) RETURNING FIELD01 INTO :FIELD01;
  SUSPEND;
END

but when doing an insert I can't seem to figure out how to retrieve the FIELD01. Ideally I would like this to with like the DMLRefresh so I could use the table in a dbgrid and see the FIELD01 value immediately when inserting a record. Any help would be appreciated

[ZEuS]

In order to obtain the key field value immediately after inserting a record, you should change the insert SQL like the following:

Code: Select all

INSERT INTO TABLE01(FIELD02) VALUES(:FIELD02) RETURNING FIELD01

There is no need to use any output parameters in the RETURNING clause, because in this case the field value will be obtained automatically.

[gregors613]

Sure, for a simple query, but I have some complex business logic that needs more than a one line SQL statement, hence the execute block. This is a greatly simplified example nothing like the real code other than the basic format of requiring a return value from the execute block.

I can't make the change you suggested in an execute block, it is illegal syntax



Engine Error (code = 335544569):
Dynamic SQL Error.
SQL error code = -104.
Unexpected end of command - line 5, column 58.

SQL Error (code = -104):
Invalid token.

The heart of this question is if I use the execute block as submitted earlier, how do I access the return value FIELD01 when inserting data?

[ZEuS]

In UniDAC, to immediately retrieve any returned data from TUniQuery update SQLs, you have to use output parameters (like :PARAM_NAME) in SQL-statements.
Commonly, you can use parametrized queries in the TUniQuery.SQLInsert property. In this case, names of the parameters should be the same as field names. In order to return OUT params back to the dataset, you should set the TUniQuery.Options.ReturnParam option to True.
But, the problem is that the EXECUTE BLOCK syntax does not allow to use parameters in the RETURNS clause, it always returns data as a recordset(see http://www.firebirdsql.org/refdocs/lang ... block.html for more details). So, you do not have an ability to automatically retrieve the key field value when using an EXECUTE BLOCK statement in the TUniQuery.SQLInsert property.