[Solved] TRUNCATE TABLE statement not recognized

Creating tables and queries
Post Reply
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

[Solved] TRUNCATE TABLE statement not recognized

Post by assa »

I am using the truncate statement in query as follows:

Code: Select all

TRUNCATE table "tblperson";
When I run this query I get the error as:
1: Unexpected token: TRUNCATE in statement [TRUNCATE]

What does this mean?
Does open office 4.1.4 not support truncate?

 Edit: Changed subject, was Truncate not working 
Make your post understandable by others 
-- MrProgrammer, forum moderator 
Last edited by MrProgrammer on Thu Aug 18, 2022 9:55 pm, edited 2 times in total.
Reason: Updated subject
OpenOffice 4.1.4 and Operating System Windows 8.1
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: truncate not working

Post by Sliderule »

The command should be entered from the menu: Tools -> SQL...

Explanation: The command is NOT a Query ( read a database and return a result set ) but rather a DML - Data Manipulation Language - statement. Therefore, it must be entered from the menu as I noted above.

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.
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: truncate not working

Post by assa »

I am entering this command in Tools->SQL only. It is showing me the same error.
OpenOffice 4.1.4 and Operating System Windows 8.1
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Truncate not working

Post by chrisb »

assa,
both 'TRUNCATE TABLE' & 'DATE_ADD' are only available in HSQLDB 2x.
it would appear that you are using the default embedded database which uses HSQLDB 1.8.0.10.
if your signature is accurate & you are not connected to an external data source then look in the bottom left hand corner of your Base window, does it say 'Embedded database'?
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: Truncate not working

Post by assa »

Yes it does say embedded database.
Does this mean I cannot use truncate and date_add?

Are there any other alternatives of doing this?
OpenOffice 4.1.4 and Operating System Windows 8.1
chrisb
Posts: 298
Joined: Mon Jun 07, 2010 4:16 pm

Re: Truncate not working

Post by chrisb »

to delete rows from a table use: DELETE FROM "The Name Of Your table" [WHERE Expression];

Date, Time & Date/Time manipulation functions are pretty much non existent in HSQLDB 1.8.0.10.
the solution is to upgrade to a split database.
it's been a long time since i first split a database using the instructions contained in this excellent guide which was posted by DACM >> viewtopic.php?p=270844#p270844
things have moved on since 2013 so it may be advisable to search this forum for 'Split Database' before embarking on any particular course.
open office 4.1.14 & LibreOffice 6.4.4.2 x64 using HSQL 1.8.0.10 (Embedded) and HSQL 2.6.0 (Split) on Windows 10
User avatar
Sliderule
Volunteer
Posts: 1278
Joined: Thu Nov 29, 2007 9:46 am

Re: Truncate not working

Post by Sliderule »

assa wrote:Yes it does say embedded database.
Does this mean I cannot use truncate and date_add?

Are there any other alternatives of doing this?
Correct. You are attempting to use a command that does not exist in the database back-end version you are using ( HSQL 1.8.0.10 ), since you are using an Embedded Database.

Please see the documentation for HSQL 1.8 to confirm the commands and functions available.

http://www.hsqldb.org/doc/1.8/guide/ch0 ... te-section
I strongly recommend you update to a SPLIT DATABASE, and, use HSQL Version 2.4.0 ( as of the date I am writing this ) with a wealth of additional functions, including Date Arithmetic, Triggers, the ability to write your own functions, stability ( so your database will NOT crash and loose all your work ), and, being able to use other utilities, outside of OpenOffice / LibreOffice Base when using your database.

Sliderule

Thanks to add [Solved] in your 1st post Subject (edit button top right) if this issue has been resolved.
assa
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: Truncate not working

Post by assa »

Thank you for the help Everyone.
I know now I need to use functions from HSQL version 1.8.0.10
OpenOffice 4.1.4 and Operating System Windows 8.1
tomaszwinnicki
Posts: 1
Joined: Thu Aug 18, 2022 8:17 pm
Location: Krakow

