Welcome to NeoOffice developer notes and announcements
NeoOffice
Developer notes and announcements
 
 

This website is an archive and is no longer active
NeoOffice announcements have moved to the NeoOffice News website


Support
· Forums
· NeoOffice Support
· NeoWiki


Announcements
· Twitter @NeoOffice


Downloads
· Download NeoOffice


  
NeoOffice :: View topic - "Error inserting the new record" when adding row i
"Error inserting the new record" when adding row i
 
   NeoOffice Forum Index -> NeoOffice Beta Releases
View previous topic :: View next topic  
Author Message
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 6:04 am    Post subject: "Error inserting the new record" when adding row i

This is OO.org bug 117638 that is a WORKSFORME in OO.org 3.3. They must have fixed it between 3.1.1 and 3.3. I will download OO.org for Mac and bisect to make it easier to find where the fix was.

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
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 6:09 am    Post subject: Version Info

This was under NeoOffice 3.1.2 p6.
I'm downloading OO.org 3.1.1, 3.2.0 and 3.2.1 to bisect. Will update on results ASAP.
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Apr 01, 2011 8:37 am    Post subject:

Since this forum is for NeoOffice 3.2 Beta support only, can you retest and confirm that this OpenOffice.org 3.1.1 bug still occurs in NeoOffice 3.2 Beta Patch 2? If this OpenOffice.org bug still occurs, then can you provide the following information:

1. Mac OS X version number
2. Postgres database version number
3. Postgres JBDC driver version

While NeoOffice 3.2 Beta uses the same OpenOffice.org 3.1.1 code, all development effort is currently being put into NeoOffice 3.2 Beta.

Patrick
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 8:56 am    Post subject:

Will do.
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Apr 01, 2011 8:59 am    Post subject:

While you are testing NeoOffice 3.2 Beta, I tried reproducing the problem that you describe in NeoOffice 3.2 Beta Patch 2 using the following:

1. Mac OS X 10.5.8
2. MySQL 5.5
3. MySQL Connector/J 5.1.15 JDBC driver

With the above, I had no problem inserting a new row so the problem that you describe appears to be limited only to the Postgres JDBC driver. For reference, here is the MySQL SQL statement that I used to create the test table which should be equivalent to your Postgres SQL statement:

Code:
create table test (
key1 int(10) NOT NULL AUTO_INCREMENT,
key2 int(10) NOT NULL,
PRIMARY KEY (key1, key2));


Patrick
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Apr 01, 2011 9:38 am    Post subject:

I just installed Postgres on my machine and with Neooffice 3.2 Beta Patch 2 I was not able to reproduce the problem. Here is what I used:

1. Mac OS X 10.5.8
2. Postgres 9.0.3
3. Postgres 9.0-801 JDBC 3 driver

Important note: since I am testing on Mac OS X 10.5 Leopard, NeoOffice uses Java 1.5 so you have to install the JDBC 3 driver as the JDBC 4 driver will only work with Java 1.6 and higher.

If you are using the JDBC 4 driver, can you see if this problem is resolved by switching to the JDBC 3 driver? Or, if you are running on Mac OS X 10.6 Snow Leopard with the JDBC 3 driver, can you try using the JDBC 4 driver?

Patrick
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 12:29 pm    Post subject: Update

The test case I proposed earlier doesn't reproduce the problem in 3.2 beta Embarassed, it turns out. I had to spiff it up a little to match what I'm doing in the actual application.

First the details: OS X 10.6.7, NeoOffice 3.2 Beta, Postgres 9.0.3, Postgres 9.0-801 JDBC 4 driver. Your setup on 10.5 should work too.

Below is the SQL needed to set up the table. The problem appears to be that base doesn't know that part of the pkey is autogenerated. I have a bigger application that does that, and I don't know if there's an easy workaround. I can't actually declare key1 to be serial due to other reasons.

Nitty gritty: this is a materialized view and exists only because OO.org base refuses to allow insertions/modifications on regular views with triggers. I'm sure there must be a proper way of handling this, since applications such as pgAdmin can insert rows into this table just fine, with key1 being empty (in spite of the constraint that gets fulfilled by the trigger just before the actual insert happens).

OO.org 3.3 doesn't trigger the error, so I'm bisecting it now.

Code:
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();


Last edited by kuba on Fri Apr 01, 2011 2:32 pm; edited 1 time in total
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 12:33 pm    Post subject: OO.org bisection

This bug is present in OO.org 3.2.1, and fixed in OO.org 3.3.

I was testing on OO.org with same PostgreSQL version and same JDBC driver as NeoOffice. I don't think that JDBC3 vs JDBC4 would make any difference.

I'll try the OOO330 snapshots that came after 3.2.1 to see which one had it fixed, to make it easier for you to nail down the patch.

