[Solved] Truncate not working

Creating tables and queries

[Solved] Truncate not working

Postby assa » Thu Jan 11, 2018 7:26 am

I am using the truncate statement in query as follows:

Code: Select all   Expand viewCollapse view
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?
Last edited by assa on Fri Jan 12, 2018 7:05 am, edited 1 time in total.
OpenOffice 4.1.4 and Operating System Windows 8.1
assa
 
Posts: 13
Joined: Sun Dec 17, 2017 9:46 am

Re: truncate not working

Postby Sliderule » Thu Jan 11, 2018 7:44 am

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.
User avatar
Sliderule
Volunteer
 
Posts: 1153
Joined: Thu Nov 29, 2007 9:46 am

Re: truncate not working

Postby assa » Thu Jan 11, 2018 11:32 am

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

Re: Truncate not working

Postby chrisb » Thu Jan 11, 2018 3:25 pm

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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Truncate not working

Postby assa » Thu Jan 11, 2018 3:29 pm

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

Re: Truncate not working

Postby chrisb » Thu Jan 11, 2018 4:11 pm

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 >> https://forum.openoffice.org/en/forum/v ... 44#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.5 & LibreOffice 6.0.5.2 (x64) using HSQL 1.8.10 (Embedded) and HSQL 2.4.1 (Split) on Windows 10
chrisb
 
Posts: 175
Joined: Mon Jun 07, 2010 4:16 pm

Re: Truncate not working

Postby Sliderule » Thu Jan 11, 2018 4:39 pm

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

http://www.hsqldb.org/doc/1.8/guide/ch09.html#delete-section http://www.hsqldb.org/doc/1.8/guide/ch09.html#delete-section wrote:
DELETE

DELETE FROM table [WHERE Expression];

Removes rows in a table.

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.
User avatar
Sliderule
Volunteer
 
Posts: 1153
Joined: Thu Nov 29, 2007 9:46 am

Re: Truncate not working

Postby assa » Fri Jan 12, 2018 7:04 am

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


Return to Tables & Queries

Who is online

Users browsing this forum: No registered users and 4 guests