1

I am running a SQLite database in memory and I am attempting to drop a table with the following command. 

DROP TABLE 'testing' ;

But when I execute the SQL statement, I get this error 

SQL logic error or missing database

Before I run the "Drop Table" query I check to make sure that the table exists in the database with this query. So I am pretty sure that the table exists and I have a connection to the database.

SELECT count(*) FROM sqlite_master WHERE type='table' and name='testing';

This database is loaded in to memory from a file database and after I attempt to drop this table the database is saved from memory to the file system. I can then use a third party SQLite utility to view the SQLite file and check to see if the "testing" exists, it does. Using the same 3rd party SQLite utility I am able to run the "Drop TABLE" SQL statement with out error.

I am able to create/update tables without any problems.

My questions:

  • Is there a difference between a memory database and a file database in SQLite when dropping a table?
  • Is there a way to disable the ability to drop a table in SQLite that I may have accentually turned on somehow?

Edit: It appears to have something to do with a locked table. Still investigating.

Steven Smethurst
  • 4,495
  • 15
  • 55
  • 92

2 Answers2

2

You should not have quotes in your DROP TABLE command. Use this instead:

DROP TABLE testing
Paul Lefebvre
  • 6,253
  • 3
  • 28
  • 36
  • Oops, there are not quotes in the real version. I added them to the question to make things clear but I guess it just made things more complicated. Edited the question and removed the quotes. – Steven Smethurst Mar 30 '11 at 18:58
1

I had the same problem when using Sqlite with the xerial jbdc driver in the version 3.7.2. and JRE7 I first listed all the tables with the select command as follows:

SELECT name FROM sqlite_master WHERE type='table'

And then tried to delete a table like this:

DROP TABLE IF EXISTS TableName

I was working on a database stored on the file system and so it seems not to effect the outcome. I used the IF EXISTS command to avoid listing all the table from the master table first, but I needed the complete table list anyway.

For me the solution was simply to change the order of the SELECT and DROP.

nickmastre
  • 26
  • 1