Database Problems

Getting Started Newbie Questions
#1

Having a great time with Corona and making good progress but having huge problems with sqlite database.

Each time I open an App I want to be able to reset my database, I have tried all the following.

  1. Drop a table and then re-create it. Using DROP and CREATE SQL commands.

  2. Delete the contents of the table and re-build using DELETE and INSERT.

The first method seems to drop the table but does not re-create it, the second does not seem to have any effect at all.

Any help would be very appreciated before I go totally mad!!!

Thanks [import]uid: 7830 topic_id: 7625 reply_id: 307625[/import]

#2

Have you tried flushing the database file with file commands before opening the database with sqlite?
http://developer.anscamobile.com/content/files

That’s how I would approach it because that’s simplest, but your approach of dropping and then creating tables should work. Are you sure you’ve written your create commands correctly? post your code
http://developer.anscamobile.com/content/data-storage [import]uid: 12108 topic_id: 7625 reply_id: 27079[/import]

#3

Thanks for the reply. The code is as follow…

require “sqlite3”
path = system.pathForFile(“cricket4.db”, system.DocumentsDirectory)
db = sqlite3.open( path )
db:exec[[
DROP table player;
CREATE TABLE if not exists match (id INTEGER PRIMARY KEY, home TEXT, away TEXT, date TEXT);
INSERT INTO match VALUES(1,‘Hurstpierpoint 1stX1’,‘Worth 1stX1’,‘20th May, 2011’);
CREATE TABLE if not exists player (player_status TEXT, bat_sixes NUMERIC, bat_fours NUMERIC, team TEXT, bowl_maidens NUMERIC, bowl_wickets NUMERIC, bowl_runs NUMERIC, bowl_overs NUMERIC, catches NUMERIC, bat_balls NUMERIC, bat_runs NUMERIC, match_id NUMERIC, player_id INTEGER PRIMARY KEY, name TEXT);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,1,1,‘Richard Hurley’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,1,2,‘Richard Cooke’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,1,3,‘Steve Crook’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,1,4,‘Oliver Morgan’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,5,‘Robert Jones’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,6,‘Warwick Mayler’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,7,‘Chris Eustace’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,8,‘Ben Cole’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,9,‘Mike Hutton’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,10,‘Chris Wroth’);
INSERT INTO player VALUES(‘DNB’,0,0,‘H’,0,0,0,0,0,0,0,0,11,‘Tim Firth’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,12,‘Henry Jones’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,13,‘Tom Pavey’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,14,‘Alex Vinall’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,15,‘Ben King’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,16,‘Ben Thomas’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,17,‘Ben Thomas’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,18,‘Charlie Silvey’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,19,‘Matthew Rickard’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,20,‘Hector Manly’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,21,‘James Taylor’);
INSERT INTO player VALUES(‘DNB’,0,0,‘A’,0,0,0,0,0,0,0,0,22,‘Greg Smart’)]]

If I then query the database I get an error that the player table does not exist.

Thanks for the help. [import]uid: 7830 topic_id: 7625 reply_id: 27086[/import]

#4

I advise you to get a SQLite tool that can load/edit/manage SQLite databases. On the Mac, I use SQLite Manager (available from the App Store and it’s great).

Using a tool like this allows you to view and alter the DB, i.e. you can create/alter tables, add data, remove data, write and test queries etc. You get decent error messages too. You can do all this much more easily than trying to do it within code in a Corona app.

Then, when your database is ready to be used you just drop the SQLite file into your development folder. In your Corona code you use a little piece of code that then copies the DB file into the Documents Directory.

This is the approach that I now use and it works really well. In fact, I was going to write a post about this for the forum, so perhaps I’ll get onto that when I get home from work! [import]uid: 26769 topic_id: 7625 reply_id: 27396[/import]