Note: the compiled snapshots aren't available from OO.org it seems, but they are archived. I managed to find them at http://mirror2.unlogisch.ch/ooodev-archive
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 2:31 pm    Post subject: bisection

(in progress)

330m1: no error dialog but the newly inserted row is displayed with all fields empty (the DB insert succeeds)
330m5: OK, no error dialog
330m10: OK, no error dialog

So presumably the changes to look for are before m5. I'll nail it down further after I get back home.


Last edited by kuba on Fri Apr 01, 2011 3:05 pm; edited 1 time in total
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Apr 01, 2011 2:32 pm    Post subject:

kuba wrote:
(in progress)

330m10: bug is fixed


I will check out that OOo tag and diff the JDBC controller code against the same code in NeoOffice 3.2 Beta's underlying OpenOffice.org and see if I can see any changes that can be backported.

Patrick
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Fri Apr 01, 2011 2:33 pm    Post subject:

Let me go back some, I want to let you see least amount of noise.
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Fri Apr 01, 2011 9:31 pm    Post subject:

I have bad news: with your latest set of tables and triggers, I can reproduce this problem in not only NeoOffice 3.2 Beta, but also in OpenOffice.org 3.3 and LibreOffice 3.3.2.

Granted, I am using the JDBC 3 driver on Mac OS X 10.5.8. But is there anything that I am missing?

Patrick
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Sat Apr 02, 2011 12:12 pm    Post subject:

I don't see it in OO 3.3, with JDBC 4, but I do see it in 3.2.1 on the same machine. I also see different behavior in 330m5 (it's like in 3.3) and in 330m1 (it's halfway -- better than 3.2.1 but worse than 3.3). I'm getting m2 and m4 just to narrow it down, but whatever changed it was the 2nd aspect of the problem.

The problem has three aspects.
1. Error message shows up - this happens in 3.2.1 but not 330m1.
2. Both key1 and key2 are displayed with 0 values no matter what you type in key2 - this happens in 330m1, not in 330m5, and I'm narrowing it down.
3. key1's actual value is not retrieved from the newly created row -- this problem is present even in 3.3, but I could live with it for now.

Just to be clear: you have to type a numeric value into key2, leaving key1 empty. After you insert, you OO.org shows key2 as zero, key1 is whatever you typed in, and then when you refresh key2 shows up correctly too (that's the leftover bug).
Back to top
kuba
Agent


Joined: Mar 19, 2010
Posts: 12

PostPosted: Sat Apr 02, 2011 12:16 pm    Post subject:

Update from my bisection:

1. Fixed in all 330 dev and 3.3.0 release, broken in 3.2.1 release.
2. Fixed in 330m2, broken in 330m1.
3. Not fixed at least in 3.3.0, didn't check libreoffice or anything else.
Back to top
pluby
The Architect
The Architect


Joined: Jun 16, 2003
Posts: 11949

PostPosted: Sat Apr 02, 2011 2:00 pm    Post subject:

I have bad news: after my last post I started diffing the OpenOffice.org OOO330_m10 database connectivity code against the OpenOffice.org 3.1.1 code that NeoOffice uses and in the small amount of code that I diffed I found backporting the OpenOffice.org 3.3 code is not feasible.

The reason is that not only did Oracle's OpenOffice.org change several thousand lines of code in the database connectivity portion of their code, but many of the changes are also incompatible with the OpenOffice.org 3.1.1 code.

In other words, the only feasible way to fix this problem in NeoOffice is for NeoOffice to upgrade all of its OpenOffice.org code to the OpenOffice.org 3.3 code.

Unfortunately, our very limited donations only supports one part time developer (me) and upgrading NeoOffice to a new version of the OpenOffice.org code is at least six months of work. Currently, I am spending every available hour working on making NeoOffice run smoothly on Apple's upcoming Mac OS X 10.7 release and that work will not likely be done until this summer. As a result, the next OpenOffice.org upgrade work cannot start until the end of this summer at the very earliest.

Given our financial and dveloper resource limitation, my recommendation would be to switch to OpenOffice.org 3.3 or LibreOffice 3.3.2 if this problem is impacting your operations.

Patrick
Back to top
Display posts from previous:   
   NeoOffice Forum Index -> NeoOffice Beta Releases All times are GMT - 7 Hours
Goto page 1, 2  Next
Page 1 of 2

 
You can post new topics in this forum
You can reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You cannot download files in this forum

Powered by phpBB © 2001, 2005 phpBB Group

All logos and trademarks in this site are property of their respective owner. The comments are property of their posters, all the rest © Planamesa Inc.
NeoOffice is a registered trademark of Planamesa Inc. and may not be used without permission.
PHP-Nuke Copyright © 2005 by Francisco Burzi. This is free software, and you may redistribute it under the GPL. PHP-Nuke comes with absolutely no warranty, for details, see the license.