sqlite prepare statement error - no such table

Question

I'm having some difficulty with my sqlite prepare statement. I get an error saying my table does not exist, although I've checked in multiple places for it, and it does exist, so I'm confuzzled.

• The file is in the correct iPhone Simulator Application folder
• The file is added to my project and viewable in the project navigator
• It is also in my build phases- Copy Bundle Resources area.
• I've cleaned and started running again.

• The database exists and running my sql statement gets me just the results I expected.

  - (NSMutableArray *) getMyWorkout{
  NSMutableArray *workoutArray = [[NSMutableArray alloc] init];
  @try {
  NSFileManager *fileMgr = [NSFileManager defaultManager];
  
  NSString *dbPath = [[[NSBundle mainBundle] resourcePath ]stringByAppendingPathComponent:@"IOSDB.sqlite"];
      NSLog(@"Db path is %@",dbPath);
  BOOL success = [fileMgr fileExistsAtPath:dbPath];
  
  if(!success) {
      NSLog(@"Cannot locate database file '%@'.", dbPath);
  }
  
  if(!(sqlite3_open([dbPath UTF8String], &db) == SQLITE_OK)){
      sqlite3_close(db);
      NSLog(@"Failed to open database with message '%s'.", sqlite3_errmsg(db));
  }
  
  const char *sql = "SELECT Id, type, difficulty, duration, description FROM workoutTbl";
  sqlite3_stmt *sqlStatement;
  
  if(sqlite3_prepare(db, sql, -1, &sqlStatement, NULL) != SQLITE_OK){
  
  NSLog(@"%s Prepare failure '%s' (%1d)", __FUNCTION__, sqlite3_errmsg(db), sqlite3_errcode(db));
  }        //...
  

When I run it, I get the file path and the following error

    2013-02-01 18:07:08.060 TriShake[9251:c07] -[MyWorkoutList getMyWorkout] Prepare failure 'no such table: workoutTbl' (1)

I've checked out these other questions, but have been unable to find a solution

• Sqlite Prepare Failed: no such table<tablename>
• Sqlite3 gives "no such table" error on iPhone

I understand sqlite3_open() creates an empty database for you if the database path does not exist, but i know it exists, so frustration ensues. Any help or guidance you could give me would be much appreciated.

Solution 1

In terms of your immediate problem, it's going to be something simple.

1. You say you've "cleaned and built again", but have you actually removed the old app from your simulator? Either remove the app manually, or, easier, just reset the simulator entirely by choosing "reset content and settings" from the "iOS Simulator" menu. Sometimes Xcode is not good about knowing what files to copy over (particularly in a case like this where your running it on the device may be changing the timestamp of the file in the simulator's bundle!)

2. Run the app again.

3. If the app doesn't work as expected, open up the database in the simulator folder from the Mac and check out the database to make sure the table is there and precisely as you expected it to be. So navigate to the app, open the bundle (you may have to choose the "show package contents" option), confirm the existence of the database, but just as importantly, open it up this particular copy of the database in your favorite Mac sqlite3 tool of choice and confirm the existence of the table there.

Let us know what you find. Again, it's got to be something simple such as:

• Perhaps the process of rebuilding the app was not reinstalling everything; I've occasionally had problems where Xcode elected to not re-copy something during the install on my simulator;

• Perhaps your database in your project was accidentally put in a subdirectory, worse, you might have two copies sitting in different directories;

• Perhaps the database in your Xcode project is missing (or has a typo or (esp in the case of the device) has incorrect filename capitalization) in the name of the table or file;

• Etc.

For a lot of these errors, you won't notice the problem until you completely reset the simulator itself. There are a million little things it could be, but hopefully completely resetting the simulator and starting over will help you find the issue. It's always something simple when it comes to these sorts of issues.

---

Some other minor observations:

1. You probably should not be opening databases from the bundle. Programmatically copy it from the bundle to the Documents folder, and open the database from there. I know it seems unnecessary, but it's important for a myriad of reasons (if db changes during operation of the app, if db accidentally gets created on you, don't let Xcode get confused about things that changed (even if only file timestamps) in the bundle changing behind Xcode's back, etc.)

2. You should, if you need the database to be there, use sqlite3_open_v2, using either SQLITE_OPEN_READWRITE or SQLITE_OPEN_READONLY for flags (but do not include SQLITE_OPEN_CREATE). It causes headaches to ever give sqlite a chance to create a blank database for you, or otherwise modify it, so never give it an opportunity to so.

Solution 2

I have encounter the same problem as yours. If the IOS can not find the designated database file, defaultly it will create one for you instead of throwing an error. So you must open the database file IOS created for you which is blank so it off course contain the table you expected. what I deal with it : 1 you have to bundle the resource file named *.sqlite3 into your project 2 Then You have to use [NSBundle mainBundle] pathFordirectory...... function to search your proper database file. then you can open the database file you expected and can operate it properly

Best regards,

Solution 3

Not enough rep to comment on Jack's post, but that helped me.

In my case, I had mistyped my path for resource extension:

// Wrong
NSString *sqLiteDb = [[NSBundle mainBundle] pathForResource:@"productList"
                                                         ofType:@"sqlite3"];

// Should have been (added db ext)
NSString *sqLiteDb = [[NSBundle mainBundle] pathForResource:@"productList"
                                                     ofType:@"db"];

I would always get past the:

if (sqlite3_open([sqLiteDb UTF8String], &_database) == SQLITE_OK))

because it was automatically creating a db file for me.

Comments

[Hot Licks]

I'd give you +3 if I could -- several good suggestions there.

[nniroclax]

Great! Thank you. By reseting the contents of the simulator, I was able to get rid of my no such table error. I also checked the bundle resources and it showed that my table did exist. Now when I run it the application connects to my table OK, but then get an error that says "file is encrypted or is not a database". I guess its on to another error now! :-)

[Rob]

@nniroclax That's generally a sign that a database was corrupted somehow. I'd recreate it from scratch and see if the problem persists.