SQLite

Question

Level 1

6 points

SQLite

I would like to store data from my app in an SQLite file. The table is simple, and what I am doing is not rocket science.

I've found a great little freeware app called Mike T's SQLite Database Maintenance application to both configure my tables, and generate and test my queries.

Question:
1) How do I have get access to the DB file on my a)simulator and b)iPhone to verify that all is working?
2) Does anybody know how the SDK will handle these files later on? That is, will I be able to transfer these to a desktop for analysis?

I'll take answers to point number 1 as a priority.

Thanks!

Posted on May 9, 2008 3:21 PM

Reply

Answer 1

May 9, 2008 4:09 PM in response to kaiuskal

Have you looked at the sample app in Dev Center:

http://developer.apple.com/iphone/library/samplecode/SQLiteBooks/index.html

It should provide you with all the code you need to figure out answers to both questions. (Make sure you look at the readme also.)

Craig

Reply

Answer 2

kaiuskal Author

Level 1

6 points

May 9, 2008 4:49 PM in response to Craig Patchett

Craig,

I guess that is my question. After reading README.TXT I am:

1) Unable to open the database in the demo (or my app) with Terminal.
(maybe I have NO idea WHAT path to use to open it...) That is, if either with Terminal, or even better, with either the FIREFOX app or the one I found, I can open this file and in real time see what is going on, my life will be made easier.

example from readme.txt: type the following on terminal

host:~ username$ sqlite3 my_database.sqlite

I ENTER:

host:~ username$ sqlite3 bookdb.sql (which is the DB in their demo)

I THEN TYPE:

INSERT INTO book(title, copyright, author) VALUES ('The Grinch', -20466864000, 'Dr Seuss');

And it returns: SQL error: no such table: book

So yes, I suspect it's a path problem, but I have NO IDEA what path to use...

2) With regards to moving or copying this file from the iPhone to my desktop or elsewhere, again I am at a loss.

Reply

Answer 3

May 9, 2008 5:19 PM in response to kaiuskal

There isn't a SQLite file already on your iPhone or in the simulator. You have to create one and put it in your project. The reason you got the "no table" error was because you didn't create a table:


CREATE TABLE book ('pk' INTEGER PRIMARY KEY, 'title' CHAR(32), 'copyright' REAL, 'author' CHAR(48));

Check the Readme.txt file in SQLiteBooks again for details on how to properly create and save the database.

Alternatively, if you're using Mike T's SQLite program, you can create the database and tables and initialize all your data with that. Then save it somewhere on your computer and then drag it into your project files in XCode. Once there, the iPhone and simulator will have access to it.

Reply

Answer 4

kaiuskal Author

Level 1

6 points

May 9, 2008 5:56 PM in response to gummyAvenger

Hi gA,

Thanks for your input.

I understand that I either have to create the file with code, or create it and seed it and place it in my project before runtime with other tools.

But perhaps I have not made myself clear. In my X-Code project, I either have bookdb.sql if it's Apple's demo, or my DBfile in the case of my code.

With Mike T's program, I can open those files, add records to them, remove rows, etc. with queries and all works fine - but BEFORE I run the programs.

Now, I start up the code on the Simulator. Inside my code, I ask it to add a record to a table, like the Dr. Seuss example. I use a query that I know works in Mike T's SQLite...

How do I now VIEW the db file with either Mike T's SQL or with Terminal to see whether the record was added? WHERE IS THIS FILE? The file for me at this time is like Schrodinger's cat in quantum mechanics. i.e. it's there (because I can access it in runtime) but it ain't there because after run time I can't find it with the changes I made to it!

Reply

Answer 5

May 9, 2008 7:20 PM in response to kaiuskal

Ah, I get what you're saying now. When your app gets sent to the simulator, it makes a copy of your SQLite file and uses that. Unfortunately, I haven't yet been able to find the copy to access it at the same time as the simulator. I doubt it would even be accessible at all, though. If anyone does happen to figure this out, I would also be interested in knowing.

Reply

Answer 6

kgelner

Level 4

1,205 points

May 9, 2008 8:12 PM in response to gummyAvenger

~/Library/Application Support/iPhone Simulator/User/Applications

Your application files are in a GUID named directory under there.

The books sample database should be queryable as well. I've used the command line, but also the tool SQLLite Database Browser:

