LibréOffice Base Forms, PostgreSQL (JDBC) and Table Joins

Jim_Seymour · February 22, 2014, 6:17pm

Hi There,

I've researched and experimented with this, and I'm out of ideas.

For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2)

Before anybody suggests a newer version: I'm currently on Ubuntu
10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer
Linux Mint desktop install at work is still 3.x, I believe.)

I have a database with a couple tables, as in

create table hdwr_assets (
  atag text primary key,
  serno text unique,
  status_id int references hdwr_status,
  .
  .
  .
);

-- e.g.: "deployed", "in stock"
create table hdwr_status (
status_id serial primary key,
descr text not null,
);

The plan was to create a form that could query and alter hardware
assets, selecting values for status_id from a drop-down list, the
available selection being from the hardware status table.

Near as I can tell: The Forms Wizard simply isn't that smart?

Interestingly: A Query created in Design View DTRT, wrt FK
references, but I can't persuade the Forms Wizard to do so. In fact:
FK references in the Forms Wizard appear to be thoroughly broken--at
least in the version of LO I have?

Is there any way to accomplish what I want, or am I going to have to
resort to Real Coding (Java, HTML+PHP+JS or whatever)?

Thanks,
Jim

mcmurchy1917-libreof · February 23, 2014, 9:22am

Jim

Is it that you want a single form, comprised of sub forms, whereby

you can filter assets according to their status i.e. "deployed" or "in stock";
from the results given select a specific asset which presents the result, of that one asset, in a sub form from which the asset can be amended?

Alex

Jim_Seymour · February 23, 2014, 3:05pm

Alex,

Thanks for the follow-up.

Ideally there'd be one form for the asset(s), wherein each value
could be entered or altered, the ones being foreign key references
presenting a list from the desired column in the table to which the
FK references.

For example: I can do an ad hoc query that looks roughly like

select ..., s.desc as "status", ... from hdwr_assets h
left join hdwr_status s on s.status_id = h.status_id...;

and a field named "status" will show in the output.

Ideally, I'd like to be able to do that. In fact: I *can* do that in
Queries -> Create Query in Design View), but not in a form?

Behind the scenes: When you clicked on a list box for "status" in the
assets form, a list derived from "descr" in the status table would be
presented. When a selection is made, the related status_id value
would be placed in the status_id field for the selected hardware
asset.

A sub-form would (could?) be presented, allowing one to alter the
status table.

Btw: When I try to associate hdwr_assets.status_id to
hdwr_status.status_id using the Forms Wizard, execution results in

    Error: The data content could not be loaded.
    Error: SQL Status: 22023
     The column index is out of range: 1, number of columns: 0.
    Information: The SQL command leading to this error is:

SELECT * FROM "public"."hdwr_status" WHERE ( "status_id"
= :link_from_status_id )

Which is not valid SQL.

I would have thought this functionality would be present. Even the
most basic of database normalization in the most trivial of databases
is going to result in multiple tables and FK references.

Thanks,
Jim

Alex_Kempshall · February 23, 2014, 4:42pm

Jim

You stated that you could generate a query to do what you require. Have you based your form on the query?

Alex

Jim_Seymour · February 23, 2014, 10:24pm

[N.B.: Switching to in-line reply format to eliminate the cruft and
because the question stands on its own.]

Jim

You stated that you could generate a query to do what you require.
Have you based your form on the query?

[big snip]

Thanks for the follow-up, Alex. (Another "Alex." I'm sensing a
trend, here ;).)

If I understand you correctly: Yes. In fact: I made it far simpler
than queries I can easily do ad hoc with the PostgreSQL command-line
interface (psql).

For example: Here's a VIEW (a VIEW, in Postgresql, is a query that's
saved, such that it looks like a table) that employs several LEFT
JOINs, the last three of which are simply to "get to" the hostname(s)
associated with a hardware asset:

SELECT a.atag, h.hostname, a.owner, s.descr AS status, d.mfgr,
d.model, a.comments FROM hdwr_assets a
   LEFT JOIN hdwr_status s ON s.status_id = a.status_id
   LEFT JOIN asset_descr d ON d.descr_id = a.descr_id
   LEFT JOIN mac_addrs m ON m.atag = a.atag
   LEFT JOIN ip_addrs i ON i.mac_addr = m.mac_addr
   LEFT JOIN hostnames h ON h.ip_addr::inet = i.ip_addr::inet;

Why the indirection/abstraction? Because there can be multiple MAC
addrs/asset, multiple IPs/MAC, and multiple hostnames/IP.

