Re: Access - ODBC - serial problem...

Hello,

Thanks for your answers.

select currval('test_id_seq') works, I have tested it. But the problem is that 
what I have described is done transparently by MS Access, when a line is added 
through a linked table, or through a subform. There is no opportunity for me to 
call currval, except if I insert a line into the table programmatically, what I 
want to avoid.

Is there maybe a way to incorporate a call to currval into the driver?

I have traced what SQL Server 2000 does, and there is call

SELECT @@IDENTITY

just after the insert. Does that mean that the problematic SQL call I have just 
talked about before

select id from test where code = 20

is a driver feature, more than a MS Access feature? If someone has an idea for 
a driver patch...

Philippe


Note: Concerning the remark of Richard Combs: "Your return for the query 
'select id where code = 20' will return a result set, not a singleton 
response.": I agree, but nothing should prevent us from inserting a line into a 
table that way... If code was unique, I agree, we would be saved... By the way, 
the inserts run fine each time I add a *new* code value...


________________________________

De : Richard Combs [mailto:rncombs@xxxxxxxxx] 
Envoyé : jeudi, 8. avril 2004 17:21
À : Richard Huxton
Cc : Philippe Lang; pgsql-odbc@xxxxxxxxxxxxxx
Objet : Re: [ODBC] Access - ODBC - serial problem...


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?
        
        
          




---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
    (send "unregister YourEmailAddressHere" to majordomo@xxxxxxxxxxxxxx)