Help with "no such table" error with SQLite

Discussion in 'iOS Programming' started by tutiplain, Apr 23, 2011.

  1. tutiplain, Apr 23, 2011
    Last edited: Apr 24, 2011

    macrumors member

    Joined:
    Feb 4, 2011
    #1
    Hi all,

    I am currently experimenting with SQLite on the iOS, but something seems wrong, I can't get to my table, even though I know it exists. The .sqlite file was created using SQLite manager for firefox, then imported into the XCode project as a resource (is this the appropiate way to do this?). Then I inserted one record into it using the "sqlite3" Terminal command. However, when I try to access that record through the Sqlite APIs, I get a "no such table" error!

    Here's the code:

    Code:
    -(IBAction) howManyDiscounts
    {
        sqlite3 *con;
        sqlite3_stmt *stmt;
        if (sqlite3_open("Descuentos.sqlite",&con) == SQLITE_OK)
        {
            const char *q = "select * from Descuento;";
            if( sqlite3_prepare_v2(con, q, -1, &stmt, NULL) == SQLITE_OK)
            {
                sqlite3_step(stmt);
                lblCount.text = [NSString stringWithFormat:@"%s",sqlite3_column_text(stmt,1)];
            }
            else
            {
                const char *err = sqlite3_errmsg(con);
                NSString *errMsg = [NSString stringWithFormat:@"%s",err];
                NSLog(errMsg);
            }
        }
        sqlite3_finalize(stmt);
        sqlite3_close(con);
        
    }
    
    
    Execution always takes goes to the else and assigns the string "no such table: Descuento" to errMsg. Does anyone have an idea what I might be doing wrong?
     
  2. macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #2
    You need to use the full path to the file in sqlite3_open.
     
  3. KnightWRX, Apr 24, 2011
    Last edited: Apr 24, 2011

    macrumors Pentium

    KnightWRX

    Joined:
    Jan 28, 2009
    Location:
    Quebec, Canada
    #3
    Somehow, I doubt that is the problem. If it was, he wouldn't be getting to his if/else statement and his SELECT wouldn't even get executed as sqlite3_open would return SQLITE_CANTOPEN instead of SQLITE_OK on the sqlite3_open() call. As long as the CWD contains the file, no need for the full path.

    OP :

    Run sqlite3 from the command-line and give us the output of .schema or .tables so that we can help in debugging your stuff :

    Code:
    
    sqlite> CREATE TABLE test (
       ...> NAME varchar(20)
       ...> );
    sqlite> INSERT INTO test VALUES ('Roger');
    sqlite> SELECT * FROM test;
    Roger
    sqlite> .schema
    CREATE TABLE test (
    NAME varchar(20)
    );
    sqlite> .tables
    test
    
    EDIT : Another point, are you sure you want this code outside the first if block ?

    Code:
    sqlite3_finalize(stmt);
        sqlite3_close(con);
    Looks to me you shouldn't finalize a stmt that didn't execute and you shouldn't close a connection if it was never opened.
     
  4. macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #4
    I think it is.

    sqlite3_open() will create the database file if it doesn't exist. If running this app on the Sim then it will create the file somewhere, either at the root of his hd or in his home directory. It's a new empty database, hence the "no such table" error.

    How could the code he shows possibly open a database that is inside the app's bundle?
     
  5. macrumors Pentium

    KnightWRX

    Joined:
    Jan 28, 2009
    Location:
    Quebec, Canada
    #5
    Digging deeper in the documentation, indeed, sqlite3_open() seems to create an empty database if the file doesn't exist :

    http://www.sqlite.org/c3ref/open.html
    Wow is that dumb. :eek: Nevermind then, OP, you need to look into [NSBundle mainBundle] to get the full path to your file.
     
  6. macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #6
    You can use sqlite3_open_v2() if you don't want it to create a new empty database.
     
  7. thread starter macrumors member

    Joined:
    Feb 4, 2011
    #7
    You guys are right. I have a copy of my SQLite file on my HD root folder!

    The tutorial I was reading suggested to do it this way, to free up any resources even if the connection was never opened (though I don't remember it mentioning anything about the _finalize() function :).

    And yes, there was also something in there about [NSBundle mainBundle], but I chose to not include it. Guess I need it after all. The reason is because I've used the sqlite APIs on Windows systems previously, and specifying just the file name causes the database to be created where the executable resides (which is fine for me on WIndows), but I guess it's a bit different with iPod apps :)

    Thanks for the advice everyone, I will post back after I've looked into the NSBundle approach.
     
  8. thread starter macrumors member

    Joined:
    Feb 4, 2011
    #8
    Hi everyone,

    I did the following:

    Code:
    NSLog([[NSBundle mainBundle] resourcePath]);
    
    and the following appeared on my debugger:
    Code:
    MyApp[420:207] /Users/ocampos/Library/Application Support/iPhone Simulator/4.3/Applications/8F48EC3E-6DCA-42FD-92FC-A17AEF4C5069/MyApp.app
    
    According to the Apple docs, the resourcePath method returns the path where .app file is located. I looked up the folder, and, indeed, the .app file was there, but not the .sqlite file. Does it not get "packed" with the rest of the resources in my app?

    So far, it seems that the best approach is to have sqlite3_open() create the .sqlite file, in the Documents directory available in the folder. Perhaps I can get to this folder with the NSDocumentDirectory variable? (I've only read that this variable exists, but I have no idea what it does, I'm just guessing this is it). I'll try it out and see what happens.
     
  9. macrumors 68030

    PhoneyDeveloper

    Joined:
    Sep 2, 2008
    #9
    Code:
    NSString* path = [[NSBundle mainBundle] pathForResource:@"filename" ofType:@"sqlite"];
     
  10. thread starter macrumors member

    Joined:
    Feb 4, 2011
    #10
    Hi again,

    Thanks Phoney, this is exactly what I was missing. Thanks to everyone else who replied, too. Your insights have proven invaluable, as always. I have learned a lot from this post. The database file does get "packed" into the path described in [[NSBundle mainBundle] resourcePath], just not where I expected. It is inside the .app file (which, in reality, is another folder!). I also learned where the iPhone Simulator stores in temp app data, as well as a bit about how an app is structured. Thanks a again!
     

Share This Page