----- Original Message -----
Sent: Friday, January 14, 2005 12:22
PM
Subject: RE: Re: [DUG] concurrent
Interbase Transactions
[Reply]
HI
Phil, et al ..
The
main point is that the IB/Firebird generator mechanism does not function
inside a transaction.
The
best way to hanlde it is to give yourself a bet both ways.
Use a
trigger like this, so that if you specify a pk value, it is supplied,
otherwise one is created.
create
trigger trMYTABLE for MYTABLE
active
before insert position 0
as
begin
if (new.id
is null) then new.id=gen_id(genANY,1);
end;
and in code I have a library routine(s) to get the
generator value.
function
GetNewID : integer;
begin
with
TIBQuery.create(nil) do try
.. //
connect to database etc
sql.add('select gen_id(genANY,1) newid from
rdb$database');
open;
result:=fieldbyname('newid').asInteger;
finally
free;
end;
end;
For those interested, the above query references the
rdb$database table, a system table with ever only one record. This is done to
satisfy the SQL92 query syntax when accessing the gen_id function - a built in
IB/Firebird (only) function.
HTH
Gary
At 11:26 on 14/01/2005 you wrote
>To :
[email protected]
>CC :
>From: Phil
Middlemiss, [EMAIL PROTECTED]
>Content Type:
text/plain
>Attached:
>
>Thanks to all who
replied. In the end I did the inevitable (Johns
>suggestion) and now
fetch a new ID first and include it in the INSERT
>operation rather than
have the trigger.
>
>Phil.
>----- Original
Message -----
>From: "Steve Aish"
<[EMAIL PROTECTED]>
>To:
<[email protected]>
>Sent: Friday, January
14, 2005 10:55 AM
>Subject: Re: [DUG]
concurrent Interbase Transactions
>
>
>> 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
>>
>>
>
>
>_______________________________________________
>Delphi mailing
list
>[email protected]
>http://ns3.123.co.nz/mailman/listinfo/delphi
>
Ref#: 41006
_______________________________________________
Delphi mailing
list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
