Обсуждение: Access - ODBC - serial problem...

Hello,

I have made some more tests regarding my precedent post "[ODBC] Access - ODBC - index strange bug", and in fact it is
notindex-related. The bug is just more visible when an index is used, apparently. 

The problem comes from the serial identifier, which is not know from the client at the time the record is being
inserted.

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
doinga  

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,
andthat is explained below... 

Is there a solution to that, except inserting records with PL-PGSQL, which I already do in some cases, by the way...?

Is that an Access-related problem? Does it work with SQL Server?


Thanks for your feedback...

Philippe


-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Philippe Lang
Envoyé : mercredi, 7. avril 2004 08:40
À : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] Access - ODBC - index strange bug

Hello,

I was able to reproduce the bug, here it is:

---------------------------------
CREATE TABLE public.test
(
  id serial PRIMARY KEY,
  code int4,
  f1 int4,
  f2 varchar(50),
  f3 text
) WITHOUT OIDS;

CREATE INDEX test_code ON public.test USING btree (code);

INSERT INTO public.test(code, f1, f2, f3) VALUES(10, 1, 'test1', 'ok1');
INSERT INTO public.test(code, f1, f2, f3) VALUES(20, 12, 'test2', 'ok2');
INSERT INTO public.test(code, f1, f2, f3) VALUES(30, 123, 'test3', 'ok3');
---------------------------------

- Link this table to an Access database, with the "Postgresql" normal driver. (Others don't work for me)

- Open the linked table.

- Type 20 in column code of the "insert" line, and insert the record.
All values are mixed up, and the newly inserted line seems to have the same values as the second record. (Because they
havethe same code?) 

- Close the table, and reopen it: the last inserted line has correct values.

- Type 31 in the same column now, things are fine. (Because code 31 was not used before?)

- Take away the index, and the bug disappears. No need to refresh the table links or whatever.


Can anyone reproduce that?


Philippe


My configuration:
-----------------
Postgresql 7.3.4 / 7.4.2
Latest snapshot from psqlodbc.dll
MS Access 2000
MDAC 2.7 / 2.8


-----Message d'origine-----

Hello,

I experience a strange bug when inserting a row into a table in which I have recently added a few indexes.
This table is linked to a MS Access database through ODBC (Latest known snapshot from Hiroshi).

When I insert a row, the serial id and different other columns are completely mixed up. Onscreen, the inserted line
inheritssome values from past lines (that have similar values), even the primary key! Things get back to normal when I
doa complete requery of the table, all values are correct, as they should always have been. 

Everything works fine if I delete the indexes.

I made a try with PG 7.3.4 and 7.4.2, same problem.

I have tried playing with the driver options, especially the one related to indexes, without success.

Did anyone have the same problem already?


---------------------------(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

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?


--
  Richard Huxton
  Archonet Ltd

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?

 

Philippe Lang wrote:

>Hello,
>
>I have made some more tests regarding my precedent post "[ODBC] Access - ODBC - index strange bug", and in fact it is
notindex-related. The bug is just more visible when an index is used, apparently. 
>
>The problem comes from the serial identifier, which is not know from the client at the time the record is being
inserted.
>
>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
doinga  
>
>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... 
>
>Is there a solution to that, except inserting records with PL-PGSQL, which I already do in some cases, by the way...?
>
>
I would love it if one of the resident Postgres gurus could verify this,
but I think running "select currval('test_id_seq')" ought to do exactly
what you want in this case. It should tell you the most recent id
assigned by the sequence in your session.

Can someone please verify that this command does not suffer races?

          Shachar

--
Shachar Shemesh
Lingnu OpenSource Consulting
http://www.lingnu.com/

I haven't tried this in Access, but why not fetch the record
identifier first then insert the record like below. Retrieving the id
first makes sure that you get the right id, because you might be in a
pooled connection environment, and you might not get the right id
after the insert.


SELECT nextval('schema.sequence_for_table_record_id') AS record_id;

INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);

or

INSERT INTO test (record_id,code) VALUES
(currval('schema.sequence_for_table_record_id'),20);


Ray A.


>Hello,
>
>I have made some more tests regarding my precedent post "[ODBC]
>Access - ODBC - index strange bug", and in fact it is not
>index-related. The bug is just more visible when an index is used,
>apparently.
>
>The problem comes from the serial identifier, which is not know from
>the client at the time the record is being inserted.
>
>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...
>
>Is there a solution to that, except inserting records with PL-PGSQL,
>which I already do in some cases, by the way...?
>
>Is that an Access-related problem? Does it work with SQL Server?
>
>
>Thanks for your feedback...
>
>Philippe
>

On Thursday 08 April 2004 17:44, Shachar Shemesh wrote:
> Philippe Lang wrote:
> >
> >Is there a solution to that, except inserting records with PL-PGSQL, which
> > I already do in some cases, by the way...?
>
> I would love it if one of the resident Postgres gurus could verify this,
> but I think running "select currval('test_id_seq')" ought to do exactly
> what you want in this case. It should tell you the most recent id
> assigned by the sequence in your session.
>
> Can someone please verify that this command does not suffer races?

currval() and nextval() are guaranteed safe. What this does mean is that you
can end up with gaps in your sequence though.

--
  Richard Huxton
  Archonet Ltd

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
transparentlyby MS Access, when a line is added through a linked table, or through a subform. There is no opportunity
forme 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
resultset, 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@covad.net]
Envoyé : jeudi, 8. avril 2004 17:21
À : Richard Huxton
Cc : Philippe Lang; pgsql-odbc@postgresql.org
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
nottell you what the latest value of id was, only all values.  If you normalize your table on code (i.e., make code a
uniquevalue) you will always get a singleton response to your query.  However, you will get an error that you will have
totrap, 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
functionin postgres that inserts your values and returns currval(''id'').  Currval is always the id you just inserted
intothe table.  You then call the function as a stored procedure (or whatever its equivalent is in Access), with the
properparameters, 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?

