LO Base not handling computed/generted/calculated column properly

[jpage]

Scenario: SQLite back end database accessed by Base via ODBC connection. Works well except when there is a generated column defined on the back-end table.

Symptoms:

1. Examining the table, the column content is displayed. When entering data in a column following the generated column, the input data shows up in the column after the modified column (when no trigger on the underlying table), or an error if the modified column is the last one in the row.
2. Accessing the table through a form, the generated column is not available.

I suspect that Base does not recognize the column type and that this causes the errors I have observed.

[Wanderer]

@jpage

Works well except when there is a generated column defined on the back-end table.

Never had problems, neither for queries nor in joins or views.
.
Can you share the SQL you use to generate your column?
.
If you problem happened to me (especially in a form) I’d guess my form is wired to the wrong source, but I’m sure you ruled this out before posting here.

[jpage]

The problem seems to be that LO does not like generated/computed columns.

My best solution is to use a view to mask the underlying table off from LO, then use the view in place of the table. This assumes that the problem being solved does not want to update the underlying table (then a trigger is needed in the backend table).

[RobertG]

If using internal databases or databases with a driver from LO (MariaDB, PostgreSQL) generated columns will work well in Base. It is a buggy behavior of the connection type.

Connected to SQLite by JDBC connection and created a table like this:

CREATE TABLE "tbl_Personen" (
 "ID" INTEGER PRIMARY KEY AUTOINCREMENT,
 "Name" TEXT NOT NULL
);

through Tools → SQL.
There isn’t shown in field “ID” (because Base doesn’t know anything about the autoincremented value) but I could add many new rows while only filling “Name” and the “ID” will automatically be generated. Special here: If I delete all content of the table it will automatically restart with “ID” = 1.

[Wanderer]

@RobertG

Special here: If I delete all content of the table it will automatically restart with “ID” = 1

Never seen, but I use ODBC.
IMHO your observation contradicts the documentation of Sqlite:

the purpose of AUTOINCREMENT is to prevent the reuse of ROWIDs from previously deleted rows.

https://www.sqlite.org/autoinc.html

But my guess is @jpage was not referring to autoincrement, but to more complex formulas. As I generate this by queries I never had problems there…

https://sqlite.org/gencol.html

[CRDF]

Here apparently it’s working.
Data source of form = query SELECT * FROM g
Months 7 - 6 were inserted via the Table GUI.
5 - 4 via the Form.
 
ODBC
LO: 24.2.7.2 (X86_64) / LibreOffice Community
Ubuntu package version: 4:24.2.7-0ubuntu0.24.04.4

[jpage]

It appears to be inconsistent. I did place the generated column at the end of the row, and got your results, but when the generated column is in the middle of the row, LO seems to lose track. Try changing the column order so that ma is not at the end, then open the table (in Base) and modify a row.

[CRDF]

Here it worked.
Inserted 10 | 9 | 2025 directly at DB.
Subsequent inserts did them in the Form, and also updated row 1.

 
For another table g
| r | dm | dia | mes |
Form generator really losed track, so I needed to explicit columns in the query
SELECT r, dm, dia, mes FROM g;