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 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(at)postgresql(dot)org [mailto:pgsql-odbc-owner(at)postgresql(dot)org] De la part de Philippe Lang
Envoyé : mercredi, 7. avril 2004 08:40
À : pgsql-odbc(at)postgresql(dot)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(at)postgresql(dot)org so that your
message can get through to the mailing list cleanly