Ubuntu does add the SDBC driver for postgresql.

Unfortunately it seems to broken currently and only allows retrieving records (fast) but not add/update.

I have been using Base with Postgresql for years now. The working SDBC driver is absolutely essential for a responsive and reliable database implementation.

Ferry

setting confirmed status. 

The SDBC postgres driver no longer currently functions to allow updates/deletes/new entries in any version of LibO, nor in the latest versions of OOo.

It needs to be recompiled / debugged with a current source tree.


Alex

*** Bug 33236 has been marked as a duplicate of this bug. ***

Please note that 33326 is not a dup, that was my mistake.

Alex

*** Bug 35944 has been marked as a duplicate of this bug. ***

(In reply to comment #5)
> *** Bug 35944 has been marked as a duplicate of this bug. ***

Forget the dup setting, read the reports too quickly.

Alex

I'm working on it. I'll release shortly a version of postgresql-sdbc that works with LibO 3.4, including updating / adding / deleting records. Still beta quality, but at least we are back to the functionality we had with OOo 3.2 (and then some more).

I'd appreciate if someone could compile and test it on other platforms, for example Apple Darwin / MacOS X and Microsoft Windows; I use Debian GNU/Linux.

Lionel,

I have applied your 'minimal patch'  (https://bugs.freedesktop.org/show_bug.cgi?id=35784 comment #16) to 
LO 3.3.4 from Ubuntu (current) and can confirm this works. With this amazingly small patch you can now again edit/modify records. Thanks so much.

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

I will be happy to do further testing as you progress.

Ferry

(In reply to comment #7)

> 
> I'd appreciate if someone could compile and test it on other platforms, for
> example Apple Darwin / MacOS X and Microsoft Windows; I use Debian GNU/Linux.

Hi Lionel,

I can test compile on Mac OS X, but I need to reclone to the new one-git repo first.

Alex

(In reply to comment #8)

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

I traced that back to LibreOffice using SQL syntax not supported by PostgreSQL, namely ODBC escapes for date literals (like that: {D 2011-08-28}). I'd be curious how Frank Schonheit fixed that "in the past"; if you have a more precise pointer (a link to the patch itself would be the best), it would be useful to me.

I see two ways to approach this:

 - have the SDBC driver parse the SQL and translate to an SQL syntax supported by PostgreSQL; that's for example what the PostgreSQL ODBC driver does

 - change LibreOffice to not use these syntax elements, but for example prepared statemants and the "setDate" function of the XParameters interface.

Google a bit I found:

http://www.mail-archive.com/users@dba.openoffice.org/msg04471.html

This traces bck to OO issue 78988:

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

The trick was to change a setting per database using a macro (that needs to be run only once per database):

Basic Macro to set the 'EscapeDateTime' property to 'false'

Sub Main
    dbcontext = createunoservice("com.sun.star.sdb.DatabaseContext")
    dbsource = dbcontext.getByName("NameOfYourDB")
    dbsource.settings.EscapeDateTime = false
End Sub

Not sure if this works with current LO.

Ferry

(In reply to comment #11)

I executed the macro

> Sub Main
>     dbcontext = createunoservice("com.sun.star.sdb.DatabaseContext")
>     dbsource = dbcontext.getByName("NameOfYourDB")
>     dbsource.settings.EscapeDateTime = false
> End Sub

but SDBC still does not add/modify records (using LibreOffice 3.3.3 and the corresponding libreoffice-sdbc-postgresql from Ubuntu 10.04).

Ulf

(In reply to comment #10)
> I traced that back to LibreOffice using SQL syntax not supported by PostgreSQL,
> namely ODBC escapes for date literals (like that: {D 2011-08-28}). I'd be
> curious how Frank Schonheit fixed that "in the past"; if you have a more
> precise pointer (a link to the patch itself would be the best), it would be
> useful to me.

Maybe I misunderstood something, but for me adding/modifying records via SDBC-postgresql also does not work for tables not containing any date columns.

Ulf

Ulf,

The macro does not fix the sdbc driver problem adding/editting records.

For this you need the patch in https://bugs.freedesktop.org/show_bug.cgi?id=35784

(comment #17).

The macro (at least in the past) made filters work when filtering on date fields.

Ferry

(In reply to comment #11)
> I found:

> http://www.mail-archive.com/users@dba.openoffice.org/msg04471.html

> The trick was to change a setting per database using a macro (that needs to be
> run only once per database):

> Basic Macro to set the 'EscapeDateTime' property to 'false'

> Sub Main
>     dbcontext = createunoservice("com.sun.star.sdb.DatabaseContext")
>     dbsource = dbcontext.getByName("NameOfYourDB")
>     dbsource.settings.EscapeDateTime = false
> End Sub

Note in passing: since OOo 3.0, this can be simplified and robustified as:

Sub Main
    ThisDatabaseDocument..DataSource.settings.EscapeDateTime = false
End Sub

> Not sure if this works with current LO.

Not only it works, but I have changed the postgresql-sdbc driver to make that automatically, in a non-sticky way (if the database is changed to another database type, the setting won't stay, unless it has been done with the macro above).

And my updated postgresql-sdbc driver is now ready to be tested/used by others; see http://wiki.documentfoundation.org/PostgreSQL-SDBC for how to get it.

Alex, let me know if compile & test on MacOS X works out fine. Thanks in advance. If you compile or run against master (as opposed to libreoffice-3-4), make sure you are after commit 5b80048a1ef119db569f9e9a259e94e773201b1f (2011-09-07 12:09) or before commit fad1d1e538ae73b3750a201b3b429e89a944ef7f (2011-08-13 07:04).

Would it be an idea to have the driver built in a Ubuntu PPA?

That way more users would benefit and we might be able to get the driver to be part of the upcoming Oneric release.

Note that the sdbc driver has always been a part of OO and LO on that platform.

Ferry

(In reply to comment #16)
> Would it be an idea to have the driver built in a Ubuntu PPA?

That's up to Ubuntu people. I'll put it in Debian, though. So AFAIK it should trickle down to Ubuntu sooner or later, when they syncrhonise with Debian.

Just for the record: the new driver is in Debian and Ubuntu now and good!

So me, I'm happy, but my colleagues are still restricted to using OO 3.2.1.

So the topic is still valid, can't the driver be made part of LO? Are there licensing restrictions that prohibit this?

I guess there are at least as much windows users out there with LO?

Ferry

(In reply to comment #18)

> So the topic is still valid, can't the driver be made part of LO?

It looks like it will be part of LO 3.5.

> Are there licensing restrictions that prohibit this?

The code is LGPL2.1-only.

I thought that LO's policy was to take only LGPL3 (or LGPL3+) code, but it turns out the project is OK with having LGPL2.1-only code in LO proper. So I'm preparing to add it in LO proper; it will most likely happen in time for LO 3.5. I hope to make the commit on Wednesday 16 november 2011.

PostgreSQL-SDBC is now in the LibreOffice source tree. Configure option "--enable-ext-postgresql-sdbc" enables it to be bundled into the installation.

Since all new unconfirmed bugs start in state UNCONFIRMED now and old unconfirmed bugs were moved to NEEDINFO with a explanatory comment, all bugs promoted above those bug states to NEW and later are automatically confirmed making the CONFIRMED whiteboard status redundant. Thus it will be removed.