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

Re: Access - ODBC - serial problem...

From: Richard Combs <rncombs(at)covad(dot)net>
To: Richard Huxton <dev(at)archonet(dot)com>
Cc: Philippe Lang <philippe(dot)lang(at)attiksystem(dot)ch>,pgsql-odbc(at)postgresql(dot)org
Subject: Re: Access - ODBC - serial problem...
Date: 2004-04-08 15:21:27
Message-ID: 40756DF7.5010509@covad.net (view raw or flat)
Thread:
Lists: pgsql-odbc
The problem, as I see it, is that you are creating a table that will 
have multiple instances of the same value for code

>CREATE TABLE public.test
>(
>  id serial PRIMARY KEY,
>  code int4,
>  f1 int4,
>  f2 varchar(50),
>  f3 text
>) WITHOUT OIDS;
>
Your return for the query 'select id where code = 20' will return a 
result set, not a singleton response.  This will not tell you what the 
latest value of id was, only all values.  If you normalize your table on 
code (i.e., make code a unique value) you will always get a singleton 
response to your query.  However, you will get an error that you will 
have to trap, when you try to insert duplicate values into the table.

My solution, and I work in Delphi, not Access, so I can't tell you how 
access will work with this, is to create a function in postgres that 
inserts your values and returns currval(''id'').  Currval is always the 
id you just inserted into the table.  You then call the function as a 
stored procedure (or whatever its equivalent is in Access), with the 
proper parameters, and you get the id you just inserted to work with.

Check the docs for proper syntax on writing the function and using currval.

HTH

Richard Huxton wrote:

>On Thursday 08 April 2004 13:23, Philippe Lang wrote:
>  
>
>>With the table described below, imagine I do, from the client:
>>
>>insert into test (code) VALUES (20);
>>
>>How does the client know the id that has been given to the record? With
>>ethereal, I could see Access fetches the id by doing a
>>
>>select id from test where code = 20"
>>
>>Of course, another record has the same code, and the wrong id is being
>>fetched back. This explains what I have noticed, and that is explained
>>below...
>>    
>>
>
>Do you not have a primary key on your table?
>
>
>  
>

In response to

pgsql-odbc by date

Next:From: Shachar ShemeshDate: 2004-04-08 16:44:45
Subject: Re: Access - ODBC - serial problem...
Previous:From: Richard HuxtonDate: 2004-04-08 13:48:53
Subject: Re: Access - ODBC - serial problem...

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