AutoInc (Firebird)

Forum related to Firebird

Moderators: gto, cipto_kh, EgonHugeist

signals
Fresh Boarder
Fresh Boarder
Posts: 3
Joined: 06.02.2010, 13:30

AutoInc (Firebird)

Postby signals » 25.04.2010, 08:55

I've been trying to post records to a table and so far it has
not worked.

The problem is the autoincrement field is not being updated at
record insert time, and remains nil. Checked the table component
and found that in all case, the "AutoGenerateValue" for the field
is default set to "arNone", when it should be "arAutoInc".

After setting the AutoGenerateValue to arAutoInc, the autoincrement
field still does not update, so at the time of posting the newly inserted
record, the error displayed is still

Field 'UNIQUE ID' is required, but not supplied.

Any ideas guys? This seems to be a simple problem but has me
stumped.

guidoaerts
Senior Boarder
Senior Boarder
Posts: 93
Joined: 01.07.2009, 16:07

Postby guidoaerts » 28.04.2010, 19:27

signals,
you should give some more information. what is the table definition, how do you insert, which platform, what versions?...
guido

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 01.05.2010, 21:10

Have you tried to set the required property of the field to false after opening the query?

The autoincrement stuff is tricky indeed. In mysql this can be handled smoothly as there the components can just decide from table metadata that a field is an autoincrement. Firebird doesn't provide an easy way to check if a field is an autoincrement, so zeoslib only sees the field is mandatory and defaults the required property to true on Open.

Mark
Image

sfxcc
Senior Boarder
Senior Boarder
Posts: 95
Joined: 13.05.2010, 18:48

Postby sfxcc » 16.05.2010, 13:38

Im a beginer on fb but i think if u create a stored procedure a triger on insert some like that when u insert a record it trigers then u can get the max value of the field something like field = field.max()+1 u get a unique number automatic.

Sorry for this, i cant explain because im a beginner so i dont undestand of trigers and stored proc.
hope this help u a bit. :prog2:
where's the code ..

guidoaerts
Senior Boarder
Senior Boarder
Posts: 93
Joined: 01.07.2009, 16:07

Postby guidoaerts » 17.05.2010, 11:15

autoincrement works fine with fb and zeos

in firebird you create a generator and a trigger:
for e.g. table 'acties' and autoincrement field 'idnummer' (with unique index):
(SET TERM ^; )
CREATE GENERATOR "GEN_ACTIES_IDNUMMER"^
SET GENERATOR "GEN_ACTIES_IDNUMMER" TO 0^


CREATE TRIGGER "TRIG_ACTIES_BI" FOR "ACTIES"
BEFORE INSERT
AS
BEGIN
IF(NEW."IDNUMMER" IS NULL) THEN NEW."IDNUMMER" = GEN_ID("GEN_ACTIES_IDNUMMER",1);
END^
(SET TERM;^)

in your insert sql you can pass null for the autoincrement field, or if you supply values for named fields, just omit the autoincrement field.

guido

sfxcc
Senior Boarder
Senior Boarder
Posts: 95
Joined: 13.05.2010, 18:48

Generator and Sequence

Postby sfxcc » 17.05.2010, 15:36

I dont know the diference between generator and sequence by now.

But i think if u want to reuse again the last id value inserted u can use a trigger with max and not generator or sequencer.

For example generator is 9 u insert a record it stays 10.
For some reason u want to delete the record.
U cant reuse that number with a generator (except forced reseting gen value (not recommended this) )

If u use the max() of field in function is better than the generator because the max value still being 9.

so u can do something like : new.id = field.max()+1

remember to lock for no insert before u
as begin
table.lock;
new.id = field.max()+1;
table.unlock;
end;

So depends what u doing if u need to reuse last number automatic u can use max() , if u dont need to reuse lst deleted use the generator is much faster.

Hope this help a bit. :idea:

sabrespectre
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 17.05.2010, 16:09

Postby sabrespectre » 17.05.2010, 16:15

You should uncheck "required field" or similar ...... if this field is a primary key, a required field is checked.... autoincrement works on server side, so the application checks for a primary key, giving the error you describle.
Unchecking this, auto increment works.

exdatis
Fresh Boarder
Fresh Boarder
Posts: 16
Joined: 18.05.2010, 06:27

Postby exdatis » 18.05.2010, 06:36

in my opinion, also:
// save position
var
curr_rec : Tbookmark;
begin
curr_rec:= DataSet.GetBookmark();
DataSet.DisableControls();
DataSet.Refresh();
DataSet.GoToBookmark(curr_rec);
DataSet.EnableControls()
end;

guidoaerts
Senior Boarder
Senior Boarder
Posts: 93
Joined: 01.07.2009, 16:07

Postby guidoaerts » 18.05.2010, 10:09

that's right, as the ZTable component doesn't know the value of the autoincrement field of the record that was added, is has to refresh the data to retrieve the inserted value. the new record has to be posted without a value for the autoincrement field when working with a TDBGrid, for example.

andrevanzuydam
Zeos Dev Team
Zeos Dev Team
Posts: 32
Joined: 22.10.2005, 08:53
Location: Bloemfontein
Contact:

Postby andrevanzuydam » 04.08.2010, 16:01

I have found after some considerable debugging that the default fields in Firebird will only work after commenting out the inherited line in
procedure TZAbstractDataset.InternalPost;

This seems to fix up the auto increment and default value fields in the system.
Image
Nothing is impossible!

User avatar
mdaems
Zeos Project Manager
Zeos Project Manager
Posts: 2766
Joined: 20.09.2005, 15:28
Location: Brussels, Belgium
Contact:

Postby mdaems » 30.08.2010, 00:19

andrevanzuydam,

What's the patch code?

Mark
Image

johnmfisk
Fresh Boarder
Fresh Boarder
Posts: 1
Joined: 07.02.2012, 21:31

Postby johnmfisk » 07.02.2012, 22:16

I don't know if anyone is still reading this thread, but I found a work about that seems to work relatively well.

I ran into this problem a while back using Delphi 7 + FB 2.1 embedded + ZeosLIB 6.6.6-STABLE. Inserts into tables with auto-incremented primary keys (using FB generators + triggers) returned error messages because the primary key field was NULL.

The solution was to programmatically assign a placeholder value to the key field prior to posting, which would then be updated by the trigger. In Delphi, it entails defining an event handler for the BeforePost event. Click on the appropriate TZQuery or TZTable entity, click on the Events tab, then look for the BeforePost event. Double click this to edit the event. Something similar to:

procedure QueryContactTableBeforePost (Sender: TObject);
begin
with QueryContactTable do
begin
if FieldsByName('CONTACT_ID').IsNull then
FieldsByName('CONTACT_ID').Value := 0;
end;
end;

Warning: I'm doing this off the top of my head - double check the code. The logic should work.

Good luck!
Jon Jon


Return to “Firebird”

Who is online

Users browsing this forum: No registered users and 1 guest