Bug 35784 - EDITING - unable to update postgresql databases using postgres SDBC driver
Summary: EDITING - unable to update postgresql databases using postgres SDBC driver
Status: CLOSED FIXED
Alias: None
Product: LibreOffice
Classification: Unclassified
Component: Base (show other bugs)
Version:
(earliest affected)
3.3.1 release
Hardware: All All
: high major
Assignee: Lionel Elie Mamane
URL: http://bugs.debian.org/620100
Whiteboard:
Keywords: regression
Depends on:
Blocks: mab3.4
  Show dependency treegraph
 
Reported: 2011-03-29 23:28 UTC by Eric Wurbel
Modified: 2012-10-07 20:52 UTC (History)
5 users (show)

See Also:
Crash report or crash signature:


Attachments
minimal patch (1.44 KB, patch)
2011-08-09 14:16 UTC, Lionel Elie Mamane
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Eric Wurbel 2011-03-29 23:28:14 UTC
When using Base to access a postgresql database with the postgresql driver, it is impossible to insert-update-delete rows in the database.

The bug is reproducible with a minimal postgresql database containing the following table :
create table test_table (
  id serial primary key,
  label text
);

Libreoffice 3.3.1 OOO330m19 (Build:8)
Postgresql 8.4
Ubuntu 10.04LTS

Regards
Comment 1 Alex Thurgood 2011-03-30 00:56:42 UTC
Also known as OOo bug :

http://openoffice.org/bugzilla/show_bug.cgi?id=117280


Alex
Comment 2 Jürgen Sauer 2011-05-18 02:22:56 UTC
Also here. It's a nasty bug. I see this Bug as critical, because major database operations are impossible.
Comment 3 Alex Thurgood 2011-05-18 08:02:51 UTC
Hi Juergen,

The workaround, until someone decides to fix it, is to use the JDBC3 driver, which still works.

Alex
Comment 4 Alex Thurgood 2011-05-18 08:04:37 UTC
Correcting the title to reflect the actual issue, which is one of the SDBC postgres driver and not being unable to work with postgres in general.

It is also not a blocker if you can work with another driver in the meantime.

Alex
Comment 5 Alex Thurgood 2011-05-18 08:05:41 UTC
Affects all platforms. Changing platform status accordingly.

Alex
Comment 6 Alex Thurgood 2011-05-24 03:01:57 UTC
Setting CONFIRMED status on whiteboard.

Alex
Comment 7 Ferry Toth 2011-06-05 02:17:31 UTC
This reported also on launchpad:

https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/782292

Ferry
Comment 8 Ferry Toth 2011-06-05 02:21:03 UTC
I think this bug should be a blocker.

The alternative JDBC driver does work but is 10x slower. The performance difference is so big that it cannot be considered a 'real' alternative.

See for details: https://bugs.freedesktop.org/show_bug.cgi?id=35944

(comment 8)

Ferry
Comment 9 Ferry Toth 2011-06-05 02:22:41 UTC
Maybe related problem:

Setting the 'properties' of the database (via menu: <edit><database>) is not possible anymore.

I suspect this is unintentionally disabled.

Ferry
Comment 10 Ferry Toth 2011-06-05 02:26:02 UTC
Are we depending on jbu (OO) to fix this bug?
Comment 11 Alex Thurgood 2011-06-05 02:57:08 UTC
Hi Ferry,

Probably - there is no one in the LibO dev team at the moment with a particular interest / availability for fixing DB related issues, so it is not just postgres. 

I can not even find the postgres SDBC driver source code anymore (the connectivity/postgres directory has disappeared from the LibO repo), so unless Joerg or someone else is willing to help out then it might just die.


Alex
Comment 12 Ferry Toth 2011-06-05 04:43:15 UTC
Alex, do you mean that LO is only half a fork from OO?

That would be a bad thing, which would force me to go back to OO, or at least go-oo 3.2.1.

If needed I would build OO 3.2.1 in a Ubuntu ppa just to make sure I do not loose database functionality.

BTW: sources for postgresql SDBC are part of Debian and Ubuntu as they build a package libreoffice-sdbc-postgresql from their libreoffice source package.

Ferry
Comment 13 Cor Nouws 2011-06-05 11:46:49 UTC
added to #35673 and will try to wake some initial interest from devs.
Comment 14 Ferry Toth 2011-06-16 13:12:49 UTC
I set regression as this problem did not occur in go-oo 3.2.1.

I can also confirm it to occur on Linux x86/x86-64 and Windows.

Ferry
Comment 15 Ferry Toth 2011-06-26 10:30:31 UTC
I just tried a little LO Basic code, to help me understand how all this
postgres driver code is supposed to work internally.

There seems to be a problem with the privileges, but
setting IgnoreDriverPrivileges on the driver doesn't seem to work. When a resultset is created it seems to have the wrong privileges.

I have the sources here and they do build, but I have no clue which sources changed from 3.2.1 to 3.3.2. Which sources files should I look at?

Ferry

Note: table TestTable is empty

This is the code:
Sub Main
   dbcontext = createunoservice("com.sun.star.sdb.DatabaseContext")
   dbsource = dbcontext.getByName("Exalon Office")

'putting a watch here on dbsource.Settings.IgnoreDriverPrivileges shows
this == TRUE
'on OO3.2.1 this is by default, on LO3.3.2 this is manual by setting in
the XCU file as you suggested

   dbsource.settings.EscapeDateTime = false    ' old hack donated by FS

' test starts here
   RowSet = createUnoService("com.sun.star.sdb.RowSet")
    RowSet.DataSourceName = "Exalon Office"
    RowSet.CommandType = com.sun.star.sdb.CommandType.TABLE
    RowSet.Command = "public.TestTable"
    ResultSetType= com.sun.star.sdbc.ResultSetType.SCROLL_INSENSITIVE

