1 2 Previous Next 16 Replies Latest reply: Nov 13, 2008 7:36 PM by coolguy1981
kaiuskal Level 1 Level 1 (5 points)
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!
  • 1. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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 (sqlite3preparev2(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 sqlite3preparev2 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?
  • 2. Re: SQLite database on iPhone
    EagerEyes Level 1 Level 1 (65 points)
    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.
  • 3. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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?
  • 4. Re: SQLite database on iPhone
    EagerEyes Level 1 Level 1 (65 points)
    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.
  • 5. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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.
  • 6. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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.
  • 7. Re: SQLite database on iPhone
    EagerEyes Level 1 Level 1 (65 points)
    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!
  • 8. Re: SQLite database on iPhone
    gkstuart Level 2 Level 2 (225 points)
    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...
  • 9. Re: SQLite database on iPhone
    Steve Patt Level 1 Level 1 (30 points)
    Any recommendations on the best things to read to get up to speed on SQLite?
  • 10. Re: SQLite database on iPhone
    gkstuart Level 2 Level 2 (225 points)
    [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!
  • 11. Re: SQLite database on iPhone
    HeoU Level 1 Level 1 (5 points)
    It works fine on both for me. I'm actually on beta 5.

    HeoU
  • 12. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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?
  • 13. Re: SQLite database on iPhone
    kaiuskal Level 1 Level 1 (5 points)
    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?
  • 14. Re: SQLite database on iPhone
    jaekwon Level 1 Level 1 (5 points)
    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
1 2 Previous Next