eeek...oh well what ever works.
Jeremy Coulter
> -----Original Message-----
> From: [EMAIL PROTECTED]
> [mailto:[EMAIL PROTECTED]]On Behalf Of Patrick Dunford
> Sent: 05 March, 2001 9:53 PM
> To: Multiple recipients of list database
> Subject: RE: [DUG-DB]: Getting unique ID through SQL
>
>
> No I have a better one... CREATE GENERATOR (or CREATE SEQUENCE)
> depending on
> your SQL dialect... Interbase uses Generator, Postgre uses Sequence, for
> example
>
> then execute a passthrough query SELECT NEXTVAL('sequencename') in Access
> which does the whole thing in one go
>
> > -----Original Message-----
> > From: [EMAIL PROTECTED]
> > [mailto:[EMAIL PROTECTED]]On Behalf Of Jeremy Coulter
> > Sent: Monday, 5 March 2001 21:42
> > To: Multiple recipients of list database
> > Subject: RE: [DUG-DB]: Getting unique ID through SQL
> >
> >
> > 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
> >
>
> ------------------------------------------------------------------
> ---------
> 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