From what I know, SELECT @@IDENTITY, is a Transact SQL call that is a
function of SQL server.  I think what is happening here, is Access is
making the call to the server and generating the call.  Just an educated
guess, but how would the ODBC driver know to make this call after an
insert unless it kept track of the table schema for every table.  Seems
like more of a higher level function, Access learns the schema for the
tables you have open, then makes the proper call.  Of course being MS
Access working with MS SQL might have something to do with it.

Tha'ts one of the reasons I wound up using the function/stored procedure
solution.  The ODBC driver knows how to handle functions, and my front
end doesn't have to know anything but how to put the proper pararmeters
in and read them on return, after making the proper call.

Again, I'm not an Access person, so I can't pretend to know how to do
this through Access tables.  It does work great when programming in
Delphi though, or for that matter wrapping your SQL code in a higher
level language.

Philippe Lang wrote:

>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
transparentlyby MS Access, when a line is added through a linked table, or through a subform. There is no opportunity
forme 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
>
>
>
>

Having fought with this before myself, I can tell you that this can be made
to work, but it's not exactly ideal.  With an Access front-end, you try to
make the back-end plug-and play.  You don't want to have to execute a
pass-through query to be able to add a row through a bound form.  It would
certainly be nice if the ODBC driver for PostgreSQL would work with Access
like other drivers do (hovever that works), and allow Access to see the new
Serial value after adding a row.

On Thursday, April 08, 2004 10:12 AM, Ray Aspeitia
[SMTP:aspeitia@sells.com] wrote:
>
> I haven't tried this in Access, but why not fetch the record
> identifier first then insert the record like below. Retrieving the id
> first makes sure that you get the right id, because you might be in a
> pooled connection environment, and you might not get the right id
> after the insert.
>
>
> SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
>
> INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
>
> or
>
> INSERT INTO test (record_id,code) VALUES
> (currval('schema.sequence_for_table_record_id'),20);
>
>
> Ray A.
>
>
> >Hello,
> >
> >I have made some more tests regarding my precedent post "[ODBC]
> >Access - ODBC - index strange bug", and in fact it is not
> >index-related. The bug is just more visible when an index is used,
> >apparently.
> >
> >The problem comes from the serial identifier, which is not know from
> >the client at the time the record is being inserted.
> >
> >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...
> >
> >Is there a solution to that, except inserting records with PL-PGSQL,
> >which I already do in some cases, by the way...?
> >
> >Is that an Access-related problem? Does it work with SQL Server?
> >
> >
> >Thanks for your feedback...
> >
> >Philippe
> >
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend

Is there a Postgresql ODBC driver guru that could give us an idea for a driver modification? I don't know the exact job
ofan ODBC driver, nor how it connects to Postgresql, so I cannot tell if it would be possible to modify the ODBC
driver,and do something like a "SELECT @@IDENTITY" (which is a SQL Server 2000 call, correct). 

Since Microsoft is behind both Access and SQL Server, maybe nothing can be done in the driver itself, and I'll have to
handlemy insertions by hand, through pl/pgsql. But really, that's a serious limitation... 


-----Message d'origine-----
De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Steve Jorgensen
Envoyé : vendredi, 9. avril 2004 03:16
À : pgsql-odbc@postgresql.org
Objet : Re: [ODBC] Access - ODBC - serial problem...

