Hi,
the LibreOffice Base program I am writing will be used on a shared server with multiple users. It is important therefore that no data specific to any one user gets stored in the permanent tables in the database. That said, I need to understand how to pass data from one query to another and to various macros, all within the same form, all relative to a specific user’s context.
For the implementation so far, just to get it working, I have setup a specific permanent table (tbl_SrchValues) that I read from the queries and write from the macros. This is okay for a stand-alone solution, but I now need something like a global variable that can be passed around.
I have read that I could implement Temporary Tables to share this data.
In the attached database, the main table is tbl_ASME, which contains data about collections of data called “volumes”, identified by VOL in the table. This table is read-only. (In the real table I have about 1000 volumes). The other fields contain data about the specific volume, such as whether it has been photographed (FOTO), what kind of data (TIPO) is contained (baptisms, weddings, deaths etc), which church (CHIESA) was involved, date span etc.
A number of concatenated queries are run on tbl_ASME, and the user can input values for these queries. For the logic of the queries I need to pass information between the queries and between a number of macros. This is accomplished in this example by using table tbl_SrchValues, which is written and read and keeps track of where we are.
You can ignore the other tables e.g. tbl_1, tbl_26 etc, as they are used in the second part of the program to visualise photographed data within an identified volume. You can also ignore tables tbl_Tipo and tbl_Chiesa.
I now need to eliminate tbl_SrchValues and find some other mechanism to share the data.
(Additional information, added 11 Jan 2019)
Hi, I have come back to this problem again. I have tried to use the temporary tables handled by Firebird. This would have the advantage of letting me keep all the present table queries and updates, I just have to substitute my tables with temporary ones. This is easier said than done!
I have been unable in fact to get them to work, even though I have managed to create a temporary table, but, despite defining its structure in the SQL CREATE request, when it is created there is no structure, the defined fields do not exist.
The Firefird description of how these tables work is a bit cryptic to me, they also talk about “commit”, which I believe only happens when the .odb files are closed.
Anyway, can you help with some simple SQL code that I can use in a macro that will let me Create a temporary structure, write and read some data into it, and then get rid of it when the user finishes? This is the code I am presently using, but although the table is created, its structure is not.
Sub create_temporary_table
Dim oDatasource As Object
Dim oConnection as Object
Dim oSQL_Statement As Object
Dim strSQL As String
oDatasource = thisDatabaseDocument.CurrentController
IF NOT (oDatasource.isConnected()) THEN oDatasource.connect()
oConnection = oDatasource.ActiveConnection()
oSQL_Statement = oConnection.createStatement()
strSQL = "CREATE GLOBAL TEMPORARY TABLE ""tbl_test"" "
strSQL = strSQL & "(""ID"" INTEGER, ""Field"" INTEGER)"
oSQL_Statement.execute(strSQL)
End sub
