> The following statement used to work.
> (pg 648 Helen's book)
>
> sql.add('SELECT GEN_ID(GEN_PK_CONSTITUENTS,1) AS RESULT FROM
> RDB$DATABASE');
>
> and did exactly what it was supposed to do - grab the new record ID
> created by the GEN_PK_CONSTITUENTS generator when the BI trigger
> fired.
>
> Now, I get the following error when I attempt to trace (in Delphi)
> thru this line of code:
> "SQL error code = -104
> Unexpected end of command'.
>
> Also, the trigger value is destroyed and the above statement causes

a

> new value to be created.
>
> Any ideas?
> Thx
> Sam Hunt

Sam,

I am a bit confused about what you have got here.

Firstly, your before insert trigger will not fire until you execute
an insert statement. Depending on the logic of that trigger, any
insert could increment the generator.

Secondly, there is nothing magical about RDB$DATABASE in this query.
It is simply a table that is guaranteed to contain a single record.

SELECT GEN_ID(GEN_PK_CONSTITUENTS,1) AS RESULT FROM MyTable

will do the exact same thing if MyTable only has one record.

Calling the gen_id function is what increments the record, so if
MyTable had 4 records, it would return you a dataset with the next
four generated values, eg

RESULT
======
5
6
7
8

So looking closer at the insert trigger, I hope the logic is
something like this

IF (NEW.ID IS NULL) THEN
BEGIN
NEW.ID = GEN_ID(GEN_PK_CONSTITUENTS,1);
END

If you have already used an ID in the insert statement, you do not
want it to generate another one. This is a good safe-guard, but I
personally do not rely on this sort of logic because I normally need
to know what the ID is.

Thirdly, looking at your Delphi code. Do you realise what you are
doing when you call sql.add?. Read the Delphi help on Add, it will
put the string at the bottom of the sql StringList. That means if you
call that line of code a second time, it will look like this

SELECT GEN_ID(GEN_PK_CONSTITUENTS,1) AS RESULT FROM RDB$DATABASE
SELECT GEN_ID(GEN_PK_CONSTITUENTS,1) AS RESULT FROM RDB$DATABASE

Which may explain the unexpected end of command. You could
alternatively run sql.clear first or convert it to sql.text := ''
syntax.

Adam