[Solved] Help with temporary table (HSQL2.5)
[Solved] Help with temporary table (HSQL2.5)
Hi,
A normal filtertable will not work in a multi user environment as there could be a conflict if two users access the table simultaneously. Reading the HSQL doco I am at a loss how to define a temporary table for each frontend as the table only exists for the current session.
So how would I turn this
CREATE TABLE "tblpfilter"("pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"did" INTEGER)
into a temporary table Global or Local ?
Thanks
A normal filtertable will not work in a multi user environment as there could be a conflict if two users access the table simultaneously. Reading the HSQL doco I am at a loss how to define a temporary table for each frontend as the table only exists for the current session.
So how would I turn this
CREATE TABLE "tblpfilter"("pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"did" INTEGER)
into a temporary table Global or Local ?
Thanks
Last edited by gkick on Sat Jun 06, 2020 6:46 am, edited 1 time in total.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with temporary table (HSQL2.5)
Per HSQL 2.5.0 documentation, I suspect you want DECLARE LOCAL TEMPORARY TABLE, that is, if you want the table ONLY available for the CURRENT SESSION. That is, any other user canSEE, or, READ, or, WRITE to it.gkick wrote:Reading the HSQL doco I am at a loss how to define a temporary table for each frontend as the table only exists for the current session.
Additionally, once the current session is ended, the table disappears.
Therefore, I suspect what you want ( assuming I correctly understand what you need ) is:gkick - http://www.hsqldb.org/doc/2.0/guide/sessions-chapt.html#snc_session_attr wrote:
Session Tables
With necessary access privileges, sessions can access all table, including GLOBAL TEMPORARY tables, that are defined in schemas. Although GLOBAL TEMPORARY tables have a single name and definition which applies to all sessions that use them, the contents of the tables are different for each session. The contents are cleared either at the end of each transaction or when the session is closed.
Session tables are different because their definition is visible only within the session that defines a table. The definition is dropped when the session is closed. Session tables do not belong to schemas.
<temporary table declaration> ::= DECLARE LOCAL TEMPORARY TABLE <table name> <table element list> [ ON COMMIT { PRESERVE | DELETE } ROWS ]
The syntax for declaration is based on the SQL Standard. A session table cannot have FOREIGN KEY constraints, but it can have PRIMARY KEY, UNIQUE or CHECK constraints. A session table definition cannot be modified by adding or removing columns, indexes, etc.
It is possible to refer to a session table using its name, which takes precedence over a schema table of the same name. To distinguish a session table from schema tables, the pseudo schema names, MODULE or SESSION can be used. An example is given below:
Code: Select all
DECLARE LOCAL TEMPORARY TABLE buffer (id INTEGER PRIMARY KEY, textdata VARCHAR(100))
Code: Select all
DECLARE LOCAL TEMPORARY TABLE "tblpfilter"("pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"did" INTEGER)
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Help with temporary table (HSQL2.5)
@SlideRule
Hi and thank you SlideRule.
I can run either code successfully from the SQL prompt, however the table is not visible and I can not refer to it by name by way of a query.
Thank you
G
Hi and thank you SlideRule.
I can run either code successfully from the SQL prompt, however the table is not visible and I can not refer to it by name by way of a query.
Thank you
G
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with temporary table (HSQL2.5)
Please re-read the last paragraph I posted above from the HSQL documentation.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Code: Select all
Select * From module."tblpfilter"
Select * From session."tblpfilter"
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Help with temporary table (HSQL2.5)
Perhaps you can try the following SQL to define your temporary table to be used ONLY by the current session, with the insertion of a few records.
Try the following Query to see the table contents, AND, the table can be used with any other table in your database:
I hope this helps, please be sure to let me / us know.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Code: Select all
DECLARE LOCAL TEMPORARY TABLE "tblpfilter"("pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"did" INTEGER) ON COMMIT PRESERVE ROWS;
INSERT INTO "tblpfilter" VALUES(default, 506);
INSERT INTO "tblpfilter" VALUES(default, 1234);
INSERT INTO "tblpfilter" VALUES(default, 1257),(default,2020);
Code: Select all
Select * From "tblpfilter"
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Help with temporary table (HSQL2.5)
Thank you got it, for some reason it only works if - Select * From MODULE."Filter" module or SESSION ARE in uppercase. Thats fine
Testing in my actual db ..
Now I can run the following script at startup to create the table and insert a single record
DECLARE LOCAL TEMPORARY TABLE "Filter"("id" INTEGER NOT NULL PRIMARY KEY,"Year" INTEGER,"Quarter" INTEGER,"Month" INTEGER,"Week" INTEGER,"Location" VARCHAR(100),"Department" VARCHAR(100),"From" DATE,"To" DATE) ON COMMIT PRESERVE ROWS;
INSERT INTO MODULE."Filter"("id","Year") VALUES 1,2019;
or INSERT INTO "Filter"("id","Year") VALUES 6,2019;
works beautifully !
interestingly I can not use INSERT INTO MODULE."Filter"("id","Year") VALUES (default,2019); as I end up with 5: integrity constraint violation: NOT NULL check constraint; SYS_CT_10254 table: "Filter" column: "id" whereas VALUES 1,2019 works - thats Ok because the table will always only have a single row.
Select * From "Filter " will open the query however it is not updateable, appears I can only run a Delete From and then do another INSERT ???
Next trying to change the filter listboxes of my form to use the temp table instead of the normal tblFilter I run into problems. See image below.
Normal setup is, the filter listboxes are all bound to the normal Filter Table and the button performing a refresh of the form is updating the table. The listbox properties however do not recognise MODULE."Filter" or just Fiter as Content. Not. sure but that could be an LO issue not recognising temporary tables.
One possible work around may perhaps be to capture all the listbox selections to variables and then do an INSERT INTO from the button, if a selection changes run a Delete From followed by another INSERT INTO as I do not know which listboxes will be NULL.
Can you think of another solution?
Appreciate your help and time
Thank you kindly
Testing in my actual db ..
Now I can run the following script at startup to create the table and insert a single record
DECLARE LOCAL TEMPORARY TABLE "Filter"("id" INTEGER NOT NULL PRIMARY KEY,"Year" INTEGER,"Quarter" INTEGER,"Month" INTEGER,"Week" INTEGER,"Location" VARCHAR(100),"Department" VARCHAR(100),"From" DATE,"To" DATE) ON COMMIT PRESERVE ROWS;
INSERT INTO MODULE."Filter"("id","Year") VALUES 1,2019;
or INSERT INTO "Filter"("id","Year") VALUES 6,2019;
works beautifully !
interestingly I can not use INSERT INTO MODULE."Filter"("id","Year") VALUES (default,2019); as I end up with 5: integrity constraint violation: NOT NULL check constraint; SYS_CT_10254 table: "Filter" column: "id" whereas VALUES 1,2019 works - thats Ok because the table will always only have a single row.
Select * From "Filter " will open the query however it is not updateable, appears I can only run a Delete From and then do another INSERT ???
Next trying to change the filter listboxes of my form to use the temp table instead of the normal tblFilter I run into problems. See image below.
Normal setup is, the filter listboxes are all bound to the normal Filter Table and the button performing a refresh of the form is updating the table. The listbox properties however do not recognise MODULE."Filter" or just Fiter as Content. Not. sure but that could be an LO issue not recognising temporary tables.
One possible work around may perhaps be to capture all the listbox selections to variables and then do an INSERT INTO from the button, if a selection changes run a Delete From followed by another INSERT INTO as I do not know which listboxes will be NULL.
Can you think of another solution?
Appreciate your help and time
Thank you kindly
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with temporary table (HSQL2.5)
This is the SQL you wrote above for the table creation, with line breaks to read it carefully:
Explanation:
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Code: Select all
DECLARE LOCAL TEMPORARY TABLE "Filter" (
"id" INTEGER NOT NULL PRIMARY KEY,
"Year" INTEGER,
"Quarter" INTEGER,
"Month" INTEGER,
"Week" INTEGER,
"Location" VARCHAR(100),
"Department" VARCHAR(100),
"From" DATE,
"To" DATE
) ON COMMIT PRESERVE ROWS;
- Your primary key will NOT accept default since, you have not defined a default value for it, as you did in your ORIGINAL statement you posted, that I mirrored in my test. You wrote: "pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY
- Do you have another table defined in your PUBLIC schema by the name of "Filter"? If so, yes the temporary table will need to have MODULE in front of it.
- FILTER IS a HSQL Keyword, Appendix A of user manual.
- Remember, the HSQL function: IDENTITY is defined in HSQL documentation as:
HSQL Documentation wrote:IDENTITY
IDENTITY ()
Returns the last IDENTITY value inserted into a row by the current session. The statement, CALL IDENTITY() can be
made after an INSERT statement that inserts a row into a table with an IDENTITY column. The CALL IDENTITY()
statement returns the last IDENTITY value that was inserted into a table by the current session. Each session manages
this function call separately and is not affected by inserts in other sessions. The statement can be executed as a direct
statement or a prepared statement. (HyperSQL) - Your table is updatable if you run the Query command NOT in direct mode. Try running the Query WITH the Base Parser, does that make a difference? You of course have to have the necessary Primary Key(s) to update a row.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: Help with temporary table (HSQL2.5)
Many, many thanks for your help with this!
Fixed the pk (oops) but running the query in Parser mode makes no difference, not updatable. Yet the programatic Inserts works.
Fixed the pk (oops) but running the query in Parser mode makes no difference, not updatable. Yet the programatic Inserts works.
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: Help with temporary table (HSQL2.5)
Just to confirm, with the newly ( even if temporary ) table you defined, ¿ does that user have right ( authority ) to UPDATE the table ?gkick wrote:Many, many thanks for your help with this!
Fixed the pk (oops) but running the query in Parser mode makes no difference, not updatable. Yet the programatic Inserts works.
As with any other table, in a multi-user environment, if you have created a ROLE for the user, ¿ is this new table a part of that role ?
Glad it is working for you.
Sliderule
Re: [Solved] Help with temporary table (HSQL2.5)
Until now I handled this problem for a couple of users with filter form records like this one:
This implies that I have to insert a set new filter records for every new user and another set of filter records for every new filter form:
Code: Select all
SELECT * FROM "FLT2" WHERE ID=3 AND CURRENT_USER="UNAME"
Code: Select all
UNAME | ID
--------------------
Villeroy | 0
Sliderule | 0
gkick | 0
Villeroy | 1
Sliderule | 1
gkick | 1
Villeroy | 2
Sliderule | 2
gkick | 2
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Help with temporary table (HSQL2.5)
@SlideRule
confirming user has admin rights. Assigning priviledges to a temporary table without or with MODULE or SESSION prefix will cause object not found error.
@Villeroy
Thanks for pointing out another work around, would you please be so kind to elaborate as my particular filtertable has just one single record at any given time. So if you have multiple rows how does a refresh button know which row to update ?
confirming user has admin rights. Assigning priviledges to a temporary table without or with MODULE or SESSION prefix will cause object not found error.
@Villeroy
Thanks for pointing out another work around, would you please be so kind to elaborate as my particular filtertable has just one single record at any given time. So if you have multiple rows how does a refresh button know which row to update ?
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] Help with temporary table (HSQL2.5)
The source of the filter table is always one distinct row:
A unique index on ID and UNAME enforces that the table stores only one row for each form and each user.
Code: Select all
SELECT * FROM "FLT2" WHERE ID=3 AND CURRENT_USER="UNAME"
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Help with temporary table (HSQL2.5)
You may use a SQL UPDATE statement with your TEMPORARY table.
You may use a SQL INSERT statement with your TEMPORARY table.
You may use a SQL DELETE statement with your TEMPORARY table.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
You may use a SQL INSERT statement with your TEMPORARY table.
You may use a SQL DELETE statement with your TEMPORARY table.
Sliderule
Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
Re: [Solved] Help with temporary table (HSQL2.5)
Quick test:
A user with update, delete, insert priviledge can declare the temp table but not update, delete nor insert. The same commands work fine for my SA user.
It seems to work with:
CREATE GLOBAL TEMPORARY TABLE "tmpTable"(...)
and
GRANT ALL ON "tmpTable" TO "User_Group"
The first command creates a "permanent temporary" table for all users. It is there from the beginning of a user session and it is blank. With the GRANT statement you allow the user to use the table.
A user with update, delete, insert priviledge can declare the temp table but not update, delete nor insert. The same commands work fine for my SA user.
It seems to work with:
CREATE GLOBAL TEMPORARY TABLE "tmpTable"(...)
and
GRANT ALL ON "tmpTable" TO "User_Group"
The first command creates a "permanent temporary" table for all users. It is there from the beginning of a user session and it is blank. With the GRANT statement you allow the user to use the table.
Please, edit this topic's initial post and add "[Solved]" to the subject line if your problem has been solved.
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Ubuntu 18.04 with LibreOffice 6.0, latest OpenOffice and LibreOffice
Re: [Solved] Help with temporary table (HSQL2.5)
Thank you !
Libre Office 6.4.6 on Windows 10 HSQL 2.51 backend
Re: [Solved] Help with temporary table (HSQL2.5)
gkick:
Using your original ( first ) post above, using the following SQL statements will allow for the creation of a TEMPORARY usable only by the user of the CURRENT SESSION.
At any time, you can use the Base Queries section to view the contents of the table. For example:
In conclusion, you can create the temporary table for the current session only, add rows, modify rows, and, delete rows.
I hope this helps, please be sure to let me / us know.
Sliderule
Using your original ( first ) post above, using the following SQL statements will allow for the creation of a TEMPORARY usable only by the user of the CURRENT SESSION.
Code: Select all
-- Create the initial table as a table only accessible by the current session
DECLARE LOCAL TEMPORARY TABLE "tblpfilter"(
"pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,
"did" INTEGER)
ON COMMIT PRESERVE ROWS;
-- Run Query below from Base Queries section ( Create Query in SQL View... ), OR, from Tools -> SQL...
Select * From "tblpfilter";
-- INSERT ( add ) four new records
INSERT INTO "tblpfilter" VALUES(default, 506);
INSERT INTO "tblpfilter" VALUES(default, 1234);
INSERT INTO "tblpfilter" VALUES(default, 1257),(default,2020);
-- UPDATE ( change ) the content of two of the records
UPDATE "tblpfilter" SET "did" = 12345 WHERE "pdid" = 1;
UPDATE "tblpfilter" SET "did" = 432 WHERE "pdid" = 0;
-- DELETE on of the rows
DELETE FROM "tblpfilter" WHERE "pdid" =2;
- Execute the command below after the Creation of the table and the INSERT
- Execute the command below after the UPDATE statements to confirm the table values has changed
- Execute the command below after the DELETE statement to confirm the row values has changed
Code: Select all
Select
*
From "tblpfilter"
I hope this helps, please be sure to let me / us know.
Sliderule