PDA

View Full Version : Help with "no such table" error with SQLite




tutiplain
Apr 23, 2011, 10:13 PM
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:


-(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, 11:19 AM
You need to use the full path to the file in sqlite3_open.

KnightWRX
Apr 24, 2011, 11:30 AM
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 :



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 ?

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, 12:11 PM
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, 12:27 PM
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. :eek: Nevermind then, OP, you need to look into [NSBundle mainBundle] to get the full path to your file.

PhoneyDeveloper
Apr 24, 2011, 12:49 PM
You can use sqlite3_open_v2() if you don't want it to create a new empty database.

tutiplain
Apr 24, 2011, 09:58 PM
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, 12:51 PM
Hi everyone,

I did the following:


NSLog([[NSBundle mainBundle] resourcePath]);


and the following appeared on my debugger:

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, 01:45 PM
NSString* path = [[NSBundle mainBundle] pathForResource:@"filename" ofType:@"sqlite"];

tutiplain
Apr 25, 2011, 05:05 PM
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!