Skip site navigation (1) Skip section navigation (2)

Re: Dealing with Insert Problems with Access

From: Jeff Eckermann <jeff_eckermann(at)yahoo(dot)com>
To: Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch>
Cc: pgsql-odbc(at)postgresql(dot)org
Subject: Re: Dealing with Insert Problems with Access
Date: 2004-04-21 20:22:41
Message-ID: 20040421202241.57574.qmail@web20809.mail.yahoo.com (view raw or flat)
Thread:
Lists: pgsql-odbc
--- Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch>
wrote:


> 
> Between the moment you have fetched the next id, and
> the moment Access
> does the insert into the table, another user or
> process could so the
> same thing, and fetch the exact same next id, which
> would result in an
> insert conflict, no? If I'm not wrong, in order to

No.  The "nextval" function returns the next available
sequence number, and advances the sequence at the same
time.  So a subsequent call will never get the same
number.

> have something fully
> multiuser-safe, you should use a stored procedure,
> and even switch to a
> serializable isolation level. I personally use LOCK
> TABLE. Then, with

With MVCC, it is almost never necessary to lock a
table (I've never needed to).

> the id your stored procedure gives you back, you
> open the record from
> Access.
> 
> The problem, with that solution, is that it requires
> a stored procedure
> call prior to typing any data. It does not solve the
> problem we may
> encounter with a raw linked table or subform in
> Access, when the data
> being inserted is already present in another row of
> the table. In that
> case, as mentioned in posts last week, the id
> fetched back may be wrong!
> That's really dangerous...
> 
> That's why I like your second solution: there must
> be a way of forcing
> Access to generate client-side a timestamp (with
> miliseconds) or some
> sort of unique random number, before each insert. In
> that case, the id
> fetched back by Access is 99.999999% the one you
> expect. If I'm not
> wrong, this kind of "trick" is even advised with an
> SQL Server backend,
> too...

Yes, I am sure this solution would work fine.  Which
is why I had to think a bit before choosing.

> 
> 
> Philippe Lang



	
		
__________________________________
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25
http://photos.yahoo.com/ph/print_splash

In response to

pgsql-odbc by date

Next:From: Fred ParkinsonDate: 2004-04-22 21:28:47
Subject: Any W2K issues around odbc driver install?
Previous:From: Jeff EckermannDate: 2004-04-21 20:17:03
Subject: Re: Dealing with Insert Problems with Access

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group