[Solved] Help with temporary table (HSQL2.5)

[gkick]

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

[Sliderule]

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.

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.

Additionally, once the current session is ended, the table disappears.

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))

Therefore, I suspect what you want ( assuming I correctly understand what you need ) is:

Code: Select all

DECLARE LOCAL TEMPORARY TABLE "tblpfilter"("pdid" SMALLINT GENERATED BY DEFAULT AS IDENTITY(START WITH 0) NOT NULL PRIMARY KEY,"did" INTEGER)

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.

[gkick]

@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

[Sliderule]

Please re-read the last paragraph I posted above from the HSQL documentation.

Code: Select all

Select * From module."tblpfilter"

Select * From session."tblpfilter"

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.

[Sliderule]

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.

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);

Try the following Query to see the table contents, AND, the table can be used with any other table in your database:

Code: Select all

Select * From "tblpfilter"

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.

[gkick]

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

[Sliderule]

This is the SQL you wrote above for the table creation, with line breaks to read it carefully:

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;

Explanation:

1. 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
2. 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.
3. FILTER IS a HSQL Keyword, Appendix A of user manual.
4. Remember, the HSQL function: IDENTITY is defined in HSQL documentation as:

   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)

5. 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.

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.

[gkick]

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.

[Sliderule]

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.

Just to confirm, with the newly ( even if temporary ) table you defined, ¿ does that user have right ( authority ) to UPDATE the table ?

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

[Villeroy]

Until now I handled this problem for a couple of users with filter form records like this one:

Code: Select all

SELECT * FROM "FLT2" WHERE ID=3 AND CURRENT_USER="UNAME"

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

UNAME     |  ID
--------------------
Villeroy  |  0
Sliderule |  0
 gkick     |  0
Villeroy  |  1
Sliderule |  1
 gkick     |  1
Villeroy  |  2
Sliderule |  2
 gkick     |  2

[gkick]

@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 ?

[Villeroy]

The source of the filter table is always one distinct row:

Code: Select all

SELECT * FROM "FLT2" WHERE ID=3 AND CURRENT_USER="UNAME"

A unique index on ID and UNAME enforces that the table stores only one row for each form and each user.

[Sliderule]

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.

[Villeroy]

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.

[gkick]

Thank you !

[Sliderule]

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.

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;

At any time, you can use the Base Queries section to view the contents of the table. For example:

1. Execute the command below after the Creation of the table and the INSERT
2. Execute the command below after the UPDATE statements to confirm the table values has changed
3. Execute the command below after the DELETE statement to confirm the row values has changed

Code: Select all

Select 
   *
From "tblpfilter"

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