ResultSetConcurrency=com.sun.star.sdbc.ResultSetConcurrency.UPDATABLE
    RowSet.execute()
    RowSet.moveToInsertRow()

'on OO3.2.1 this command completes
'on LO3.3.2 this command fails with and com.sun.star.sdbc.SQLException
' Message: privilege INSERT is not available
'inspecting Rowset.Privileges shows
' on OO3.2.1 == 511
'on LO3.3.2 == 1
'the Rowset.Privileges is RO, so I can not change it

End Sub
Comment 16 Lionel Elie Mamane 2011-08-09 14:14:54 UTC
I'm working to improve the postgresql native sdbc driver overall, but in the meantime, here's a minimal patch that fixes this particular bug.

You need to also increment the version number, or LibreOffice won't install / register the new oxt file.
Comment 17 Lionel Elie Mamane 2011-08-09 14:16:38 UTC
Created attachment 50080 [details]
minimal patch
Comment 18 Ferry Toth 2011-08-15 01:12:25 UTC
Lionel,

I have the sources of LO 3.3.4 and are not exactly sure how to a apply a patch so that the version number increases.

Instead I just made the patch manually while the build of LO commences (plenty of time to do that :-) ) resulting in a deb with the same version number as in the repository. This I 'reinstalled', and it appears to solve the problem.

Thanks so much.

I did discover some new regressions (fixed in the past by Frank Schonheit): applying filters on 'date' type colums do not seems to work, neither in forms nor tables.

This filters are quite useful to users to 'drill down' the data in the tables to find a particular record and it would be nice if these would work.

Ferry
Comment 19 Ulf Mehlig 2011-09-04 12:37:39 UTC
(In reply to comment #8)
> The alternative JDBC driver does work but is 10x slower. The performance
> difference is so big that it cannot be considered a 'real' alternative.

same here:

https://bugs.launchpad.net/ubuntu/+source/libreoffice/+bug/835662
Comment 20 Lionel Elie Mamane 2011-09-07 09:47:03 UTC
(In reply to comment #18)

> I have the sources of LO 3.3.4 and are not exactly sure how to a apply a patch
> so that the version number increases.

FWIIW, this was about the version number of the postgresql-sdbc extension, not of LO.

> Instead I just made the patch manually while the build of LO commences (plenty
> of time to do that :-) ) resulting in a deb with the same version number as in
> the repository. This I 'reinstalled', and it appears to solve the problem.

Ah yes, if you go the .deb route, it works. I was thinking of rebuilding just postgresql-sdbc and installing the resulting .oxt in LibreOffice. If the version number has not changed, LO does not do it; one has to either uninstall and install,or use "unopkg add --force".

> I did discover some new regressions (fixed in the past by Frank Schonheit):
> applying filters on 'date' type colums do not seems to work, neither in forms
> nor tables.

Fixed; see http://wiki.documentfoundation.org/PostgreSQL-SDBC for how to get the new source code.
Comment 21 Ferry Toth 2011-10-09 13:08:35 UTC
I just downloaded the Kubuntu 11.10 with LO 3.4.3 and the SDBC driver 0.8.

With this I still can not add/modify records (as opposed to the minimal patch in comment #17 which did solve the problem).

Things that I can think of that cause this:
- I am not using a password on the database server
- The psql version on the server is 8.3
- I might need to set IgnoreDriverPrivileges

Any/all tips appreciated.

Ferry
Comment 22 Lionel Elie Mamane 2011-10-10 01:14:41 UTC
(In reply to comment #21)
> Kubuntu 11.10 with LO 3.4.3 and the SDBC driver 0.8.

> With this I still can not add/modify records (as opposed to the minimal patch
> in comment #17 which did solve the problem).

> Things that I can think of that cause this:
> - The psql version on the server is 8.3

This is the most likely culprit in my opinion. I use the array function "unnest" in the driver, and "unnest"  appeared in PostgreSQL 8.4. Take a look at http://wiki.postgresql.org/wiki/Array_Unnest, it contains instructions on how to add "unnest" to PostgreSQL 8.3 and lower.

> - I might need to set IgnoreDriverPrivileges

This might indeed work around this particular issue, but I think adding "unnest" function will be more robust, as it is used also in other parts of the driver.
Comment 23 Ferry Toth 2011-10-12 09:24:17 UTC
Lionel,

I can confirm this driver works (tested with Kubuntu Oneric beta) with postgres 8.4 from debian squeeze (stable).

Thank you so much!

I believe Base connected to postgresql 8.4 now to be giving the best functionality and performance using the sdbc driver (compared to hsql, mysql, sqlite using jdbc, odbc or sdbc drivers).

I guess this combination will not be available to windows/mac users until someone builds the extension and puts it one the extension website.

I must admit although we have a MS compiler here we have no clue what the effort would be to build the driver for windows and package it into an extension.

Ferry
Comment 24 Lionel Elie Mamane 2011-11-23 02:03:30 UTC
We are trying to get my new PostgreSQL-SDBC (which has this bug fixed) directly integrated in LibreOffice 3.5 beta0. Stay tuned.
Comment 25 Ferry Toth 2011-11-23 13:27:43 UTC
That is certainly good news for my Windows using colleagues. As a Kubuntu user I'm am already happy of course.

Is there a todo list for the driver?

Ferry
Comment 26 Lionel Elie Mamane 2011-12-04 15:29:57 UTC
As this has been fixed, closing bug.

Fixed in PostgreSQL-SDBC v0.8.0 for LibreOffice 3.4.

Fixed in PostgreSQL-SDBC v0.8.1 for LibreOffice 3.5.