Having fought with this before myself, I can tell you that this can be made to work, but it's not exactly ideal.  With
anAccess front-end, you try to make the back-end plug-and play.  You don't want to have to execute a pass-through query
tobe able to add a row through a bound form.  It would certainly be nice if the ODBC driver for PostgreSQL would work
withAccess like other drivers do (hovever that works), and allow Access to see the new Serial value after adding a row. 

On Thursday, April 08, 2004 10:12 AM, Ray Aspeitia [SMTP:aspeitia@sells.com] wrote:
>
> I haven't tried this in Access, but why not fetch the record
> identifier first then insert the record like below. Retrieving the id
> first makes sure that you get the right id, because you might be in a
> pooled connection environment, and you might not get the right id
> after the insert.
>
>
> SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
>
> INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
>
> or
>
> INSERT INTO test (record_id,code) VALUES
> (currval('schema.sequence_for_table_record_id'),20);
>
>
> Ray A.
>
>
> >Hello,
> >
> >I have made some more tests regarding my precedent post "[ODBC]
> >Access - ODBC - index strange bug", and in fact it is not
> >index-related. The bug is just more visible when an index is used,
> >apparently.
> >
> >The problem comes from the serial identifier, which is not know from
> >the client at the time the record is being inserted.
> >
> >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...
> >
> >Is there a solution to that, except inserting records with PL-PGSQL,
> >which I already do in some cases, by the way...?
> >
> >Is that an Access-related problem? Does it work with SQL Server?
> >
> >
> >Thanks for your feedback...
> >
> >Philippe
> >
>
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 8: explain analyze is your friend

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

               http://archives.postgresql.org

Philippe,

I believe the problem is that when you linked the table in Access, you
selected code as the unique field, instead of the id field.  Try unlinking
the table, then relinking, making sure you select the id field (or
combinations of fields) that is unique.

Thanks.

Michael


