I understand the question to be seeking a way to sidestep the GUI, which is cumbersome for large-scale changes.
You can change the table by drafting a SQL script, which you can compose in a text editor. This might result in improvement in speed in creating the instruction. Here is the instruction to enter into
SQL where the table with the column is
Table1 and the text column is
ALTER TABLE "Table1"
ALTER COLUMN "text" INT;
For additional columns, just repeat that statement with correct information, customized to the particular field, separating the statements by semi-colons This worked for me with two statements, should work for more.
To make this even more efficient, here is another
SQL script that will automatically spit out a
CSV with the above statement for every
VARCHAR column in your
Table1 – you will need to change both instances of
Table1 to your table name:
SELECT CONCAT(CONCAT('ALTER TABLE "Table1" ALTER COLUMN "', "COLUMN_NAME"), '" INT;') "phrases"
INTO TEXT "sql_altertable_phrases"
WHERE "TABLE_NAME" = 'Table1'
AND "TYPE_NAME" = 'VARCHAR'
Linux system, the
CSV is outputted to
/home/user/Downloads/ The new table definition will be visible through the
GUI after you do
Refresh Tables. I would refresh immediately to avoid an ambiguous state for LO Base.
Warning: you must delete the reference to the CSV table in the list of tables when you are done and before closing LibreOffice. For a unknown reason, the presence of that table will prevent HSQLDB from loading anything on restart and the error seems irrecoverable. So delete the reference to the CSV table, as it does not really help.
This probably is the simplest and most efficient way. The statement will try to change the table definition and convert the data in the column. In testing, the statement failed when the data could not be converted, but I would be extra careful about that-- create a backup before executing and verify the output of the operation.
You also could
CREATE a new table with the desired definition and then
INSERT the data using the
SQL tool, (or
ALTER TABLE ADD COLUMN and
UPDATE) with or without use of the
CAST function. This is all documented in the HSQLDB v.1.8 manual.
EDIT: added shortcut for creating script with
EDIT2: Added further cleanup instruction.
(if this answered your question, please accept the answer by clicking the check mark () to the left)