No Such Table error

Question

I had to search for many posts regarding these errors, but still I cannot fix the problem. Here is my code, can anyone help me to see what is going wrong?

- (void) copyDatabaseIfNeeded {       NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);     NSString *documentsDirectory = [paths objectAtIndex:0];    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"SQL.sqlite"];      if (sqlite3_open([path UTF8String], &newDBconnection) == SQLITE_OK)      {         NSLog(@"Database opened successfully");  if(updateStmt == nil) {             NSString *updStmt = [NSString stringWithFormat: @"UPDATE Coffee SET CoffeeName = '500 Plus', Price = '1.40' Where CoffeeID= '3'"];    const char *mupdate_stmt = [updStmt UTF8String];   if(sqlite3_prepare_v2(newDBconnection, mupdate_stmt, -1, &updateStmt, NULL) != SQLITE_OK){ NSAssert1(0, @"Error while creating update statement. '%s'", sqlite3_errmsg(newDBconnection));             } else {                 NSLog(@"Update successful");             }          }         if(SQLITE_DONE != sqlite3_step(updateStmt))             NSAssert1(0, @"Error while updating. '%s'", sqlite3_errmsg(newDBconnection));         else {             sqlite3_reset(updateStmt);             NSLog(@"Step successful");          }     }      else      {         NSLog(@"Error in opening database  ");     } } 

Answer 1

There is no table Coffee in SQL.sqlite.

SQLite silently creates the database file if it does not exist. So if you've got the path wrong, you are opening an empty database file, which of course does not contain any tables. Make sure the database file exists there and it is not empty.

You can see what tables are there in the database by running SELECT * FROM sqlite_master; query.

Answer 2

One thing my DBA tells me when I have database problems (Oracle in this case), is to try the query using the command line tools.

This is how you would diagnose problems with sqlite3 and the iPhone simulator:

1. Run the App in the iPhone Simulator so the database is created/copied in the Documents directory.
2. Start Terminal
3. cd ~/Library/Application Support/iPhone Simulator/<version>/Applications/<your-app-hash>/Documents. It's best to have just one App installed in the simulator so it's obvious what App is what.
4. sqlite3 SQL.sqlite and try your query from there. You can also use the .schema command to see what the schema looks like.

If the query works there and not in your App then you know you have a bug, else you have a sqlite query problem or broken schema.