How do I DROP TABLE ?

Databases
1

My first attempt to drop a table was:

db.SQLExecute("DROP TABLE IF EXIST Creator")

And this does not works at all. I resume working on that earlier today and added an error block (If …/… End If below) even if the documentation says this can’t return an error.

[code] // [2016-06-26] Clears the previous tables
db.SQLExecute(“DROP TABLE IF EXIST Creator”) // I also tried: db.SQLExecute(“DROP TABLE IF EXIST TABLE=Creator”) // [same error !]
If db.Error Then
MsgBox “Save the data into a SQLite file” + EndOfLine + EndOfLine +_
“An error occured while trying to delete a previous Table.” + EndOfLine +_
"Error " + Str(db.ErrorCode) + " " + db.ErrorMessage + “.”

db.Rollback
Return

Else
db.Commit
End If[/code]

And, as surprising as it could be, I get the error:

Error 1 near "Exists"; syntax error

DROP TABLE syntax. What means the dot* in the syntax artwork ?

I’ve made some searches on the internet without success.

Nota: there is no issued error when I do not have the If …/… End If block, one arise, but far away from here some lines below when I want to create a TABLE using the same name.

PS: the test line db.SQLExecute("Hi Blondie !") returns the same “Error 1: Syntax Error.” :wink:

  • This dot is meaningful, I saw a coma in another syntaw artwork. I tried to place one (" . ") following the syntax artwork (after EXISTS and before the TABLE name), but the syntax error is still here.
2

you forget the “S” at the end of “EXIST” ?

3

Thank you for your answer. And, yes, you are right Jean-Yves !

Unfortunately, the TABLE is not deleted, I get the TABLE exists error later (some lines later).

The table appears in sqlite_master.

PS: In fact, I have missed the ending S in four lines (located in the same group of lines).

4

Things are getting better: I get 3 TABLES deleted, 1 to go !

5

Last table dropped (deleted): my fault too: wrong table name and no reported error.

This leads me to two other questions:

a. Why no error is reported when the TABLE does not exists (worst: when the SQLExecute string is not related to SQL ? (SQLite here).

b. The file contents still holds the data that was here before the DROP TABLE. Nothing is reported when asked, bus like deleting file(s) in a hard disk, the previous data are still there.

I will investigate a little bit in case I am in charge of b.

6

VACUUM is the PRAGMA to use…

https://sqlite.org/lang_vacuum.html

7

because you execute “drop table if exists” … if it does not exist (your case) it does not delete and does … nothing.
use “drop table” only and you will get an error if the table does not exist.

8

Thanks.