<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META HTTP-EQUIV="Content-Type" CONTENT="text/html; charset=us-ascii">
<TITLE>Message</TITLE>
<META content="MSHTML 6.00.2900.2523" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff size=2>Hi
Phil</FONT></SPAN></DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff size=2>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.</FONT></SPAN></DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff
size=2></FONT></SPAN> </DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff
size=2>Cheers,</FONT></SPAN></DIV>
<DIV><SPAN class=015512220-13012005><FONT face=Arial color=#0000ff
size=2>John</FONT></SPAN></DIV>
<BLOCKQUOTE dir=ltr style="MARGIN-RIGHT: 0px">
<DIV></DIV>
<DIV class=OutlookMessageHeader lang=en-us dir=ltr align=left><FONT
face=Tahoma size=2>-----Original Message-----<BR><B>From:</B>
delphi-bounces@ns3.123.co.nz [mailto:delphi-bounces@ns3.123.co.nz] <B>On
Behalf Of </B>Phil Middlemiss<BR><B>Sent:</B> Friday, 14 January 2005 8:42
a.m.<BR><B>To:</B> NZ Borland Developers Group - Delphi
List<BR><B>Subject:</B> [DUG] concurrent Interbase
Transactions<BR><BR></FONT></DIV>
<DIV><FONT face=Arial size=2>Hi everyone - hope you all had a good break.
Looks like summer has finally arrived (now that everyone's back at work)!
Anyway...</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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:</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face="Courier New" size=1>
orderTransaction.Active := True;<BR> <STRONG>with</STRONG>
orderQuery <STRONG>do</STRONG> <EM><FONT color=#008000>//insert the
record</FONT><BR></EM>
<STRONG>begin</STRONG><BR>
ExecQuery;<BR> <STRONG>with</STRONG>
IBCurrentGeneratorValueQuery <STRONG>do</STRONG> <FONT color=#008000><EM>//
this query fetches the current value of the
generator</EM></FONT><BR>
<STRONG>try</STRONG><BR> Transaction
:= orderTransaction; <FONT color=#008000><EM>// share the same
transaction as the
order</EM></FONT><BR>
Open;<BR> result:=FieldByName(<FONT
color=#000080>'newID'</FONT>).asInteger; <EM><FONT color=#008000>// get
the generator value</FONT></EM><BR>
<STRONG>except</STRONG><BR>
result:=-1;<BR>
<STRONG>end</STRONG>;<BR>
orderTransaction.Commit;<BR>
<STRONG>end</STRONG>;<BR></FONT></DIV>
<DIV><FONT face=Arial size=2>The question is, if multiple clients access the
database server at the same time, will <FONT face="Courier New"
size=1>IBCurrentGeneratorValueQuery </FONT>still return the correct value, or
if another concurrent transaction on another thread increments the generator,
will this query return the wrong value?</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>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.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT> </DIV>
<DIV><FONT face=Arial size=2>Cheers,</FONT></DIV>
<DIV><FONT face=Arial size=2>Phil.</DIV></FONT><BR>
<P><FONT size=2>---<BR>Incoming mail is certified Virus Free.<BR>Checked by
AVG anti-virus system (http://www.grisoft.com).<BR>Version: 6.0.832 / Virus
Database: 566 - Release Date: 10/01/2005<BR></FONT></P>
<P><FONT face=Arial size=2></FONT></P></BLOCKQUOTE></BODY></HTML>