To comment on the following update, log in, then open the issue: http://www.openoffice.org/issues/show_bug.cgi?id=103672 Issue #|103672 Summary|database form list box inserts incorrect primary key v |alue Component|Database access Version|OOO310m11 Platform|All URL| OS/Version|All Status|UNCONFIRMED Status whiteboard| Keywords| Resolution| Issue type|DEFECT Priority|P3 Subcomponent|none Assigned to|dbaneedsconfirm Reported by|prlw1
------- Additional comments from [email protected] Mon Jul 20 16:51:21 +0000 2009 ------- I am trying to debug an error in list box handling in forms. The problem happens under ubuntu 8.04.3 which only has openoffice 2.4, so I have now compiled from 3.1.0 source on NetBSD-current/i386 (pkgsrc) and see the same problem. So, openoffice 3.1.0, unixODBC, psqlODBC, PostgreSQL is the datasource setup. Two toy tables, one references the other which will be the source for the list box: create table college ( id serial primary key, shortname text ); create table names ( id serial primary key, name text, college integer references college(id) ); insert into college (shortname) values ('Gonville and Caius'); insert into college (shortname) values ('Newnham'); insert into college (shortname) values ('Trinity Hall'); insert into names (college,name) values (1,'John'); Now create a form in design view, names is the source for the form, college is the source for the list box, and you want names.college to link to college.id. Add a text box for id and name to check all the data. When leaving design view, the data looks correct and it appears that you see the equivalent of select names.id, names.name, college.shortname from names, college where names.college = college.id; id | name | shortname ----+------+-------------------- 1 | John | Gonville and Caius But now try entering a record. From an earlier run (more colleges hence '9'): LOG: statement: BEGIN;update "public"."names" set "name" = E'Minnie', "college" = -1309508768 where ctid = '(0, 9)' returning ctid ERROR: insert or update on table "names" violates foreign key constraint "names_college_fkey" DETAIL: Key (college)=(-1309508768) is not present in table "college". -1309508768 doesn't look like the id from the listbox... Some oddities looking at the logs: SELECT * FROM "cuca"."public"."names" WHERE 0 = 1 is called often - why? It can't possibly return any data, and I think you already found the field names from the system tables (pg_catalog schema). PREPARE "_KEYSET_0xb41f90e0"(tid,tid) as SELECT * , "ctid" FROM "cuca"."public"."names" where ctid in ($1,$2) That query is prepared, but I don't think it is used: test=# select ctid,id from names; ctid | id -------+---- (0,1) | 1 (1 row) test=# select *,ctid from names where ctid in (0,1); ERROR: operator does not exist: tid = integer LINE 1: select *,ctid from names where ctid in (0,1); ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts. So, what should I do now? Do you know which bit of OO code I could start looking at? (Why the complication of ctids instead of using the primary keys - is it to take care of tables without primary keys? (PKCOLUMN_NAME)) --------------------------------------------------------------------- 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]
