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
>> http://ns3.123.co.nz/mailman/listinfo/delphi
>> _______________________________________________
>> Delphi mailing list
>> http://ns3.123.co.nz/mailman/listinfo/delphi
>>
>>
>
>
>_______________________________________________
>Delphi mailing list
>http://ns3.123.co.nz/mailman/listinfo/delphi
>
Ref#: 41006
_______________________________________________
Delphi mailing list
[email protected]
http://ns3.123.co.nz/mailman/listinfo/delphi
