To comment on the following update, log in, then open the issue:
http://www.openoffice.org/issues/show_bug.cgi?id=67102
Issue #|67102
Summary|Error in SQL SELECT statement when re-reading new tabl
|e record
Component|Database access
Version|OOo 2.0.3
Platform|All
URL|
OS/Version|All
Status|UNCONFIRMED
Status whiteboard|
Keywords|
Resolution|
Issue type|DEFECT
Priority|P5
Subcomponent|none
Assigned to|dbaneedsconfirm
Reported by|rosspjohnson
------- Additional comments from [EMAIL PROTECTED] Thu Jul 6 23:39:55 -0700
2006 -------
There is an identifier quoting error in a SQL query statement that is generated
by either OOo or the PostgreSQL SDBC driver (version 0.7.2).
The problem is shown below, and the fix should be very simple once the relevant
code section is located.
The bug is exposed when making use of the Database "Advanced Settings" dialog as
follows:
In "Auto-increment Statement" I enter "nextval()". This appears to hint to the
SDBC driver that it can the currval() function to get the current sequence
value, but there appears to be a simple bug.
Now, 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
The problem appears to be a simple oversight in the code: i.e. double quotes are
needed around the pk column name in the WHERE clause to prevent Postgres from
down-casing the name.
This error causes OOo to revert to the workaround method of re-reading the new
record, which is not multi-user safe:
SELECT MAX("ID") FROM "public"."Foo".
>>> max 4
SELECT * FROM "public"."Foo" WHERE "public"."Foo"."ID" = '4'.
---------------------------------------------------------------------
Please do not reply to this automatically generated notification from
Issue Tracker. Please log onto the website and enter your comments.
http://qa.openoffice.org/issue_handling/project_issues.html#notification
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
---------------------------------------------------------------------
To unsubscribe, e-mail: [EMAIL PROTECTED]
For additional commands, e-mail: [EMAIL PROTECTED]
