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 [details] 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
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.