Or simpler: you could copy tables plots + plot into an embedded, preferably Firebird, db.
Just this.
And we can test best way to do persistence of selected row id field…
I’ve been working with libre office base and its gui. I don’t know how to do what you are suggesting. I’m willing to learn if there is no other way and because I have a decent grasp on libreoffice base I’d rather do it there if it’s not to hard.
Jonathan Francoeur a.k.a. Kernel Hazelnut
Everything Edible Landscaping H.Q.
48.381980, -123.707835
250 642 0267
6411 Sooke Rd., Sooke, B.C., Canada, V9Z 0A8
did an exercise here; but as I don’t have your form layout, it’s speculative…
[plot_id] persisted in table’s PLOT only (pk) ID field.
Say you have a TableControl for PLOTS.
Select the row. If update msg does not show up, insert cursor in the [plant_id] control and leave it (TAB or Enter).
Event = When losing focus
Sub PersistID(Evt As Object)
REM DECLARE THE VARIABLES:
Dim F As Object, ID As Object, Con As Object, Stmt As Object
Dim sSQL As String, plot_id As String
Dim resultado As Integer
REM SET REFERENCES:
ID = Evt.Source.Model
F = Evt.Source.Model.Parent.Parent
Con = F.ActiveConnection()
Stmt = Con.createStatement()
REM BUILD SQL UPDATE AND PERSIST Plot #:
plot_id = CStr(ID.CurrentValue) ' plot id to persist
sSQL = "UPDATE ""PLOT"" SET ""ID"" = '" & plot_id & "'"
resultado = Stmt.executeUpdate(sSQL)
print resultado & " record(s) updated to " & plot_id
End Sub
ActiveConnection is the connection that your form that holds the controls – TableControl or text boxes – have to the DB.
Because of these things I suggested you to upload an embedded simplified version of your DB, with just what matters for the question.
Here in my “exercise” I have mine of course.
Tried to send a screen record .mkv but was not authorized
possibly this will solve the error:
*you need to tell the database where the files are: Go to Edit>Database>Properties: and change the file address to reflect your setup
for example:
hsqldb:file:////home/jonathan/Dropbox/LANdpLAN/LANdpLAN/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=c
becomes
hsqldb:file:////home/workaway/Dropbox/LANdpLAN/LANdpLAN/mydb;default_schema=true;shutdown=true;hsqldb.default_table_type=c
I look forward to trying the example you shared. Thank you
Here the DB modified for this thread.
Select a record and value in the id column is persisted in PLOT’s only field.
Rem: if msg does not show up, place cursor into field ID and leave it – TAB or Enter.
CopyingTB.odb (15.1 KB)
Thanks for trying again. I am not able to open it. I wonder what version Libre office it was created in? Here is what Chat GPT said:
" You’re running a newer LibreOffice version (7.6+) where embedded HSQLDB support was removed. That’s why “HSQLDB Embedded” doesn’t appear in the connection list anymore. LibreOffice 7.6+ only shows external connections (JDBC, Firebird, MySQL, etc.).
Your .odb file was made with an older version (6.x or earlier) that used embedded HSQLDB. That’s why you get:"
When I try to open CTBTT.odb and CopyingTB.odb I get these errors:
The connection to the data source “CTBTT” could not be established.
SQL Status: HY000
The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:embedded:firebird’. at ./connectivity/source/commontools/dbexception.cxx:413
A connection for the following URL was requested “sdbc:embedded:firebird”.
Can you let me know what version Libre office this was made on?
Jonathan Francoeur a.k.a. Kernel Hazelnut
Everything Edible Landscaping H.Q.
48.381980, -123.707835
250 642 0267
6411 Sooke Rd., Sooke, B.C., Canada, V9Z 0A8
That’s obviously nonsense, generated by an AI without the ability to write something simple like “I have no clue.”
.
The abillity to work with HSQLDB was not removed. There are versions - especially on linux - wich are installed without java etc.
.
There may be problems to use embedded and external HSQLDB at the same time/in the same configuration. Would need to read/google the details on this…
ChatGPT says a lot of things. A lot of them also aren’t true. LLMs do not have a concept of “truth” as their entire function is to recomposite the words from their training data into decent-sounding word salad. Whether it was correct this time or not, I suggest you do not rely on ChatGPT or any other LLM for information, it will eventually mislead you.
I did:
sudo apt-get install libreoffice-sdbc-hsqldb
and it worked allowing me to open the tables once, but only once. After closing and reopening now I’m getting
The connection could not be established. The database was created by a newer version of LibreOffice. at ./connectivity/source/commontools/dbexception.cxx:41
and once again I can’t open the tables or the form but I was able to copy the macro
Option Explicit
Sub PersistID(Evt As Object)
REM DECLARE THE VARIABLES:
Dim F As Object, ID As Object, Con As Object, Stmt As Object
Dim sSQL As String, plot_id As String
Dim resultado As Integer
REM SET REFERENCES:
ID = Evt.Source.Model
F = Evt.Source.Model.Parent.Parent
Con = F.ActiveConnection()
Stmt = Con.createStatement()
REM BUILD SQL UPDATE AND PERSIST Plot #:
plot_id = CStr(ID.CurrentValue) ' plot id to persist
sSQL = "UPDATE ""PLOT"" SET ""ID"" = '" & plot_id & "'"
resultado = Stmt.executeUpdate(sSQL)
print resultado & " record(s) updated to " & plot_id
End Sub
You should first check in another machine with an independent LO’s install.
If “plot_id” is numeric (INTEGER) no need for
CStr(ID.CurrentValue) and ’
Dim plot_id As Long ' or Integer
[...]
sSQL = "[...] SET ""ID"" = " & plot_id
By the way: no more need for -get.
CopyingTB.odb is doing what I want by copying the id to the correct place. Thank you!
I’d like to understand it better so that I can modify it and use it for my application. Some questions to help me understand:
When/how is it called to run? I was expecting to see it listed in the events tab tied to something like ‘when record change’.
I’ve added a table to the example that shows the plot_id on the plot table. I have to select the table and click refresh to see the change. Is there a way to automate this?
Event is ID - On losing focus
But I think better is Mouse button released (to click inside control).
Control = NOME in enclosed new dummy Firebird DB without plot_id control, as per your design.
It could be. Must check what suits your setup better trying. And what Events are disponible of course.
And you can add Event (no need to just 1 Event). So you can add another Event if you want.
I think better having it, but no need. At first I didn’t grasp you don’t have it in the form.
Check enclosed new dummy DB, Form TESTE.
What change? Routine saves selected plot_id (be it present in the Form or not) without need to have changed data in the selected row.
If you have Form with Sub Forms linked together, when you change record (row), linked Form(s) row(s) change. But of course we must check your specific setup and needs.
And yes: basic way to automate is
TheForm.reload
But first we must check your setup (forms design) and how you do want them working together.
Check this new dummy DB (Firebird).
LO 25.8.2.2
OS: Windows 11 X86_64 (build 26200)
PersistID.odb (20.0 KB)
I am able to open CopyingTB.odb after having done sudo apt-get install libreoffice-sdbc-hsqldb
I am not able to open CTBTT.odb or PersistID.odb because I get the error
“The connection to the external data source could not be established. No SDBC driver was found for the URL ‘sdbc:embedded:firebird’. at ./connectivity/source/commontools/dbexception.cxx:413”
when I try to open tables of forms. Looking at CopyingTB.odb where/when is the macro engaged? I looked in the events of the control and form and did not see anything.
TableControl > ID > Column > Events (of the column).
But please open and check these 2 new dummy DBs I sent, in another machine with another LO’s install. I included routine for your layout without the plot_id control, and Event After update.
Enclosed example persists id both on
- clicking NOME
or - After updating GRANDEZA
Without id showing up in the TableControl.
PersistID_HSQLDC.odb (13.4 KB)