[solved] IBConnection trigger
lazarus / 16-Databases

----------------------------------------------------------------------
SlowCoder · 2012-05-19 12:58

Hi all,

I try to create a trigger for auto-increment a primary key. Using
FlameRobin I can create the trigger, but how do I create a trigger in
program?

I tried these SQL statements (which are working in FlameRobin):
Code: [Select]DM1.IBConnection1.ExecuteDirect('CREATE GENERATOR
DocIdGen;');
DM1.IBConnection1.ExecuteDirect('SET GENERATOR DocIdGen TO 0;');
DM1.IBConnection1.ExecuteDirect('set term !! ;');
DM1.IBConnection1.ExecuteDirect('CREATE TRIGGER trgDOCUMENTS for
DOCUMENTS '+
                                              'active before insert
position 0 '+
                                              'as '+
                                              'begin '+
                                                'if ((new.id is null)
or (new.id = 0)) then '+
                                                'begin '+
                                                  'new.id = gen_id(
DocIdGen, 1 ); '+
                                                  'end '+
                                                'end!!');
DM1.IBConnection1.ExecuteDirect('set term ; !!');   

I get error: IBConnection1 : PrepareStatement
                 -Dynamic SQL error
                 -SQL error code = 104
                 -Token unknown - line 1,column 5
                 -term

How can I create this trigger without being able to 'set term'?

Greetings

Using Laz 0.9.30.4, FPC 2.6.0, FireBird (embedded) 2.5, winXP-sp3

----------------------------------------------------------------------
BigChimp · 2012-05-19 13:23

Interesting.

Set term is used in isql and other tools so you can nest one statement
(e.g. the sql in an sp or trigger) inside another. Firebird normally
expects ; to close off statements; the set term temporarily changes
this so you can close off the "inner" statement with ;.

Isql then detects the end of the outer statement using the temporary
characters you specified in the set term command.

Presumably, this kind of parsing is not necessary when using the
client API... so let's try without it.

I haven't tried it, but you could modify it to this (air code, not
tested):
Code: [Select]DM1.IBConnection1.ExecuteDirect('CREATE GENERATOR
DocIdGen;');
DM1.IBConnection1.ExecuteDirect('SET GENERATOR DocIdGen TO 0;');
//let's leave this out:
//DM1.IBConnection1.ExecuteDirect('set term !! ;');
// I stuck a ; at the end, hope that is the right one to choose...
DM1.IBConnection1.ExecuteDirect('CREATE TRIGGER trgDOCUMENTS for
DOCUMENTS '+
                                              'active before insert
position 0 '+
                                              'as '+
                                              'begin '+
                                                'if ((new.id is null)
or (new.id = 0)) then '+
                                                'begin '+
                                                  'new.id = gen_id(
DocIdGen, 1 ); '+
                                                  'end '+
                                                'end;');
// note that the ending set term is omitted.
// make sure you commit the transaction etc as usual.

----------------------------------------------------------------------
ludob · 2012-05-19 13:52

You don't need to bother with terminating characters since
ExecuteDirect or TSQLQuery.ExecSql don't need one to end a query.
Terminating character are only for console input.
BigChimps suggestion works fine.

----------------------------------------------------------------------
SlowCoder · 2012-05-19 14:16

Thanks BigChimp and Ludob,

It works as suggested!



Greetings