Firebird Auto_Increment [SOLVED]

[lennit]

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]

Set 'Required' property to false.

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

[lennit]

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]

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]

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

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]

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]

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.

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]

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

[taazz]

@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.