Firebird Auto_Increment [SOLVED]
lazarus / 16-Databases

----------------------------------------------------------------------
lennit · 2014-07-22 18:07

Hello,

I've got an issue with ID autoincrement generator. I searched the
forum and found this:
Code: [Select]IBConnection1.ExecuteDirect('CREATE GENERATOR
GEN_FIELDS_ID;');
  IBConnection1.ExecuteDirect('SET GENERATOR GEN_FIELDS_ID TO 0;');

  IBConnection1.ExecuteDirect('CREATE TRIGGER TR_FLD FOR FIELDS ACTIVE
BEFORE INSERT POSITION 0 AS '+
                              'BEGIN if (NEW.ID is NULL) then NEW.ID =
GEN_ID(GEN_FIELDS_ID, 1);END;');

I also put beforehand triggers in my database (using IBEasy+)
following this example:

Code: [Select]CREATE TRIGGER trgTTEST_BI_V3 for TTEST
active before insert position 0
as
begin
  if ((new.id is null) or (new.id = 0)) then
  begin
    new.id = gen_id( gidTest, 1 );
  end
end

The problem is somehow still the ID is not generated when i use the
DBNavigator. I get:

SQLQuery1: Field ID is required but not supplied.

What am I doing wrong?

----------------------------------------------------------------------
korba812 · 2014-07-22 18:33

Set 'Required' property to false.

eg.:
SQLQuery1.FieldByName('ID').Required := False;

----------------------------------------------------------------------
lennit · 2014-07-22 18:40

That solved my problem with the error code   However the database is
not updated in the end. I wonder already if to give up on Navigator
and make my own buttons XD

----------------------------------------------------------------------
exdatis · 2014-07-22 19:16

You can use a simple view to find next_id, eg:
create view vmy_table_id(next_id) as
select
  gen_id(your_generator, 1)
from
  rdb$database;

Now you can use beforePost event to set id:
eg.
if(my_tablemy_id.IsNull) then //new record
  //select query vmy_table_id

also:
http://www.firebirdsql.org/refdocs/langrefupd21-ddl-sequence.html

create view...
select
  next value for sequence_name
from
  rdb$database

----------------------------------------------------------------------
taazz · 2014-07-22 19:26

Quote from: lennit on July 22, 2014, 08:40:46 pmThat solved my problem
with the error code   However the database is not updated in the end.
I wonder already if to give up on Navigator and make my own buttons XD

If I remember correctly the check Code: [Select]if (NEW.ID is NULL)
then will be true only when the insert statement is something like
Code: [Select] insert into fields (Id,......) values (null,.....) if
for example the insert statement does not include the ID field at all
then it will always be false at least that is what I remember from a
few years when I had the same problem I think that that problem was
addressed in newer version of the firebird database but I do not
remember how it was addressed or at which version so better ask on a
firebird list for more information on that error.

PS. What the navigator has to do with the server?

----------------------------------------------------------------------
lennit · 2014-07-23 07:56

This is a sample database with a sample project. My problem with
Navigator is that i don't control the information sent to the
database, so when i try to post a new record all i get is a record
with null index i can reach in runtime and no information sent to the
database.
There must be some major thing i miss here, because i am sure this
same component works fine for everybody. I use Firebird 2.5.3 (64bit)

----------------------------------------------------------------------
rvk · 2014-07-23 11:38

Quote from: lennit on July 23, 2014, 09:56:46 amMy problem with
Navigator is that i don't control the information sent to the
database, so when i try to post a new record all i get is a record
with null index i can reach in runtime and no information sent to the
database.
Taking a (very quick) look at your program i'm missing two things.

The first is i'm missing the ApplyUpdates. Even when you do a post
(with the navigator) you need to do an Applyupdates on the changed
dataset to transfer the data to the database.

Second is the missing commit on the transaction. You have a default
transaction with caRollback, which means your changes would be rolled
back when you don't commit the transaction explicitly.

So you can do the following (with a button, at the end of your program
or after every post):

Code: [Select]  mainQ.ApplyUpdates;
  if IBC.Transaction.Active then
    IBC.Transaction.CommitRetaining;

@taazz: No, it is not needed to have the ID in the statement. NEW.ID
will still be NULL if it has not been defined. You can do an insert
with just one field value and the trigger should still assign an
increment number to ID. (It has been so since at least fb 1.5. I'm not
sure if you're experience is with an even older version )

----------------------------------------------------------------------
lennit · 2014-07-23 13:55

Thank you all for the replies! I learned a lot and actually made it
work  . Especially to rvk: thank you

----------------------------------------------------------------------
taazz · 2014-07-23 13:59

Quote from: rvk on July 23, 2014, 01:38:57 pm@taazz: No, it is not
needed to have the ID in the statement. NEW.ID will still be NULL if
it has not been defined. You can do an insert with just one field
value and the trigger should still assign an increment number to ID.
(It has been so since at least fb 1.5. I'm not sure if you're
experience is with an even older version  )

My experience starts with the first interbase 6.x public releases and
from there to firebird 1.0 and later versions. So it might be that old
yes I do not keep a time table of bugs.