Emptying an SQLite database

Getting Started
1

Hello,
how do you clear an SQLite database from all data?
I tried line by line with the following code:
" Sub Azzera()
'//•••••••••••••• AZZERA TUTTI I CAMPI •••••••••••••••
’
If Sommario.RiasList.SelectedRowIndex < 0 Then Return

Dim sql As String = “DELETE FROM Race WHERE ID = ?;”
Var id As Integer = Sommario.RiasList.SelectedRowIndex

Try
DB.ExecuteSQL(sql, id)
Catch err As DatabaseException
MessageDialog.Show("Error deleting the file from the database: " + err.Message)
End Try
End Sub
"
but I get an erro in line 5 (DB.ExecuteSQL(sql, id)). DB isn SQLite db declared in Globals and it is recognized in other methods.

I would like to deleted all rows in the SQLite database (Table named Race) so that I can have a pristine one in case I need

Thank you

2

DB.ExecuteSQL(„delete from Race;“) should do it.

3

Read more

https://sqlite.org/lang_delete.html

4

Also look at TRUNCATE.

5

SQLite does truncate automatically when you use delete without conditions.

1 Like
6

It will bypass triggers the way TRUNCATE does?

7

and the table being deleted has no triggers

it does truncate, if there are not triggers.

8

I tried all your seuggestions but I still get the same red X. I’m probably doing something wrong because if I use "Dim sql As String = “DELETE FROM Race WHERE ID = ?;” I get the screenshot I attach; if I use "Dim sql As String = “TRUNCATE FROM Race;” I get no errors but no deletion in the database.Screenshot of Xojo (06-05-2021, 09-01-30)|527x499

9

if you like to delete all, don’t use the ID=?

"Dim sql As String = “DELETE FROM Race”

10

I even tried it but I still get the same red X

11

Please read the message int he database exception.
Not just give up when you see red X.

12

I modified the line of the method to “DELETE FROM Race;”. I also tried “TRUNCATE FROM Race;" version, both follwed by “DB.ExecuteSQL(sql, id)”
I do not get any errors anymore but I don’t get an empty SQLite db either. All the rows stay exactly as they were

13

It might be helpful to know what the exception type is and what the message property is set to.

14

DELETE FROM race
doesn’t have a parameter (will delete all the records)
so there is no need to repeat the call DB.ExecuteSQL(sql, id)
but you need only
DB.ExecuteSQL(sql)

once

15

Does it need a COMMIT command?

16

Depends whether you have auto commit activated.

17

Thank you guys for your help. Thru your help and some experiments I solved my problem.
I have another question but I’ll open a new topic.

Thanks again

18

FYI, it’s TRUNCATE table_name, not TRUNCATE FROM....

19

Thank you

20

After a delete or a truncate, also make a VACUUM

sql= "VACUUM"
DB.ExecuteSQL(sql)

Or you will end with empty space, or free database pages making your file much more bigger that it needs to be

2 Likes