JOIN not working in sqlite query compile

Question

I'm writing a simple navigation view iphone program using sqlite for learning purposes. I previously had a single table in the database, but have now updated it to two and my INNER JOIN statement crashes out. The SQL statement seems to work just fine running it directly. In the code below the uncommented statement works just fine, but the commented one will kick out to the error if I switch them out.

static sqlite3_stmt *init_statement = nil;
static sqlite3_stmt *dehydrate_statment = nil;

@implementation ICD9
@synthesize primaryKey,text,priority,status,match;

- (id)initWithPrimaryKey:(NSInteger)pk database:(sqlite3 *)db {

    if (self = [super init]) {
        primaryKey = pk;
        database = db;
        if (init_statement == nil) {
            const char *sql = "SELECT text,priority,complete FROM todo WHERE pk=?"; 
        //const char *sql = "SELECT todo.*, match.code10 FROM todo INNER JOIN match ON match.text = todo.text WHERE pk=1;";
        if (sqlite3_prepare_v2(database, sql, -1, &init_statement, NULL) != SQLITE_OK) {
                NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
        }
    }
    return self;
}

The tables are in the same db:

CREATE TABLE todo(pk INTEGER PRIMARY KEY, text VARCHAR(25), priority INTEGER, complete BOOLEAN);
CREATE TABLE match(matchid INTEGER PRIMARY KEY, text VARCHAR(25), name VARCHAR(25));

I'm pretty new to this so any help would be appreciated.

Answer 1

The key is to examine the error message returned by sqlite3_errmsg. You report that your NSAssert line reports the message:

failed to prepare statement with message 'no such table: match'

That means that the database you've opened does not have a match table. If you've run this on the simulator, the easiest thing to do is to open the database in your MacOS SQLite tool of choice (I use Base, you can use the sqlite3 command line tool; use whatever you want). The database can be found in your ~/Library/Application Support/iPhone Simulator folder. To make it easier to find this database, you might want to unhide your ~/Library folder by firing up the Terminal app and then running the command chflags nohidden ~/Library/.

Anyway, I think you'll find the match table is not there (perhaps the entire database will be blank). A common reason for that is invoking sqlite_open on a database that doesn't exist, in which case it will create a blank database for you.

If this is the case, you want to

• remove any blank databases you might have in your Documents folder (the easiest way to do that is to remove the app from your device/simulator) and run it again; and

• check your database opening logic, which should probably look something like:

  NSString *filename = @"yourdb.sqlite";
  NSFileManager *fileManager = [NSFileManager defaultManager];
  NSString *bundlePath = [[NSBundle mainBundle] pathForResource:[filename stringByDeletingPathExtension] ofType:[filename pathExtension]];
  NSString *documentsPath = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES)[0];
  NSString *path = [documentsPath stringByAppendingPathComponent:filename];
  
  if (![fileManager fileExistsAtPath:path isDirectory:NO])
  {
      NSError *error = nil;
      [fileManager copyItemAtPath:bundlePath toPath:path error:&error];
      NSAssert(error == nil, @"Unable to copy %@ to %@", bundlePath, path);
  }
  

  Clearly, this assumes that you have a database ready for use in your bundle. If you're creating that programmatically, then just do that inside the if block where you determined that it's not the case that fileExistsAtPath.