You may be too late with your SEARCH command as another user may have instructed an insert command at the same time as you did, resulting in the wrong ID number.
John -----Original Message----- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Steve Aish Sent: Friday, 14 January 2005 10:56 a.m. To: [email protected] 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 --- 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