Re: [Solved] Truncate not working

Post by tomaszwinnicki »

Here is a summary of some differences between truncate and delete sql commands:


delete command


a) Deletes all or some records from the table, you can limit the records to be deleted by using the WHERE clause
b) Does not free the space occupied by the data in the table (in the TABLESPACE - on the disk)
c) Does not reset the SEQUENCE value assigned to the table
d) DELETE works much slower than TRUNCATE
e) DELETE can be applied to tables and tables in a cluster (can be Oracle specific)
f) Oracle - For DELETE, you can use the GRANT command
g) The DELETE operation does not make unusable indexes usable again
h) DELETE requires a shared table lock
i) Triggers fire
j) DELETE can be used in the case of: database link
k) DELETE returns the number of records deleted
l) Transaction log - for each deleted record (deletes rows one at a time and records an entry in the transaction log for each deleted row) - slower execution than TRUNCATE. The table may still contain blank pages after executing the DELETE statement. DELETE needs to read records, check constraints, update block, update indexes, and generate redo / undo. All of this takes time, hence it takes time much longer than with TRUNCATE
m) DELETE uses more transaction space than the TRUNCATE statement
n) DELETE can be used with indexed views
o) It is a DML (Data Manipulation Language) command, therefore the following commands are used for this command: COMMIT and ROLLBACK
p) You can undo the operation of removing records by using the ROLLBACK command



truncate command


a) Deletes all records from the table, records cannot be limited to deletion. For Oracle, when the table is split per partition, individual partitions can be truncated (TRUNCATE) in isolation, making it possible to partially remove all data from the table
b) Frees up the space occupied by the data in the table (in the TABLESPACE - on disk). For Oracle - if you use the REUSE STORAGE clause, the data segments will not be rolled back, i.e. you will keep space from the deleted rows allocated to the table, which can be a bit more efficient if the table is to be reloaded with data. The high mark will be reset
c) Resets the SEQUENCE value assigned to the table to zero. However, the following options can be used: RESTART IDENTITY or CONTINUE IDENTITY
d) TRUNCATE works much faster than DELETE
e) TRUNCATE only affects tables or the entire cluster (may be Oracle specific)
f) Oracle - TRUNCATE cannot be granted (GRANT) without using DROP ANY TABLE
g) The TRUNCATE operation makes unusable indexes usable again
h) TRUNCATE requires an exclusive table lock, therefore, turning off exclusive table lock is a way to prevent TRUNCATE operation on the table
i) DML triggers do not fire after executing TRUNCATE (so be very careful in this case, you should not use TRUNCATE, if a delete trigger is defined in the table to perform an automatic table cleanup or a logon action after row deletion). On Oracle, DDL triggers are fired
j) Oracle - TRUNCATE cannot be used in the case of: database link
k) TRUNCATE does not return the number of records deleted
l) Transaction log - one log indicating page deallocation (removes data, releasing allocation of data pages used for storing table data and writes only page deallocations to the transaction log) - faster execution than DELETE. TRUNCATE only needs to adjust the pointer in the database to the table (High Water Mark) and the data is immediately deleted, therefore it uses less system resources and transaction logs
m) TRUNCATE takes up less transaction space than the DELETE statement
n) TRUNCATE cannot be used with indexed views
o) It is a DDL (Data Definition Language) command, therefore commands such as COMMIT and ROLLBACK do not apply to this command (the exceptions here are PostgreSQL and MSSQL, whose implementation of the TRUNCATE command allows the command to be used in a transaction)
p) You cannot undo the operation of deleting records, it occurs automatically and is irreversible (except for the above exceptions - provided, however, that the operation is included in the TRANSACTION block and the session is not closed). In case of Oracle - Includes two implicit commits, one before and one after the statement is executed. Therefore, the command cannot be withdrawn while a runtime error will result in commit anyway


more information:
https://rozwoj-oprogramowania.pl/en/blo ... elete.html
OpenOffice on Ubuntu 20.04
Post Reply