Patrick. Under SQLServer, there is a system Stored proc. called "LOCK" that
from memory will lock the record until you call unlock. I THINK this might
help you.
i.e. call the storedproc. LOCK do the select, then the update then
unlock....or better still do the select AND the update in one
Storedproc....this means only one connection
Jeremy Coulter
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Patrick Dunford
> Sent: 05 March, 2001 8:11 PM
> To: Multiple recipients of list database
> Subject: [DUG-DB]: Getting unique ID through SQL
>
>
> People will have seen my post on problems with PostgreSQL ODBC
> driver and MS
> Access 97.
>
> Access 97 has some problems when a record is added that contains a primary
> key field of type SERIAL. This has something to do with the fact that the
> value of the primary key is not actually generated until the
> record is sent
> to the server.
>
> It seems it is easiest for me to get the unique ID from the server myself
> and insert it into the record when Access creates it.
>
> In the realm of file based databases on a local machine it is easy to do
> this: store the unique variable into a special table, read it
> out, increment
> it and store it back. Very quick and there may only ever be one user.
>
> Things become different on an SQL server because there may be
> multiple users
> simultaneously accessing the database. Two SQL operations are required to
> retrieve the variable's value and update it: a SELECT and UPDATE.
> Depending
> on how fast your connection is, between the SELECT and UPDATE,
> someone else
> could have run the same SELECT and got the same value back. Then when both
> records are sent to the server with duplicate values in the same primary
> key, one will fail.
>
> What I need is some foolproof way of getting and updating the variable in
> one operation. Is it going to be an Int4 stored in a special table, or can
> it be a serial? Do I use a stored procedure or what? How do I get
> its value
> from Access?
>
> Whatever you think of Access, the alternative seems to be clunky PHP forms
> with lots of code behind them for data entry and editing.
>
> =======================================================================
> Patrick Dunford, Christchurch, NZ - http://pdunford.godzone.net.nz/
>
> Peter replied, Repent and be baptized, every one of you, in the
> name of Jesus Christ for the forgiveness of your sins. And you will
> receive the gift of the Holy Spirit. The promise is for you and
> your children and for all who are far offfor all whom the Lord our
> God will call.
> -- Acts 2:38
> http://www.heartlight.org/cgi-shl/todaysverse.cgi?day=20010304
> =======================================================================
> Created by Mail2Sig - http://pdunford.godzone.net.nz/software/mail2sig/
>
> ------------------------------------------------------------------
> ---------
> New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
> Website: http://www.delphi.org.nz
---------------------------------------------------------------------------
New Zealand Delphi Users group - Database List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
