Base cannot add/edit records using postgres sdbc driver

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

Also known as OOo bug :

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

Alex

Also here. It's a nasty bug. I see this Bug as critical, because major database operations are impossible.

Hi Juergen,

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

Alex

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

Affects all platforms. Changing platform status accordingly.

Alex

Setting CONFIRMED status on whiteboard.

Alex

This reported also on launchpad:

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

Ferry

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

Maybe related problem:

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

I suspect this is unintentionally disabled.

Ferry

Are we depending on jbu (OO) to fix this bug?

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

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

added to #35673 and will try to wake some initial interest from devs.

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

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

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.

Created attachment 50080
minimal patch

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

(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

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

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

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

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

We are trying to get my new PostgreSQL-SDBC (which has this bug fixed) directly integrated in LibreOffice 3.5 beta0. Stay tuned.

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