http://sourceforge.net/projects/sqlitebrowser

Reply

Answer 7

May 10, 2008 3:24 AM in response to kaiuskal

Prior to SDK 5 every time you built your app you lost any documents that were already created by that app on the simulator or device (including sqlite databases). SDK 5 seems to preserve previous documents, which makes continuing testing a bit easier, but can lead to other problems.

The SQLiteBooks app is a goldmine of techniques and ideas that you should thoroughly investigate if you're doing any sqlite database work. For instance, that app copies in a blank database document from the application package, if one doesn't already exists. It also uses the idea of 'hydrating' objects from the database - only loading the full data when it's absolutely needed.

Check it out, it's a very useful learning tool.

Also, as another commented mentioned, you can access the live simulator documents directory, so if you type this in the terminal:

sqlite3 ~/Library/Application Support/iPhone Simulator/User/Applications/<your app GUID>/Documents/<yourdatabase>.sqlite

you will be able to interactively query the database as your app is running.

Reply

Answer 8

kaiuskal Author

Level 1

6 points

May 10, 2008 8:40 AM in response to Andrew Rennard

I'll try what Andrew says, but it sounds like it should work.

Can we keep this thread alive?

I think that as Andrew says, the SQLite techniques are a goldmine to learn from.

But even more important, we can learn from ourselves here, since documentation on this subject is scant at this point. And an iPhone app without good data management seems to me like a quick dead end.

I encourage others to post small tidbits of code in this thread for SQLite. The Apple demo is extensive and we can certainly learn how to weed through it and extract smaller more digestible portions of what we need.

Thanks Andrew!

Reply

Answer 9

Eploko

Level 1

0 points

May 10, 2008 10:28 AM in response to kaiuskal

Let me put 2 cents in,

There's an Objecive-C SQLite wrapper that really simplifies working w/ the SQLite API. I'm currently using it in my iPhone app. It's written by Gus Mueller--the creator of VoodooPad--so, IMHO, it's definitely worth checking out.

The wrapper can be found here:
http://gusmueller.com/blog/archives/2008/03/fmdbforiphone.html

Hope you find it as useful as I do.

Reply

Answer 10

May 10, 2008 9:04 PM in response to Eploko

I have been using FMDatabase by Gus also. It works extremely well on the iPhone so far.

Reply

Answer 11

May 12, 2008 3:04 AM in response to Eploko

Thanks - I will definitely check that out !

Reply

Answer 12

kaiuskal Author

Level 1

6 points

May 12, 2008 7:44 AM in response to Andrew Rennard

A wrapper, as the name implies, is extra baggage. I have been working with the demo during the weekend, and then introducing code from it into mine.

Pretty easy. So at this point, I recommend that you just dive into the real thing. I am constructing real time SQL sentences that write data every few seconds with a customizable timer, and it works seamlessly.

One thing. I did find that the best tool to work with is the SQLite Database Browser that was recommended in this thread. The one I used earlier (Mike's tool) would sometimes have trouble reading my DB after I wrote to it.

Reply

Answer 13

May 27, 2008 1:57 PM in response to kaiuskal

I'm having trouble importing (or technically copying/creating a database) in my iphone application. I've gone through the SQLiteBooks sample code and it is very useful; however, I can't seem to figure out how to successfully import a .sql file into it. Here is what I've done:

1) Through my terminal, I created a database
2) Executed a .dump on the table to create the .sql file
3) Used a similar function to the "CreateEdittableCopyofDatabaseIfNeeded" from the SQLBooks sample code.

Though my application now has a .sql file in it, whenever I try to access it through the terminal it says "SQL error: file is encrypted or is not a database". So I don't think it's formatted correctly. Here is what my .sql looks like:

BEGIN TRANSACTION;
CREATE TABLE user ('_id' INTEGER PRIMARY KEY, 'name' CHAR(255), 'number' CHAR (20));
INSERT INTO "user" VALUES(12,'John Doe','5555556666');
COMMIT;

Reply

Answer 14

May 27, 2008 2:09 PM in response to draketherake

You don't want the SQL file but the actual database file in your application package! It's unfortunate that they named the file something.sql in the example, if you look at it you will see that it's really the (binary) database file. Naming it something.db (or something.sqlite) makes a lot more sense.

Reply