SQLite database on iPhone

Question

Level 1

6 points

SQLite database on iPhone

I done all my development for my SQL on the simulator, and I have my app writing and reading from my DB seamlessly.

In fact, very few lines of code go quite far. In my Apple computer I am able to find my database and verify that all is working properly (under library/application support/iphone simulator/user/applications/myapp/documents)

BUT...

I know have moved the identical code to the iphone, and my app is UNABLE to read my database for its default configuration.

My code to find the DB in question comes from the Apple demos:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sql"];

Questions:
1) When I "build" my app for the iphone, and not the simulator, and my default DB is part of my project, is it copied to the iphone?
2) How can I see where it goes?
3) Why is it that what works on my simulator, does not work on the iPhone?

Thanks!

Posted on May 28, 2008 10:46 AM

Reply

Answer 1

kaiuskal Author

Level 1

6 points

May 28, 2008 12:08 PM in response to kaiuskal

In fact the following code works on my MAC iphone simulator, but not on my iPhone:

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"MyDB.sql"];

if (sqlite3_open([path UTF8String], &database) == SQLITE_OK){

sqlite3_stmt *statement;
strcpy (gMyAlQuery,"SELECT * FROM SysDefaults WHERE PK = 1");
if (sqlite3 preparev2(database, gMyAlQuery, -1, &statement, NULL) == SQLITE_OK)
...

THE ABOVE "if" is the one failing on the iPhone, not the first "if" so that means that my code IS finding the DB on the iphone but failing on the sqlite3 preparev2 line on the iPhone.

Has anybody seen this as a bug - or am I getting away with something on the simulator that is not kosher on the iPhone?

Reply

Answer 2

May 28, 2008 12:11 PM in response to kaiuskal

Have you looked at the SQLiteBooks example? They copy the database to the Documents directory before opening it because the db in the bundle can't be written to. That might be necessary even for a simple select to get a lock on the file. The code in that example is very useful, and there are some good comments there as well.

Reply

Answer 3

kaiuskal Author

Level 1

6 points

May 28, 2008 12:50 PM in response to EagerEyes

Hi,

Thanks for your response. In fact, I have patterned my code around that example, and yes, I do use the createEditableCopyOfDatabaseIfNeeded code up front...

So this would not seem to be the problem. The line that compiles the query seems to be the problem while running on the iphone, while it works just fine on the simulator.

Has anybody had problems with the compiling of the query statement while running on the iphone?

Reply

Answer 4

May 28, 2008 12:58 PM in response to kaiuskal

I'm sorry, I didn't read your posting properly. The only thing that comes to mind right now is a problem with the memory you use for the statement. Maybe you could try a const char *myQuery = "SELECT ..."; to be sure that the memory isn't the problem. Or perhaps something overwrites your database pointer before you get to the prepare call.

Reply

Answer 5

kaiuskal Author

Level 1

6 points

May 28, 2008 3:06 PM in response to EagerEyes

Tried your solution, and while it works with the simulator, it again does not work on the iPhone...

Does anybody have other ideas? Are others using SQLite for the iPhone?

In the meantime, I'm going to upgrade and will you posted.

Reply

Answer 6

kaiuskal Author

Level 1

6 points

May 28, 2008 5:14 PM in response to kaiuskal

The problem has been solved with NO code changes on iPhone version 6 and SDK 6.

I just recompiled everything and - bingo!

Which leads me to the next question... Who is using SQLite around here if nobody had brought this issue up?

For such a powerful feature in the SDK I am surprised to see how few of you are using it for setting up default configurations, saving data, etc.

Reply

Answer 7

May 28, 2008 5:27 PM in response to kaiuskal

I'm using it and it has worked fine on both the simulator and the device so far. Maybe there was just some weird bug that interfered with your code? Glad it works now!

Reply

Answer 8

gkstuart

Level 2

225 points

May 28, 2008 9:08 PM in response to kaiuskal

SQLite works fine for me in the simulator. I have not been admitted to the developer program yet so I have no way of doing device testing with FW 2.0 yet... and I suspect there may be a good number of people in this situation. Just my hunch...

Reply

Answer 9

May 28, 2008 10:20 PM in response to kaiuskal

Any recommendations on the best things to read to get up to speed on SQLite?

Reply

Answer 10

gkstuart

Level 2

225 points

May 28, 2008 10:29 PM in response to Steve Patt

[http://www.sqlite.org>

also, there is a SQLite example in the SDK (SQLBooks I think? something like that) which is quite good you should take a look at.

Hope this helps!

Reply

Answer 11

HeoU

Level 1

5 points

May 29, 2008 12:08 AM in response to kaiuskal

It works fine on both for me. I'm actually on beta 5.

HeoU

Reply

Answer 12

kaiuskal Author

Level 1

6 points

May 29, 2008 4:12 PM in response to EagerEyes

Is anybody doing a check on how big your database is vs how much memory your iphone user has, and whether you are about to overflow?

If so, how are you doing this?

Also, another useful thing to do is to know when one can "clean" your db and start from scratch...

Comments? Suggestions?

Reply

Answer 13

kaiuskal Author

Level 1

6 points

May 29, 2008 4:14 PM in response to HeoU

Is anybody doing a check on how big your database is vs how much memory your iphone user has, and whether you are about to overflow?

If so, how are you doing this?

Also, another useful thing to do is to know when one can "clean" your db and start from scratch...

Comments? Suggestions?

Reply

Answer 14

jaekwon

Level 1

5 points

May 29, 2008 5:15 PM in response to kaiuskal

I found that (at least for beta 5) there were in fact some differences in SQLite3 behavior.
So I had code that worked in the simulator but not on the IPhone.

I don't remember what the issue was, it could be one of the following:
- check your bind variable name, and make sure the prefix is right and consistent ('$', '%' etc)
- some ROLLBACK statements can throw an exception
- i think i figured out the problem by getting the specific error message. use sqlite3_errmsg like this: [NSString stringWithCString:sqlite3_errmsg(self->database)]
- make sure the database schema in the iphone documents directory is consistent with your template .sql file.

- Jae

Reply

Answer 15

Aatif

Level 1

40 points

Jul 7, 2008 12:31 AM in response to EagerEyes

@EagerEyes

I am having the same problem with the sqlite3preparev2 method, it is not returning SQLITE_OK.
You said that DB is copied into some writable location, what is this in the case of simulator? I have bundled the DB into the project.

Reply