AutoInc (Firebird)

[signals]

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]

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

[mdaems]

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

[sfxcc]

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.

[guidoaerts]

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]

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.

[sabrespectre]

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]

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]

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]

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.

[mdaems]

andrevanzuydam,

What's the patch code?

Mark

[johnmfisk]

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