The way I handle this is to insert the record and then immediately do a
search for the record I just inserted. This will give you the value
just inserted.
e.g.
insert into debtor_trans (accountnumber, amount, transdate, ...)
trigger fires and fills in the unique seq_no
select max(seq_no) from debtor_trans where accountnumber =
accountnumber and amount = amount and transdate ...
this will return the seq_no from the record you just inserted.
I don't know if this is the best or most efficient way of doing this
but it has always worked for me.
Steve
>>> [EMAIL PROTECTED] 14/01/2005 10:16:37 a.m. >>>
MessageI was kind of hoping to avoid that, but it's not a biggie. Is
there no way to fetch the record that has just been added within a
transaction?
Cheers,
Phil.
----- Original Message -----
From: JC
To: 'NZ Borland Developers Group - Delphi List'
Sent: Friday, January 14, 2005 9:27 AM
Subject: RE: [DUG] concurrent Interbase Transactions
Hi Phil
It might be safer to have the generator giving you a new unique
number (on request, not in a trigger) and to use and insert that with
the data in the ExeQuery.
As you indicated, when implemented in a trigger then your fetch, to
get this value, may come too late and you end up with somebody else's
number.
Cheers,
John
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED] On Behalf Of Phil Middlemiss
Sent: Friday, 14 January 2005 8:42 a.m.
To: NZ Borland Developers Group - Delphi List
Subject: [DUG] concurrent Interbase Transactions
Hi everyone - hope you all had a good break. Looks like summer has
finally arrived (now that everyone's back at work)! Anyway...
I'm using Firebird (and transactions) for the first time. I'm
connecting to a remote database via the Firebird Server with no
problems, but I have a question about transactions:
When I insert a new record, I use a trigger and a generator to
generate a new unique ID for the new record - I also query the generator
to see what the new value is that was returned by the trigger. The code
snippet is as follows:
orderTransaction.Active := True;
with orderQuery do //insert the record
begin
ExecQuery;
with IBCurrentGeneratorValueQuery do // this query fetches
the current value of the generator
try
Transaction := orderTransaction; // share the same
transaction as the order
Open;
result:=FieldByName('newID').asInteger; // get the
generator value
except
result:=-1;
end;
orderTransaction.Commit;
end;
The question is, if multiple clients access the database server at
the same time, will IBCurrentGeneratorValueQuery still return the
correct value, or if another concurrent transaction on another thread
increments the generator, will this query return the wrong value?
When I was using MySQL (with PHP) I could use mysql_insert_id() to
query the new key value. I guess that if I could get hold of the newly
inserted row I could just get the field value, but the same concurrency
issue applies and I'm not sure how to get the record I just inserted.
Cheers,
Phil.
---
Incoming mail is certified Virus Free.
Checked by AVG anti-virus system (http://www.grisoft.com).
Version: 6.0.832 / Virus Database: 566 - Release Date: 10/01/2005
------------------------------------------------------------------------------
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
