Syntax error for Drop Table

General
1

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
  theTables.AddRow(data.Column("name"))
  data.MoveToNextRow
wend
data.Close

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

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

1 Like
2

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
3

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?

4

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

5

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.

2 Likes
6

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.

7

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

8

I see …

9

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