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.

I think `SELECT todo.*` is right, the problem is in `pk=?`, no pass parameter value. — Nekak Kinich, Oct 29 '13 at 16:23
My apologies, my CREATE statement for the "match" table should have stated "code10 VARCHAR(25)" instead of "name". By crash out, I mean the program aborts, ending on the NSAssert1 line. pk is being passed a value through the program and I also added in a manual value to double check. I have also tried editing the SELECT todo.* to list the individual columns to no affect. I'm trying to figure out why the program is going straight to the NSAssert1 instead of executing the table join successfully in the SELECT statement. — AaronB, Oct 29 '13 at 17:30
0x000b234e <+0007> call 0xb2353 <-[NSAssertionHandler handleFailureInMethod:object:file:lineNumber:description:]+12> 0x000b23bb <+0116> add $0x34,%esp <---debugger — AaronB, Oct 29 '13 at 17:41
I see what you're asking. Here's the message. Error: failed to prepare statement with message 'no such table: match' — AaronB, Oct 29 '13 at 18:59
@AaronB Then the table doesn't exist in the database. See my answer below. — Rob, Oct 29 '13 at 19:28

Rob · Accepted Answer · 2013-10-29T20:50:56.230

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.

That's exactly what happened. Thank you, Rob! – AaronB Oct 29 '13 at 19:40 — AaronB, Oct 29 '13 at 19:40

JOIN not working in sqlite query compile

1 Answers1