*That* might be a bit much to expect of a graphical query/form design
tool. (Tho it sure would be nice if it was handled.)

All I'm trying to do, so far, is "a single level of indirection," as
it were. (That is also in that query. You can see it in the
's.descr as "status" and "ON status_id" bits.)

Regards,
Jim

iplaw67 · February 24, 2014, 10:00am

Hi Jim,

For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2)

Before anybody suggests a newer version: I'm currently on Ubuntu
10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer
Linux Mint desktop install at work is still 3.x, I believe.)

You should be able to install newer versions even on Ubuntu 10.04 LTS,
if you download them from the LibreOffice website. Of course, they won't
necessarily have full desktop integration, although Unity came after
10.04, so it should be OK.

As for Linux Mint, I have Mint 15 and 16 on two separate machines. Both
have (at least I think they do) more recent packaged versions of LO in
their repos than 3.5.7 (Mint 16 : LO 4.1.3.2). Anyway, as that is not
necessarily an immediate solution to your problem, on to the meat of the
question :

I have a database with a couple tables, as in

create table hdwr_assets (
  atag text primary key,
  serno text unique,
  status_id int references hdwr_status,
  .
  .
  .
);

-- e.g.: "deployed", "in stock"
create table hdwr_status (
  status_id serial primary key,
  descr text not null,
);

The plan was to create a form that could query and alter hardware
assets, selecting values for status_id from a drop-down list, the
available selection being from the hardware status table.

Does the LEFT JOIN query with a single join actually run from the Query
Designer or do you get an error ?

Does it make any difference if you turn off/on the Direct SQL button
(which causes the query to bypass the internal LO SQL parser, or
alternatively, ask it to interpret the query before fielding it to the
JDBC driver) ?

What are the settings in the "Advanced Options" of your db connection
for that ODB file ?

Which driver version of JDBC are you using ?
Which version of postgres ?

Alex (yes, yet another)

Jim_Seymour · February 24, 2014, 2:24pm

Hi Jim,

>
> For starters: LibréOffice 3.5.7.2, Build ID: 350m1 (Build: 2)
>
> Before anybody suggests a newer version: I'm currently on Ubuntu
> 10.04 LTS (Lucid Lynx), so I'm stuck with it. (Even my much newer
> Linux Mint desktop install at work is still 3.x, I believe.)

You should be able to install newer versions even on Ubuntu 10.04 LTS,
if you download them from the LibreOffice website. Of course, they
won't necessarily have full desktop integration, although Unity came
after 10.04, so it should be OK.

I followed the instructions for adding the proper repository and
installed with apt-get. 3.5.7.2 is what it gave me.

As for Linux Mint, I have Mint 15 and 16 on two separate machines.
Both have (at least I think they do) more recent packaged versions of
LO in their repos than 3.5.7 (Mint 16 : LO 4.1.3.2).

I have Mint 13 at work. It isn't all *that* old. Only installed about
a year ago, if memory serves. Yes: I see 15 and 16 are now available.
Wow.

Anyway: 3.5.7.2 at work, on my Mint 13 machine, as well.

I have 4.1.something under MS-Win on my work laptop. Haven't played
with that, yet. (Have yet to work out the self-signed CA cert with
keytool under MS-Win.)

Anyway, as that
is not necessarily an immediate solution to your problem, on to the
meat of the question :

>
> I have a database with a couple tables, as in
>
> create table hdwr_assets (
> atag text primary key,
> serno text unique,
> status_id int references hdwr_status,
> .
> .
> .
> );
>
> -- e.g.: "deployed", "in stock"
> create table hdwr_status (
> status_id serial primary key,
> descr text not null,
> );
>
> The plan was to create a form that could query and alter hardware
> assets, selecting values for status_id from a drop-down list, the
> available selection being from the hardware status table.
>

Does the LEFT JOIN query with a single join actually run from the
Query Designer or do you get an error ?

The left join works in Query Designer. I get the same thing as I would
with an ad hoc query via psql.

Does it make any difference if you turn off/on the Direct SQL button
(which causes the query to bypass the internal LO SQL parser, or
alternatively, ask it to interpret the query before fielding it to the
JDBC driver) ?

I did not try those things. Didn't know about them. I'll look into it.

What are the settings in the "Advanced Options" of your db connection
for that ODB file ?

"Edit -> Advanced Settings -> Database -> Special Settings", perhaps?

Options checked are:

    Ignore the privileges from the database driver
    Use catalog name in SELECT statements
    Use schema name in SELECT statements
    Create index with ASC or DESC statement
    Form data input checks for required fields
    Use ODBC conformant data/time literals

