Вопрос по objective-c, firefox, ios, sqlite, iphone – Правильный способ вставки данных в SQLite db iPhone

2

Как вставить данные в таблицу в sqlite iPhone?

Я пытаюсь следовать, но это не удается:

<code> NSString *query=[NSString stringWithFormat:@"insert into %@ (name) values ('%@')", table name,myName ];


 sqlite3 *database;

sqlite3_stmt *createStmt = nil;

if (sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
    if (createStmt == nil) {

        if (sqlite3_prepare_v2(database, [query UTF8String], -1, &createStmt, NULL) != SQLITE_OK) {
            return NO;
        }
        sqlite3_exec(database, [query UTF8String], NULL, NULL, NULL);
        return YES;
    }

    return YES;
}else {
    return NO;
}
</code>

Я создал таблицу следующим образом:

<code>create table if not exists myDets (dets_id integer primary key asc, name text);
</code>

Я также использую плагин Firefox SQLite для проверки БД. Когда я пытаюсь вставить запись через Firefox в мою базу данных, это дает мне следующую ошибку:

<code>Failed to insert values
Exception Name: NS_ERROR_STORAGE_IOERR
Exception Message: Component returned failure code: 0x80630002 (NS_ERROR_STORAGE_IOERR)        [mozIStorageStatement.execute]
</code>

Плохо застрял :(

Кроме того, я запускаю это на симуляторе iPhone, это имеет значение?

Пожалуйста помоги

Заранее спасибо.

Ваш Ответ

2   ответа
3
- (NSString *) getDBPath {

    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory , NSUserDomainMask, YES);
    NSString *documentsDir = [paths objectAtIndex:0];
    return [documentsDir stringByAppendingPathComponent:@"iHadDB.sqlite"];
}

- (void) insertValueToDatabase {

    NSString *dbPath =[self getDBPath];

    NSString *select_Meal = dummy_select_Meal;
    NSString *dateTime = dummy_date_Time;
    NSString *location = dummy_location;
    NSString *mealItem = dummy_mealItem;

    if (sqlite3_open([dbPath UTF8String], &database) == SQLITE_OK) {

        const char *sqlDB = "Insert into iHad(Mealtype,DateAndTime,Location,Mealitem) VALUES (?,?,?,?)";

        if(sqlite3_prepare_v2(database, sqlDB, -1, &addStmt, NULL) != SQLITE_OK)
            NSAssert1(0, @"Error while creating add statement. '%s'", sqlite3_errmsg(database));

        sqlite3_bind_text(addStmt, 1, [select_Meal UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 2, [dateTime UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 3, [location UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_text(addStmt, 4, [mealItem UTF8String], -1, SQLITE_TRANSIENT);

        if(SQLITE_DONE != sqlite3_step(addStmt))
            NSAssert1(0, @"Error while inserting data. '%s'", sqlite3_errmsg(database));
        //else
            //SQLite provides a method to get the last primary key inserted by using sqlite3_last_insert_rowid
            //coffeeID = sqlite3_last_insert_rowid(database);

        //Reset the add statement.
        sqlite3_reset(addStmt);

    }
    else
        sqlite3_close(database); 

}
3
Use parameters and don't string format your update statement Add lots of tracing, check return codes and check for error codes (you're question isn't clear what error, issue you're hitting on iPhone). use sqlite3_errmsg to get error messages construct your dbPath and log it out. Ensure you can open the db from terminal under the emulator. Remember that sqlite will create a database in memory passively so if you're path is off and the db doesn't exist it can be confusing. print out your update statement and try it from sqlite cmdline app in terminal in the path you logged. if you're putting the db in the main bundle resources, it's a template and needs to be copied in order to open and write to it.

- (void)updateContact: (Contact*)contact error:(NSError**)error
{
    if (![self ensureDatabaseOpen:error])
    {
        return;
    }

    NSLog(@">> ContactManager::updateContact");

    // prep statement
    sqlite3_stmt    *statement;
    NSString *querySQL = @"update contacts set name=?,address=?,phone=? where id=?";
    NSLog(@"query: %@", querySQL);
    const char *query_stmt = [querySQL UTF8String];

    // preparing a query compiles the query so it can be re-used.
    sqlite3_prepare_v2(_contactDb, query_stmt, -1, &statement, NULL);     
    sqlite3_bind_text(statement, 1, [[contact name] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 2, [[contact address] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_text(statement, 3, [[contact phone] UTF8String], -1, SQLITE_STATIC);
    sqlite3_bind_int64(statement, 4, [[contact id] longLongValue]);

    NSLog(@"bind name: %@", [contact name]);
    NSLog(@"bind address: %@", [contact address]);
    NSLog(@"bind phone: %@", [contact phone]);
    NSLog(@"bind int64: %qi", [[contact id] longLongValue]);

    // process result
    if (sqlite3_step(statement) != SQLITE_DONE)
    {
        NSLog(@"error: %s", sqlite3_errmsg(_contactDb));
    }

    sqlite3_finalize(statement);
}

В этом примере БД копируется из ресурсов в путь и открывается. Вот функция гарантированного открытия, которую я использую для этого:

- (BOOL)ensureDatabaseOpen: (NSError **)error
{
    // already created db connection
    if (_contactDb != nil)
    {
        return YES;
    }

    NSLog(@">> ContactManager::ensureDatabaseOpen");    
    if (![self ensureDatabasePrepared:error])
    {
        return NO;
    }

    const char *dbpath = [_dbPath UTF8String]; 
    if (sqlite3_open(dbpath, &_contactDb) != SQLITE_OK &&
        error != nil)
    {
        *error = [[[NSError alloc] initWithDomain:@"ContactsManager" code:1000 userInfo:nil] autorelease];
        return NO;
    }

    NSLog(@"opened");

    return YES;
}

- (BOOL)ensureDatabasePrepared: (NSError **)error
{
    // already prepared
    if ((_dbPath != nil) &&
        ([[NSFileManager defaultManager] fileExistsAtPath:_dbPath]))
    {
        return YES;
    }

    // db in main bundle - cant edit.  copy to library if !exist
    NSString *dbTemplatePath = [[NSBundle mainBundle] pathForResource:@"contacts" ofType:@"db"];
    NSLog(@"%@", dbTemplatePath);

    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    _dbPath = [libraryPath stringByAppendingPathComponent:@"contacts.db"];

    NSLog(@"dbPath: %@", _dbPath);

    // copy db from template to library
    if (![[NSFileManager defaultManager] fileExistsAtPath:_dbPath])
    {
        NSLog(@"db not exists");
        NSError *error = nil;
        if (![[NSFileManager defaultManager] copyItemAtPath:dbTemplatePath toPath:_dbPath error:&error])
        {
            return NO;
        }

        NSLog(@"copied");
    }    

    return YES;    
}
Спасибо bryanmac, попробую ... iOSDev
Большой! это работает для меня тоже.

Похожие вопросы