[Libreoffice-qa] Firebird - creating auto-increment fails via Tools - SQL

[Alex Thurgood]

Hi,

Since the GUI for interfacing with Firebird doesn't currently enable
creating a working autoincrement column (there is already an open bug
report for that), I've been following this Firebird FAQ :

http://www.firebirdfaq.org/faq29/


but am getting stuck at the passage below :

Now we create a trigger:

set term !! ;
CREATE TRIGGER T1_BI FOR T1
ACTIVE BEFORE INSERT POSITION 0
AS
BEGIN
if (NEW.ID is NULL) then NEW.ID = GEN_ID(GEN_T1_ID, 1);
END!!
set term ; !!

One thing to note is that the built-in Firebird support currently seems
to require that, when using Tools - SQL utility, you have to type in
each command separately and then execute them separately. In other
words, you don't seem to be able to type in several DDL commands,
separate them with colons, and have the statement execute correctly.

I have tried the above, both with and without the "set term" statements,
and when using "set term", I get an error about term not being
recognized, and without I get an error of the type below :

15: firebird_sdbc error:
*Dynamic SQL Error
*SQL error code = -104
*Unexpected end of command - line 5, column 54
caused by
'isc_dsql_prepare'

which probably refers to the presence of the semi-colon in the line
if (NEW.ID is NULL)...

So, I guess the question is : is this actually supposed to even work in
the current state of affairs ?

If it isn't, how are users supposed to create/set triggers for
autoincrement with the embedded firebird db ?


Alex