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 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/
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_keyfoobar=#
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:
Show quoted text
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
Does anyone has pointers on CORBA and PostgreSQL?
What is the story ?
Cheers...
Franck@sopac.org
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/
I guess these stubs are for accessing PG as a corba server...
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/
<http://www.sopac.org/> Support FMaps: http://fmaps.sourceforge.net/
<http://fmaps.sourceforge.net/>
This e-mail is intended for its addresses only. Do not forward this e-mail
without approval. The views expressed in this e-mail may not be necessarily
the views of SOPAC.
-----Original Message-----
From: Peter T Mount [mailto:peter@retep.org.uk]
Sent: Tuesday, 6 March 2001 3:52
To: Franck Martin
Cc: PostgreSQL List
Subject: Re: [HACKERS] CORBA and PG
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/
Import Notes
Resolved by subject fallback
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
CORBA has mechanisms for locating and executing remote objects. Some
services, like the naming service, could use a database as a persistant
store. Other services, like the implementation repository, could use a
database to hold rules for *how* to start a service, as well as holding
persistant info.
CORBA IORs are glue holding clients and servers together; storing those
in a database would make them persistant (as mentioned above for the
naming service). An actual CORBA object typically is an executable,
which would need to be stored as a binary object. Not sure what storing
that in a database would do for you; perhaps you could give us a use
case?
- Thomas
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
CORBA has mechanisms for locating and executing remote objects. Some
services, like the naming service, could use a database as a persistant
store. Other services, like the implementation repository, could use a
database to hold rules for *how* to start a service, as well as holding
persistant info.
CORBA IORs are glue holding clients and servers together; storing those
in a database would make them persistant (as mentioned above for the
naming service). An actual CORBA object typically is an executable,
which would need to be stored as a binary object. Not sure what storing
that in a database would do for you; perhaps you could give us a use
case?
- Thomas
Quoting Franck Martin <Franck@sopac.org>:
I guess these stubs are for accessing PG as a corba server...
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
Although I've not tried it (yet) it should be possible to access Java EJB's
from corba.
If so, then using an EJB server (JBoss www.jboss.org) you could then store them
as Entity beans. Each one would then have its own table in the database.
Peter
Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/
<http://www.sopac.org/> Support FMaps: http://fmaps.sourceforge.net/
<http://fmaps.sourceforge.net/>This e-mail is intended for its addresses only. Do not forward this
without approval. The views expressed in this e-mail may not be
necessarily
the views of SOPAC.-----Original Message-----
From: Peter T Mount [mailto:peter@retep.org.uk]
Sent: Tuesday, 6 March 2001 3:52
To: Franck Martin
Cc: PostgreSQL List
Subject: Re: [HACKERS] CORBA and PGQuoting 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/
--
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/
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
CORBA has several mechanisms for finding CORBA objects, including the
naming service and the implementation repository. The naming service
provides a directory for objects, returning IORs to allow a client to
contact a server. A database could be used to provide a persistant store
for this information. One could use a database to store rules for an
implementation repository, as well as IOR info.
A CORBA object itself is an executable. So it could be stored as a
binary object, but I'm not sure what the benefits of storage in a
database would be. Some time ago I saw an article on using PostgreSQL to
implment a versioned file system, which might have some aspects similar
to what you are asking about.
Do you have a use case to help us out?
- Thomas
Hi,
This was mentioned a while back on this list (pg hackers) - thanks to whoever
provided the pointer :-) I have not yet looked at it in depth, though that is high
on my list of TO-DO's. It is released under an apache style licence. Any reason
why there are no pointers to it on the PostgreSQL related projects or interfaces
pages?
project page: http://4suite.org/index.epy
docs on ODMG support: http://services.4Suite.org/documents/4Suite/4ODS-userguide
From project page:
"4Suite is a collection of Python tools for XML processing and object database
management. It provides support for XML parsing, several transient and persistent
DOM implementations, XPath expressions, XPointer, XSLT transforms, XLink, RDF and
ODMG object databases.
4Suite server ... features an XML data repository, a rules-based engine, and XSLT
transforms, XPath and RDF-based indexing and query, XLink resolution and many
other XML services. It also supports related services such as distributed
transactions and access control lists. Along with basic console and command-line
management, it supports remote, cross-platform and cross-language access through
CORBA, WebDAV, HTTP and other request protocols to be added shortly."
Drivers for PostgreSQL and Oracle are provided.
BTW, page pays postgresql quite a compliment too: "PostgresQL is a brilliant,
enterprise-quality, open-source, SQL DBMS." :-)
Peter T Mount wrote:
Quoting Franck Martin <Franck@sopac.org>:
I guess these stubs are for accessing PG as a corba server...
I'm trying to look to see if I can store CORBA objects inside PG, any
ideas...
Although I've not tried it (yet) it should be possible to access Java EJB's
from corba.If so, then using an EJB server (JBoss www.jboss.org) you could then store them
as Entity beans. Each one would then have its own table in the database.Peter
Franck Martin
Network and Database Development Officer
SOPAC South Pacific Applied Geoscience Commission
Fiji
E-mail: franck@sopac.org <mailto:franck@sopac.org>
Web site: http://www.sopac.org/
<http://www.sopac.org/> Support FMaps: http://fmaps.sourceforge.net/
<http://fmaps.sourceforge.net/>This e-mail is intended for its addresses only. Do not forward this
without approval. The views expressed in this e-mail may not be
necessarily
the views of SOPAC.-----Original Message-----
From: Peter T Mount [mailto:peter@retep.org.uk]
Sent: Tuesday, 6 March 2001 3:52
To: Franck Martin
Cc: PostgreSQL List
Subject: Re: [HACKERS] CORBA and PGQuoting 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/--
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/---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to majordomo@postgresql.org
--
----------------------------------------------------------------------
john reid e-mail john_reid@uow.edu.au
technical officer room G02, building 41
school of geosciences phone +61 02 4221 3963
university of wollongong fax +61 02 4221 4250
uproot your questions from their ground and the dangling roots will be
seen. more questions!
-mentat zensufi
apply standard disclaimers as desired...
----------------------------------------------------------------------
At 12:37 07/03/01 +1100, John Reid wrote:
Hi,
This was mentioned a while back on this list (pg hackers) - thanks to whoever
provided the pointer :-) I have not yet looked at it in depth, though
that is high
on my list of TO-DO's. It is released under an apache style licence. Any
reason
why there are no pointers to it on the PostgreSQL related projects or
interfaces
pages?
Probably no one's asked to put it on there ;-)
Actually there's quite a few projects out there that use PostgreSQL and
don't say so here or register it on the web site, hence the lack of links...
Peter
project page: http://4suite.org/index.epy
docs on ODMG support:
http://services.4Suite.org/documents/4Suite/4ODS-userguideFrom project page:
"4Suite is a collection of Python tools for XML processing and object database
management. It provides support for XML parsing, several transient and
persistent
DOM implementations, XPath expressions, XPointer, XSLT transforms, XLink,
RDF and
ODMG object databases.
Hmmm, nothing to do with postgres but I think I may have seen a demo of
this about a month back. If it was that, it was pretty interesting...
Peter