iOS Help with "no such table" error with SQLite

tutiplain · Apr 23, 2011

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?

PhoneyDeveloper · Apr 24, 2011

You need to use the full path to the file in sqlite3_open.

KnightWRX · Apr 24, 2011

PhoneyDeveloper said:
You need to use the full path to the file in sqlite3_open.

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.

PhoneyDeveloper · Apr 24, 2011

Somehow, I doubt that is the problem.

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?

KnightWRX · Apr 24, 2011

PhoneyDeveloper said:
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?

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

If the database is opened (and/or created) successfully, then SQLITE_OK is returned.

Wow is that dumb.

Nevermind then, OP, you need to look into [NSBundle mainBundle] to get the full path to your file.

PhoneyDeveloper · Apr 24, 2011

You can use sqlite3_open_v2() if you don't want it to create a new empty database.

tutiplain · Apr 24, 2011

You guys are right. I have a copy of my SQLite file on my HD root folder!

Another point, are you sure you want this code outside the first if block ?

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.

tutiplain · Apr 25, 2011

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.

PhoneyDeveloper · Apr 25, 2011

Code:

NSString* path = [[NSBundle mainBundle] pathForResource:@"filename" ofType:@"sqlite"];

tutiplain · Apr 25, 2011

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!

Search

Search

iOS Help with "no such table" error with SQLite

tutiplain

macrumors member

PhoneyDeveloper

macrumors 68040

KnightWRX

macrumors Pentium

PhoneyDeveloper

macrumors 68040

KnightWRX

macrumors Pentium

PhoneyDeveloper

macrumors 68040

tutiplain

macrumors member

tutiplain

macrumors member

PhoneyDeveloper

macrumors 68040

tutiplain

macrumors member

Our Staff