开发者

Invalid SQLite table on iPhone?

开发者 https://www.devze.com 2023-02-20 15:54 出处:网络
I\'ve been trying to access an SQLite3 database via my iphone, but I keep on getting \"no such table: user_info\" as an error.

I've been trying to access an SQLite3 database via my iphone, but I keep on getting "no such table: user_info" as an error.

So, here are the steps I've gone through:

    开发者_JAVA百科
  1. Create the database via command line:

    • sqlite3 users.sqlite
    • create table user_info (name text, info text);
    • insert into user_info value('Name1', 'This is info for Name1');
    • select * from user_info;
    • [result]: Name1|This is info for Name1
    • select * from sqlite_master;
    • [result]: table|user_info|user_info|3|CREATE TABLE user_info (name text, info text)
  2. Copy this into the resources folder in XCode, with the option to copy it to the appropriate directory.

  3. Attempt to access it and get the error "no such table: user_info".

Okay, so how am I doing #3? Well, I've updated a bit, so now I try to create the table if it's non-existent. Here is my current code:

static NSString *dbname = @"users.sqlite";

-(NSString *) dbFilePath {
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(
        NSDocumentDirectory, NSUserDomainMask, YES
    );
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:dbname];
    BOOL success = [fileManager fileExistsAtPath:dbPath];
    if (!success) {
        fprintf(stderr, "Database is not writeable!\n");
    }
    return dbPath;
}

- (void)createEditableCopyOfDatabaseIfNeeded {
    BOOL success;
    NSError *error;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSString *writeableDBPath = [self dbFilePath];

    success = [fileManager fileExistsAtPath:writeableDBPath];
    if (success) {
        return;
    }

    NSString *defaultDBPath =
        [[[NSBundle mainBundle] resourcePath]
            stringByAppendingPathComponent:dbname];

    success = [fileManager
                    copyItemAtPath:defaultDBPath
                    toPath:writeableDBPath
                    error:&error];

    if (!success) {
        fprintf(stderr, "Failed to create writable database file\n");
    }
}

-(void) openDB {
    if (sqlite3_open([[self dbFilePath] UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        fprintf(stderr, "Database failed to open.\n");
    }
}

-(void) createTableIfNeeded {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "CREATE TABLE IF NOT EXISTS user_info (name text info text);"
    );

    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        NSAssert1(0, "no user_info table!", nil);
        fprintf(
            stderr,
            "Error in preparation of query: %s\n", 
            sqlite3_errmsg(db)
        );
        sqlite3_close(db);
        return;
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);
}

- (NSString *)getDatabaseEntry:(NSString *)i_name {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "SELECT 'info' FROM 'user_info' WHERE name='%q'",
        [i_name cStringUsingEncoding:NSASCIIStringEncoding]
    );

    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        fprintf(
            stderr,
            "Error in preparation of query: %s\n", 
            sqlite3_errmsg(db)
        );
        sqlite3_close(db);
        return result;
    }
    dbresult = sqlite3_step(statement);
    if (SQLITE_ROW == dbresult) {
        char *nfo = (char *)sqlite3_column_text(statement, 0);
        result = [NSString stringWithUTF8String:nfo];
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);
    return result;
}

// viewDidAppear
- (void)viewDidAppear:(BOOL)animated {
    [self createEditableCopyOfDatabaseIfNeeded];
    [self openDB];
    [self createTableIfNeeded];
}

// Later, when I have a valid user:
- (BOOL)peoplePickerNavigationController:(ABPeoplePickerNavigationController *)peoplePicker
        shouldContinueAfterSelectingPerson:(ABRecordRef)person {
    ...
    NSString *nfo = [self getDatabaseEntry:name.text];
    ...
}

I've been against this wall for a couple of days now, so any help is greatly appreciated.


Well, I fixed it, but I'm not certain on how. I created a blank project and followed the tutorial here: http://www.raywenderlich.com/913/sqlite-101-for-iphone-developers-making-our-app And made sure that worked. (It did.)

I then used the bit from here: http://www.raywenderlich.com/725/how-to-read-and-write-xml-documents-with-gdataxml To copy the database into a location where it's writeable. I had very similar code before.

So now it works. I built a new table specifically with the "sqlite3" extensions. I don't know if this actually makes a difference or not. I also used:

[[NSBundle mainBundle] pathForResource:@"users" ofType:@"sqlite3"];

As opposed to:

[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:dbname];

Again, no idea if this matters or not.

Hopefully this helps someone else out!

//----------------------------------------------------------------------------
-(NSString *) dbFilePath {
    NSError *error;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory,
                                                         NSUserDomainMask,
                                                         YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    NSString *dbPath = [documentsDir stringByAppendingPathComponent:@"users.sqlite3"];
    BOOL success = [fileManager fileExistsAtPath:dbPath];
    if (!success) {
        fprintf(stderr, "Database is not writeable!  Attempting to create writeable database.\n");
        NSString *bundle_path = [[NSBundle mainBundle] pathForResource:@"users" ofType:@"sqlite3"];
        success = [fileManager copyItemAtPath:bundle_path toPath:dbPath error: &error];
        if (!success) {
            fprintf(stderr, "Failed to create writeable database file\n");
        }
    }
    return dbPath;
}

//----------------------------------------------------------------------------
-(void) openDB {
    if (sqlite3_open([[self dbFilePath] UTF8String], &db) != SQLITE_OK) {
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open.");
        fprintf(stderr, "Database failed to open.\n");
    }
}

//----------------------------------------------------------------------------
- (NSString *)getDatabaseEntry:(NSString *)i_name {
    NSString *result = @"";
    sqlite3_stmt *statement;

    char *sql = sqlite3_mprintf(
        "SELECT info FROM user_info WHERE name='%q'",
        [i_name cStringUsingEncoding:NSASCIIStringEncoding]
    );
    dbresult = sqlite3_prepare_v2(db, sql, strlen(sql), &statement, NULL);
    if (SQLITE_OK != dbresult) {
        fprintf(stderr,
                "Error in preparation of query: %s\n",
                sqlite3_errmsg(db));
        sqlite3_close(db);
        sqlite3_free(sql);
        return result;
    }

    dbresult = sqlite3_step(statement);
    if (SQLITE_ROW == dbresult) {
        char *nfo = (char *)sqlite3_column_text(statement, 0);
        result = [NSString stringWithUTF8String:nfo];
    }
    sqlite3_finalize(statement);
    sqlite3_free(sql);

    return result;
}
0

精彩评论

暂无评论...
验证码 换一张
取 消