Need help deleting all rows in a sql database

Getting Started Newbie Questions
#1

Hi, I have been having a difficult time figuring out how to delete rows from a database. I’ve looked at other posts for an answer and cannot find one.

Here is a sample of code:

–delete all the table contents
local n = 1
for row in db:nrows(“SELECT * FROM test”) do
local tableDelete = [[DELETE FROM test WHERE id == row]]
db:exec(tableDelete)
end

This does not delete my rows.

Also, how do I delete a database file entirely, when it is no longer needed?

I want to use a database in sql for saving number of objects and position of those objects. The number of objects changes from game to game. Any help or advise would be greatly appreciated! I’ve looked at some other lua based sql tutorials but the code samples they give dont seem to work in Corona.
Anyway, Thanks for your help in advanced!!!

Bodee [import]uid: 15607 topic_id: 8196 reply_id: 308196[/import]

#2

I’m not at home to test this directly, but I think you need to insert “row” through concatenation rather than directly writing it like that. Refer to this thread:
http://developer.anscamobile.com/forum/2011/03/19/lua-and-sqlite#comment-28810

Also when I look it up online it looks like your syntax may be slightly incorrect. The example on this website only has one equals sign:
http://zetcode.com/databases/sqlitetutorial/datamanipulation/#delete

Also, how do I delete a database file entirely, when it is no longer needed?

The SQLite database is simply a file in the documents directory, so you can use Corona’s file handling commands to erase it:
http://developer.anscamobile.com/content/files

I’m not sure you can technically delete the file, but you can certainly flush it clean and then recreate all the tables from scratch in an empty file. [import]uid: 12108 topic_id: 8196 reply_id: 29345[/import]

#3

JHocking, thanks for the advice and direction. I think the flush is what I’m looking for. I’ll try it out tonight and let you know how it goes!
Thanks again, [import]uid: 15607 topic_id: 8196 reply_id: 29522[/import]