""Philippe Lang"" <philippe.lang@attiksystem.ch> wrote in message
news:6C0CF58A187DA5479245E0830AF84F420802A0@poweredge.attiksystem.ch...
> Hello,
>
> I have made some more tests regarding my precedent post "[ODBC] Access -
ODBC - index strange bug", and in fact it is not index-related. The bug is
just more visible when an index is used, apparently.
>
> The problem comes from the serial identifier, which is not know from the
client at the time the record is being inserted.
>
> 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...
>
> Is there a solution to that, except inserting records with PL-PGSQL, which
I already do in some cases, by the way...?
>
> Is that an Access-related problem? Does it work with SQL Server?
>
>
> Thanks for your feedback...
>
> Philippe
>
>
> -----Message d'origine-----
> De : pgsql-odbc-owner@postgresql.org
[mailto:pgsql-odbc-owner@postgresql.org] De la part de Philippe Lang
> Envoy� : mercredi, 7. avril 2004 08:40
> � : pgsql-odbc@postgresql.org
> Objet : Re: [ODBC] Access - ODBC - index strange bug
>
> Hello,
>
> I was able to reproduce the bug, here it is:
>
> ---------------------------------
> CREATE TABLE public.test
> (
>   id serial PRIMARY KEY,
>   code int4,
>   f1 int4,
>   f2 varchar(50),
>   f3 text
> ) WITHOUT OIDS;
>
> CREATE INDEX test_code ON public.test USING btree (code);
>
> INSERT INTO public.test(code, f1, f2, f3) VALUES(10, 1, 'test1', 'ok1');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(20, 12, 'test2', 'ok2');
> INSERT INTO public.test(code, f1, f2, f3) VALUES(30, 123, 'test3', 'ok3');
> ---------------------------------
>
> - Link this table to an Access database, with the "Postgresql" normal
driver. (Others don't work for me)
>
> - Open the linked table.
>
> - Type 20 in column code of the "insert" line, and insert the record.
> All values are mixed up, and the newly inserted line seems to have the
same values as the second record. (Because they have the same code?)
>
> - Close the table, and reopen it: the last inserted line has correct
values.
>
> - Type 31 in the same column now, things are fine. (Because code 31 was
not used before?)
>
> - Take away the index, and the bug disappears. No need to refresh the
table links or whatever.
>
>
> Can anyone reproduce that?
>
>
> Philippe
>
>
> My configuration:
> -----------------
> Postgresql 7.3.4 / 7.4.2
> Latest snapshot from psqlodbc.dll
> MS Access 2000
> MDAC 2.7 / 2.8
>
>
> -----Message d'origine-----
>
> Hello,
>
> I experience a strange bug when inserting a row into a table in which I
have recently added a few indexes.
> This table is linked to a MS Access database through ODBC (Latest known
snapshot from Hiroshi).
>
> When I insert a row, the serial id and different other columns are
completely mixed up. Onscreen, the inserted line inherits some values from
past lines (that have similar values), even the primary key! Things get back
to normal when I do a complete requery of the table, all values are correct,
as they should always have been.
>
> Everything works fine if I delete the indexes.
>
> I made a try with PG 7.3.4 and 7.4.2, same problem.
>
> I have tried playing with the driver options, especially the one related
to indexes, without success.
>
> Did anyone have the same problem already?
>
>
> ---------------------------(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
>
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 8: explain analyze is your friend
>

To state the obvious, MS Access thinks the world is MSSQL,
ODBC interface or not. At Simba (ODBC Driver kit company),
proofing drivers specifically against MS Access weirdness
was our "competitive edge". :-)

SHORT
If you don't have a unique key for MyTable (other than the identity)
but you're feeling like taking a few risks,
then the best bet I've found amounts to (pardon the T-SQL):

    declare @id int
    select @id = max(IDENTITYCOL) from MyTable
    insert MyTable(code,...) values(20, ...)
    select @id = IDENTITYCOL from MyTable
    where    IDENTITYCOL > @id and code = 20 ...
    -- IDENTITYCOL is a T-SQL specific moniker for "the"
    --    serial column of a table.
LONG
SELECT @@IDENTITY (or safer in MSSQL: SELECT SCOPE_IDENTITY() )
can only be emulated in the PG server, not the driver.
The driver will never know enough to determine what table
(hence sequence) this is the nextval for.
Note that in MSSQL, an insert can cause a trigger to fire
that inserts INTO A DIFFERENT TABLE: this will set
@@IDENTITY to the nextval for that OTHER TABLE.
SCOPE_IDENTITY() is "safer", since it deals with the last
table insertion in the current stored procedure.

So, if you want to emulate this feature, it means:
- every connection(server process) needs a qua-global nextval storage
- every procedure local context (no matter what language !?)
    needs a nextval
- nextval has to be changed to update this.

How likely does that sound as a to-do?
------
In case you're wondering how MS Access could possibly survive
working with any dataservers other than MSSQL, I never thought
there was really any such intention. MS Access uses
proprietary ODBC entry points and Connection/Statement
attributes (though it's mercifully silent if it tries them
and an innocent non-MS driver errors out).



Philippe Lang wrote:
> Is there a Postgresql ODBC driver guru that could give us an idea for a driver modification? I don't know the exact
jobof an ODBC driver, nor how it connects to Postgresql, so I cannot tell if it would be possible to modify the ODBC
driver,and do something like a "SELECT @@IDENTITY" (which is a SQL Server 2000 call, correct). 
> Since Microsoft is behind both Access and SQL Server, maybe nothing can be done in the driver itself, and I'll have
tohandle my insertions by hand, through pl/pgsql. But really, that's a serious limitation... 
> -----Message d'origine-----
> De : pgsql-odbc-owner@postgresql.org [mailto:pgsql-odbc-owner@postgresql.org] De la part de Steve Jorgensen
> Envoyé : vendredi, 9. avril 2004 03:16
> À : pgsql-odbc@postgresql.org
> Objet : Re: [ODBC] Access - ODBC - serial problem...
>
> Having fought with this before myself, I can tell you that this can be made to work, but it's not exactly ideal.
 > With an Access front-end, you try to make the back-end plug-and play.

You don't want to have to execute a pass-through query to be able to add
a row through a bound form.

It would certainly be nice if the ODBC driver for PostgreSQL would work
with Access

like other drivers do (hovever that works), and allow Access to see the
new Serial value after adding a row.
> On Thursday, April 08, 2004 10:12 AM, Ray Aspeitia [SMTP:aspeitia@sells.com] wrote:
>
>>I haven't tried this in Access, but why not fetch the record
>>identifier first then insert the record like below. Retrieving the id
>>first makes sure that you get the right id, because you might be in a
>>pooled connection environment, and you might not get the right id
>>after the insert.
>>
>>SELECT nextval('schema.sequence_for_table_record_id') AS record_id;
>>INSERT INTO test (record_id,code) VALUES ([retrieved record_id],20);
>>
>>or
>>
>>INSERT INTO test (record_id,code) VALUES
>>(currval('schema.sequence_for_table_record_id'),20);
>>

>>>Hello,
>>>
>>>I have made some more tests regarding my precedent post "[ODBC]
>>>Access - ODBC - index strange bug", and in fact it is not
>>>index-related. The bug is just more visible when an index is used,
>>>apparently.
>>>
>>>The problem comes from the serial identifier, which is not know from
>>>the client at the time the record is being inserted.
>>>
>>>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...
>>>
>>>Is there a solution to that, except inserting records with PL-PGSQL,
>>>which I already do in some cases, by the way...?
>>>
>>>Is that an Access-related problem? Does it work with SQL Server?