Syntax error for Drop Table

I want to delete all tables in my database. But the code below gives me an exception for the Drop Table SQL with an error “near “?”: syntax error”. What am I doing wrong?

dim data as RowSet
  dim theSQL as String = "SELECT name FROM sqlite_master WHERE type ='table' AND name NOT LIKE 'sqlite_%';"
  data = PlanDataDB.SelectSQL(theSQL)

dim theTables(-1) as String
While not data.AfterLastRow

for currentTable as integer = 0 to theTables.LastRowIndex
    PlanDataDB.ExecuteSQL("DROP TABLE ?", theTables(currentTable))
  catch err as DatabaseException
    Return PlanDataErrors.kErrorDeleteTables + " " + kError + Err.Message
  end try

Xojo 2019r3 on High Sierra. I have omitted most of the error handling.

1 Like

you can’t do tablenames as a parameter, you have to do something like this:

PlanDataDB.ExecuteSQL("DROP TABLE "+theTables(currentTable))

This seems to be not supported by SQLite (proably other db types as well) in the first place.

1 Like

Would it not be easier and faster to just recreate your database, or what is the reason that you want to keep it and only delete the tables, views?

In any case, as there appears to be no user input involved, then you can just do as @DerkJ mentions as there is no real driver for a prepared statement

A bit of Googling showed me that such a prepared statement isn’t supported.

@Jeannot_Muller : the database is for preferences. So deleting the data should be invisible for the user. It’s only for when bad things happen, anyways.

As we say in German: kaum macht man es richtig und schon funktioniert es.


Note that when using such string inside the actual query, watchout for SQL Injection. If a user can enter the value somewhere they can mess up everything in the db if care is not taken.

Have you tried?
PlanDataDB.ExecuteSQL("DROP TABLE IF EXISTS ?", theTables(currentTable))
Maybe this works.

I see …

This topic was automatically closed 182 days after the last reply. New replies are no longer allowed.