Welcome to the world of C/S programming. You will need to use a
transaction:
TDatabase.StartTransaction;
try
TQuery.SQL.Text := 'update singleelementtable set somefield=somefield+1';
TQuery.ExecSQL;
TQuery.SQL.Text := 'select somefield from singleelementtable';
TQuery.Open;
try
SequentialOrderNumber := TQuery.Fields[0].AsInteger;
finally
TQuery.Close;
end;
// Insert your order here.
TDatabase.Commit;
except
TDatabase.Rollback;
raise;
end;
In C/S, you do not normally request an exclusive lock. The first query
increments the field, and as the transaction is still pending, the server
will lock the record. If the record is already locked (because another
process is in the middle of a transaction), the server will block until the
lock is released. The second query reads the value. You could pass both
queries in one call to TQuery.Open, but I have chosen to illustrate it this
way, so as to make it clearer what is happening.
The transaction will also guarantee that if for some reason the order
insertion fails, the number is not incremented (because the whole
transaction is rolled backed).
Dennis.
> -----Original Message-----
> From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED]]On
> Behalf Of Terry Johnson
> Sent: Friday, 11 February 2000 14:08
> To: Multiple recipients of list delphi
> Subject: [DUG]: A simple SQL question...
>
>
> Suppose you are wanting to insert an order into an order database. The
> first thing you need is a sequential order number.
>
> When working with tables, you could use a single element
> database that was
> accessed exclusively, so that an
> open-read-increment-write-close procedure
> would handle it nicely.
>
> In migrating to an ADO/ODBC SQL interface, how would you
> achieve the same
> thing? The TQuery does not support the exclusive property of
> the TTable,
> and the BDE's Local SQL is limited in scope...
>
> I'm sure there's an easy answer to this, but my brain is
> starting to melt.
>
> Cheers,
> Terry
> --------------------------------------------------------------
> -------------
> New Zealand Delphi Users group - Delphi List -
> [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
>
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
