Apache OpenOffice (AOO) Bugzilla – Issue 117638
"Error inserting the new record" when adding a row to a table with multicolumn, partially auto-generated foreign key.
Last modified: 2012-06-13 12:23:00 UTC
To reproduce: 1. In a postgresql database (8.x or 9.x should be fine), create a test table. The table should have a two column integer primary key, one of which is autogenerated: CREATE TABLE test ( key1 serial NOT NULL, key2 integer NOT NULL, CONSTRAINT test_pkey PRIMARY KEY (key1, key2) ) WITH ( OIDS=FALSE ); 2. Connect to the database from OO.org Base using the JDBC driver. 3. Open the table in table view. 4. Try adding the row. An error message box appears, stating the following: "Error inserting the new record ResultSet not positioned properly, perhaps you need to call next." I believe that this problem is not unique to PostgreSQL, it should be reproducible with any JDBC-connectable database that supports the table definition equivalent to the one given above. Note: this problem persists from times of OpenOffice 2.0.2: http://www.oooforum.org/forum/viewtopic.phtml?t=34200
I tested in OOo 3.3 with Postgresql 9.0, pgJDBC 8.4.701 and java 1.6.0_24 under win7, and it works for me. I can add new records to test table. I could not test on Mac, please update to OOo 3.3 and test again.
Here's updated test case that doesn't work. The rationale for this weird approach: the application in question uses materialized views because OO.org base doesn't allow modification of the views, even if the database itself allows it. The long-term solution is to have a way of telling the Base code which columns of the view should be treated as the primary key, and if they are given then inserts/updates/deletes should be enabled. You should be inserting a row with empty key1 and an integer value in key2. This works from say pgAdmin. CREATE TABLE test ( key1 integer NOT NULL, key2 integer NOT NULL, CONSTRAINT test_pkey PRIMARY KEY (key1, key2) ) WITH ( OIDS=FALSE ); CREATE SEQUENCE test_key1_seq INCREMENT 1 MINVALUE 1 MAXVALUE 9223372036854775807 START 1 CACHE 1; CREATE OR REPLACE FUNCTION test_it() RETURNS trigger AS $BODY$BEGIN NEW.key1 := nextval('test_key1_seq'::regclass); END$BODY$ LANGUAGE plpgsql VOLATILE SECURITY DEFINER COST 100; CREATE TRIGGER test_it BEFORE INSERT ON test FOR EACH ROW EXECUTE PROCEDURE test_it();
Upon further testing: the error dialog box is not present anymore in 3.3, but the correct values for key1 are not retrieved -- 0 is visible instead. That'd be a separate bug.
Closing.
getting rid of value "enhancement" for field "severity". For enhancement the field "issue type" shall be used.