"Error inserting the new record" when adding row i

[kuba]

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

[kuba]

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.

[pluby]

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

[kuba]

Will do.

[pluby]

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:

[pluby]

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

[kuba]

The test case I proposed earlier doesn't reproduce the problem in 3.2 beta , 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.

[kuba]

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

[kuba]

(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.

[pluby]

[kuba]

Let me go back some, I want to let you see least amount of noise.

[pluby]

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

[kuba]

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).

[kuba]

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.

[pluby]

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