Frank Schönheit - Sun Microsystems Germany wrote:
Hi Ross,
Using tcpdump to compare the SQL commands sent from OOo to PostgreSQL, I
can see where the problem is occuring. When adding a new record via the
form, the INSERT includes the autosequencing column (sampleid), which
inserts a value of 0 (zero). When adding a new record via the OOo data
explorer, the sql INSERT command doesn't include the autosequencing
sampleid column.
Interesting. Both views share nearly the same code, so there really
shouldn't be a difference. Out of interest, if you have a form with a
table control only, does it also show the problem?
Hi Frank,
Thanks for this. I now have a working form.
In short, if I delete the original form control and replace it with a
new one it works fine from then on. I should be satisfied that it's
working now, but I'm interested in what happened to the original control
to break it. Something in the original form control presumably went bad,
or always was bad and, whatever that was, it was stored in the OOo
document file so that it was perpetuated. I'll try finding it later, but
I'm over my deadline just now.
"SELECT MAX(columnid)" is only a workaround, which of course isn't
multi-user-safe at all. This workaround is used for databases where no
other way to retrieve generated values is known - unfortunately there's
no (wide-spread enough) standard to retrieve the server-side generated
values after an insertion.
I'm not sure at the moment, but I suspect PostgreSQL allows to retrieve
those values. Did you play with "Edit|Database|Advanced settings"? This
is for overriding the SELECT MAX behaviour.
Do these work with the SDBC driver? After playing with them, I've
realised that the Postgres SDBC driver is using the postgres functions
nextval() and currval() by default, which is great, but there appears to
be a simple bug which causes OOo to fall back to the SELECT MAX("ID")
FROM "Foo" method. Otherwise this would adequately avoid the multi-user
contention problem (which, as you will know already, is because
currval() returns the value returned by the most recent nextval() for
that sequence in the same session).
To illustrate the error, when I insert a new record into table "Foo"
with pk column "ID", OOo issues the following statements (return values
indicated with >>>):
INSERT INTO "public"."Foo" ( "Value") VALUES ( '4').
SELECT attname,attnum FROM pg_attribute INNER JOIN pg_class ON attrelid
= pg_class.oid INNER JOIN pg_namespace ON pg_class.relnamespace =
pg_namespace.oid WHERE relname='Foo' AND nspname='public'.
SELECT conkey FROM pg_constraint INNER JOIN pg_class ON conrelid =
pg_class.oid INNER JOIN pg_namespace ON pg_class.relnamespace =
pg_namespace.oid LEFT JOIN pg_class AS class2 ON confrelid = class2.oid
LEFT JOIN pg_namespace AS nmsp2 ON class2.relnamespace=nmsp2.oid WHERE
pg_class.relname
= 'Foo' AND pg_namespace.nspname = 'public' AND
pg_constraint.contype='p'.
SELECT pg_attribute.attname, pg_attrdef.adsrc FROM pg_class,
pg_namespace, pg_attribute LEFT JOIN pg_attrdef ON pg_attribute.attrelid
= pg_attrdef.adrelid AND pg_attribute.attnum = pg_attrdef.adnum WHERE
pg_attribute.attrelid = pg_class.oid AND pg_class.relnamespace =
pg_namespace.oid AND
pg_namespace.nspname = 'public' AND pg_class.relname LIKE 'Foo'
AND pg_attrdef.adsrc != ''.
>>> nextval('"Foo_ID_seq"'::regclass)
SELECT currval('"Foo_ID_seq"'::regclass).
>>> currval 4
SELECT * FROM "public"."Foo" WHERE ID = 4.
>>> ERROR: column "id" does not exist
SELECT MAX("ID") FROM "public"."Foo".
>>> max 4
SELECT * FROM "public"."Foo" WHERE "public"."Foo"."ID" = '4'.
Also, it looks like the process is assuming that the error is due to an
incorrect currval value rather than the quoting error in the record
re-read SELECT statement.
I have entered the following issue:
http://www.openoffice.org/issues/show_bug.cgi?id=67102
Ross
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
