Horton
Menu

Thread

  1. Getting unique ID through SQL

    Patrick Dunford <dunfordsoft@clear.net.nz> — 2001-03-05T07:11:28Z

    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 off—for 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/

  2. Re: Getting unique ID through SQL

    Justin Clift <aa2@bigpond.net.au> — 2001-03-05T10:39:09Z

    Hi Patrick,

With PostgreSQL, I do this inside PL/PGSQL functions (but I'll do it
outside a function here to make it simpler) :

Lets say you have :

foobar=# create table demonstration (barfoo serial, data varchar(10));
NOTICE:  CREATE TABLE will create implicit sequence
'demonstration_barfoo_seq' for SERIAL column 'demonstration.barfoo'
NOTICE:  CREATE TABLE/UNIQUE will create implicit index
'demonstration_barfoo_key' for table 'demonstration'
CREATE
foobar=# \d demonstration
                                Table "demonstration"
 Attribute |    Type     |                          Modifier
-----------+-------------+------------------------------------------------------------
 barfoo    | integer     | not null default
nextval('demonstration_barfoo_seq'::text)
 data      | varchar(10) |
Index: demonstration_barfoo_key
 
foobar=#

The way I insert data in a scalable manner is :

foobar=# select nextval('demonstration_barfoo_seq');     /* Put this
returned value in a variable */
 nextval
---------
       1
(1 row)
 
foobar=# insert into demonstration (barfoo, data) values (1, 'Some
data');  /* Insert the data using the previously generated serial number
*/ 
INSERT 28776302 1
foobar=#

Pretty simple eh?  No two clients can get the same value, and therefore
there's no conflict.  It's even transaction safe, as rolling back a
transaction won't let the same value be generated again.  This does mean
you will get gaps in the sequence numbering after a while, but for my
applications that's not a problem.

Regards and best wishes,

Justin Clift
Database Administrator


Patrick Dunford wrote:
> 
> 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 off-for 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/
> 
> ---------------------------(end of broadcast)---------------------------
> TIP 3: if posting/reading through Usenet, please send an appropriate
> subscribe-nomail command to majordomo@postgresql.org so that your
> message can get through to the mailing list cleanly

  3. CORBA and PG

    Franck Martin <franck@sopac.org> — 2001-03-05T11:48:00Z

    Does anyone has pointers on CORBA and PostgreSQL?

What is the story ?

Cheers...
Franck@sopac.org

  4. Re: CORBA and PG

    Peter T Mount <peter@retep.org.uk> — 2001-03-05T15:51:45Z

    Quoting Franck Martin <franck@sopac.org>:

> Does anyone has pointers on CORBA and PostgreSQL?
> 
> What is the story ?

There's some old stubs for one of the orbs somewhere in the source (C/C++)

Also the old JDBC/Corba example is still there 
(src/interfaces/jdbc/example/corba)

Peter


-- 
Peter Mount peter@retep.org.uk
PostgreSQL JDBC Driver: http://www.retep.org.uk/postgres/
RetepPDF PDF library for Java: http://www.retep.org.uk/pdf/