Comparison of Boolean values: Default

Which driver version of JDBC are you using ?
Which version of postgres ?

PostgreSQL v8.4.19 w/postgresql-jdbc4-9.1.jar at work
PostgreSQL v8.4.19 w/postgresql-jdbc3-8.4.jar at home

Behaviour is the same at both locations.

Alex (yes, yet another)

LOL!

Thanks for the follow-up, Alex the III

Regards,
Jim

iplaw67 · February 25, 2014, 8:18am

What are the settings in the "Advanced Options" of your db connection
for that ODB file ?

"Edit -> Advanced Settings -> Database -> Special Settings", perhaps?

Options checked are:

    Ignore the privileges from the database driver
    Use catalog name in SELECT statements
    Use schema name in SELECT statements
    Create index with ASC or DESC statement
    Form data input checks for required fields
    Use ODBC conformant data/time literals

Comparison of Boolean values: Default

OK, so probably not where the problem lies.
Not sure about the "Form data input checks for required fields" option
though. If the primary key of your subform requires input/updating and
this is not bound to the form, perhaps that might explain where your
error is coming from.

Which driver version of JDBC are you using ?
Which version of postgres ?

PostgreSQL v8.4.19 w/postgresql-jdbc4-9.1.jar at work
PostgreSQL v8.4.19 w/postgresql-jdbc3-8.4.jar at home

There was an issue with writing to postgresql servers using versions
prior to 8.4, so that shouldn't be the problem here.

Alex

iplaw67 · February 25, 2014, 8:42am

Hi Jim,

A sub-form would (could?) be presented, allowing one to alter the
status table.

Btw: When I try to associate hdwr_assets.status_id to
hdwr_status.status_id using the Forms Wizard, execution results in

    Error: The data content could not be loaded.
    Error: SQL Status: 22023
     The column index is out of range: 1, number of columns: 0.
    Information: The SQL command leading to this error is:

     SELECT * FROM "public"."hdwr_status" WHERE ( "status_id"
     = :link_from_status_id )

How are your table indexes defined ? Are they "multiple column" indexes?

Which type of UI control are you using in your form to display the
result - Combobox or ListBox ?

You might be better off trying to do this manually rather than via the
wizard, i.e. in Form design mode using the Form Navigator to create your
subforms and then binding the corresponding data fields to UI controls,
as the subforms wizard hasn't always produced the expected results
throughout the various versions of LO.

At the moment, I can't tell whether this is a bug in the version of LO
that you are using, or whether it is a combination of other factors
(type of UI control in form, binding of data field to UI control, etc).

Alex T

iplaw67 · February 25, 2014, 9:00am

Hi Jim,

A sub-form would (could?) be presented, allowing one to alter the
status table.

Btw: When I try to associate hdwr_assets.status_id to
hdwr_status.status_id using the Forms Wizard, execution results in

    Error: The data content could not be loaded.
    Error: SQL Status: 22023
     The column index is out of range: 1, number of columns: 0.
    Information: The SQL command leading to this error is:

     SELECT * FROM "public"."hdwr_status" WHERE ( "status_id"
     = :link_from_status_id )

Which is not valid SQL.

I just found this :

https://forum.openoffice.org/en/forum/viewtopic.php?f=13&t=47100

where others are having the same problem, and which goes back initially
to 2012, and concerns Apache OpenOffice, but much of the code (and
unexpected behaviour) like that reported here, is still shared with
LibreOffice. Independent developments in the LibreOffice code have
further compounded, or added to, the existing bugs.

It doesn't look like this particular question was solved, at least the
question hasn't been marked as such, but it does suggest to set up the
form and subforms manually rather than by using the subform wizard.

Alex Thurgood

iplaw67 · February 25, 2014, 9:09am

In fact, further reading of the post shows that people using
OpenOffice.org 3.1, Apache OpenOffice 4.01 and LibreOffice have all had
this problem.

Alex

iplaw67 · February 25, 2014, 11:35am

Hunting this down a bit more :

https://issues.apache.org/ooo/show_bug.cgi?id=114280#c6

so it would appear that there should be an extra option in JDBC
connection settings "Advanced Settings" dialog that allows you to switch
on "replace named parameters with '?'

At least, that was the case for OOo 3.3.0.

If it is no longer there, then that option was either deleted from the
dialog for some reason, or the code that reads the available driver
options fails (for some other reason) or else ignores that setting
possibility and so is not present in the UI dialog.