IBX-Problem with Generators used as AutoID
Bernhard Hartl
I have got a probelm with AutoID's with Interbase.
I use a BeforeInsert-Trigger to Increment the generator and set the ID to
the related Dataset.
This works without any problems as long I do not need the ID-Value in Delphi
for Master-Detail-Relationships aso.
When I read out the generator in delphi in the AfterInsert-Event the
return-Value is the ID before the Increment.
I use IB5.6 and Delphi5 with the latest IBX-Components
Transaction-options read_committed, rec_version, nowait
Example:
TabUser with the Field UserID that is a AutoID-Value
create trigger trig_gen_userid for TABUSER active before insert position 0
as
begin
new.UserID = gen_id(GEN_UserID, 1);
end
To get the ID I use the following Query in AfterInsert-EventProcedure:
SELECT DISTINCT Gen_ID(GEN_UserID,0) FROM TabHelp
Result Value is 'Gen_ID' but not the value set by the trigger but the old
value.
TabHelp is a Table with only one Line and one row that is only used for
generator-queries as help-Table.
I have used a database trigger to increment the ID -Generator and not the
AfterInsert-Event in Delphi as I want to be able to insert new datasets with
tools like the SQL-Explorer or Marathon. This works fine but this problem
with reading the value of the Trigger a short time after inserting the
dataset.
The other problem caused by the construction above is when two users add a
dataset at the same time the read-back query may go wrong.
Is there a smarter way how to get the Auto-ID generated by the IB-Server
directly?
Thank you
Bernhard Hartl
Finze und Wagner GmbH
Piracher Str. 76
84489 Burghausen
GERMANY
Tel.: +49 / 08677 / 884-780
Fax.: +49 / 08677 / 884-777
Bernhar...@fi-wa.com
http://www.fi-wa.com
Jeff Overcash (TeamB)
just inserted so that it can re read the record to get the changes the server
made. If you have a secondary key known on the client side then make that the
where clause of the RefreshSQL and set ForcedRefresh to true. If you do not
have a secondary key then there is no way the client side can requery that
single record. In that case you need to call the generator by hand in either
the BeforePost or the OnNewRecord event. In that case either get it through SQL
like
select gen_id(<mygen>, 1) NEXTID from RDB$DATABASE
or make a stored procedure that returns the next generated value and call it to
retrieve the next value.
Bernhard Hartl wrote:
>
> Hallo,
>
> I have got a probelm with AutoID's with Interbase.
> I use a BeforeInsert-Trigger to Increment the generator and set the ID to
> the related Dataset.
> This works without any problems as long I do not need the ID-Value in Delphi
> for Master-Detail-Relationships aso.
> When I read out the generator in delphi in the AfterInsert-Event the
> return-Value is the ID before the Increment.
>
--
Jeff Overcash (TeamB)
(Please do not email me directly unless asked. Thank You)
And so I patrol in the valley of the shadow of the tricolor
I must fear evil. For I am but mortal and mortals can only die.
Asking questions, pleading answers from the nameless
faceless watchers that stalk the carpeted corridors of Whitehall.
(Fish)
Bernhard Hartl
I know that the client does not know the Dataset it has inserted and I know
too that GenID(..,1) increments a generator.
The problem is that when you use a BeforeInsert Trigger to Increment the
GenID you can not read the new GeneratorValue but get the old one in
AfterInsert Event of TIBQuery ....
IB-SERVER:
create trigger trig_gen_userid for TABUSER active before insert position 0
as
begin
new.UserID = gen_id(GEN_UserID, 1);
end
1.) DELPHI:
call IBQuery.Insert;
2.) IBSERVER:
Inserts a new dataset to TabUser, Increments the GEN_UserID, sets the
UserID-Value of the new Dataset
3.) DELPHI:
IBQuery.AfterInsert:
SELECT Gen_ID(GEN_UserID,0) FROM RDB$DATABASE
returns the OLD VALUE, NOT the NEW VALUE generated by the according
Trigger-Event in the IB-Server
I just want to read the Generator after it has been incremented by the
BeforeInsert-Trigger that's all.
Is that a bug?
How will the Timing of Insert - when is the INSERT-Command sent to the
IBServer by IBQuery?
Nothing about that is written in the IB6 DevGuide "Working with Queries"
I just want to know how the Query works - (basically)!?!
Have spent about one day to understand the TIBQuery Source but I still do
not understand many things because it is quite complex.
Thanks
Bernhard Hartl
Finze und Wagner GmbH
Piracher Str. 76
84489 Burghausen
GERMANY
Tel.: +49 / 08677 / 884-780
Fax.: +49 / 08677 / 884-777
Bernhar...@fi-wa.com
http://www.fi-wa.com
Jeff Overcash (TeamB) <over...@onramp.net> schrieb in im Newsbeitrag:
393E4C57...@onramp.net...
Jeff Overcash (TeamB)
to somehow mark that you are Inserting a record. The Generator is not changed
on the DB end until after you post the change (the trigger is not fired until
the Post event). Please note that your method can fail in a multiuser
environment.
If you have a secondary key on your table that is known on the client side use
that to refresh the trigger changed values. If you do not and you need the
generated value after the insert, then do not use triggers and instead use a
stored procedure or a SQL statement to get the next generated value and fill it
out on the client side before Posting the changes to the server.
I said you method could change so let me explain. Take the instance where two
people are inserting at the same time and post at nearly identical times (in
systems that do batch processing this can be a common situation). Now since
generators exist outside of transactions, User1 can insert, User2 insert, User1
Post, User2 Post (near simultaneous), now before you can call the generator
again in your AfterPost for User1 it might have incremented for User2's Post
(due to network delays this is not unreasonable) and you actually get the
generator value for User2's Insert and not yours.
The event order is something like
(User Inserts)
BeforeInsert
(User fills out and moves off the record)
AfterInsert
(User Posts)
BeforePost
(Triggers fire)
AfterPost
--