Several database backends for a single LibreOffice Base file?

[BsalocinN]

I’m willing to use LibreOffice Base as a desktop frontend to a remote shared (PostgreSQL) database. Since I am new to LibreOffice Base, I bought and read the “LibreOffice 4.0 Base Handbook”.

As I understand it, if I want to implement a search or a filter in a Form, the form has to be backed by a database table, and the search parameter by a column in that table. The standard way to do seems to be to have a single-row table. In a context of a shared database engine, that sound like bad design to me, to store such “runtime” data in the shared database, especially if several users may use the same ODB frontend simultaneously…

Is there some way to have some “runtime” data that is not backed in the database?

As an alternative, is it possible to have several database backends for a single ODB file? I’d be happy if I could store the main data in the remote PostgreSQL database, and the “runtime” data in a local HSQLDB.

Another option would be to create a temporary table, private to the connection, when I connect to the remote PostgreSQL database. I guess it might be possible with a macro.

For the record, I’m currently using LibreOffice 5.2.7 on a Debian Stretch 9.8 system, but upgrading to a newer LibreOffice is an option if it helps.

[Ratslinger]

Hello,

Please explain what “runtime” data is that you seem to refer to. Base simply requests access and updates of data from the database it is connected to. Base itself is NOT a database, but just a front end to a database.

[BsalocinN]

@Ratslinger What I call “runtime” data is things like, for example, the content of the input field (in a Form) where a librarian will type the name of a reader in order to find the right entry in a huge Reader table. I see no reason why this should be backed in the database, especially if several librarians may be using the same database simultaneously.

[Ratslinger]

Not clear. Don’t really get the problem you seem to have. Typing data in a form to search a table to retrieve a record has nothing to do with the actual database.

Need better explanation. Multi user operation should not be a problem. That is a task of the database itself.

Searching a table for a specific record is nothing more than a simple query. This is typically done from a form using a list box or text field to get the data for the query. Has nothing to do with the DB itself or other users.

[BsalocinN]

@Ratslinger Well… If I have a look at the “Loan” form in the Media_without_Macros.odb Base file, there is a “Textbox 1” field (below label “Filter (Last Name)”), within the “Filter” form.

As far as I can see, the “Filter” form is backed by a table called “Filter”. That table has a single row. The “Textbox 1” field is backed by the “Filter” column of that “Filter” table…

If there is a way I can implement such a filter without that “Filter” table, I’d be happy to know how.

[Ratslinger]

Hello,

Thank you for the explanations. Seems my head was a bit cloudy and couldn’t piece together what you were looking for.

This is what I understand you are asking. Documentation states to filter a selected record use a Table Filter. And yes this works. But you are looking at a multi user environment where two or more will be trying to filter also and having this single record in the Table presents a problem. That I can now see as your question - How to deal with this.

Without going goofy with some crazy table scheme, the best way to accomplish this (and my opinion the easiest) is to use a macro. The macro is attached to your input field (list box, text field or other). An event is set for that control to detect when a selection has been made and it triggers the macro. This macro reads the value, moves it into the internal forms filter property and refreshes the form which selects the appropriate record.

This type of macro is very small and needs to be adjusted for your field(s) and form(s). The sample code is in my answer on this post → filter from control value. There is also a working sample in the answer which is based on a list box.

You also asked about using multiple back ends which now makes sense here. While this is possible, the above macro is a much better solution.

[BsalocinN]

Many thanks for your help, and sorry if my question was unclear. I haven’t tried yet but that solution with a macro looks fine.I just did not figure out it was possible to have a List Box not backed with a Data field.
Anyway, I’m curious about your last paragraph: how can one use several database back-end?

[Ratslinger]

As stated, it was more my problem of the hazy vision. Sometimes just can’t see what is in view. FOG!

About multiple databases in a Base file. Mostly through macros. You can access many different DB’s and even DB types (MySQL, PostgreSQL etc.) in a macro just with different connections.

A lot you can do with macros but macros require a lot of learning and research.

[BsalocinN]

I just implemented it, and I confirm that it works fine with a (trivial) macro. Thanks again!