One solution I have seen used is as follows:
When you go to create a new record insert a blank record immediately
(preferably with a autonumber field as the key or some other method that
keeps trying until it succeeds) and have a status field in your record
defaulted to "cancelled". The key returned is then used later on when
posting then new record back to the server as an edit. If the user abandons
the new transaction then it status remains cancelled in the DB until such
time as you decide to get rid of the record (very rear occurrence). The
other benifit to this method is of course you get a Key or Transaction
Number displayed immmediately in your client application when creating a new
record.
-----Original Message-----
From: [EMAIL PROTECTED]
[mailto:[EMAIL PROTECTED]]On Behalf Of Patrick Dunford
Sent: Monday, March 05, 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
