ios - 从 sqlite3 表中提取数据

Question

当用户试图在同一日期(日期是主键)输入两个或多个条目时，我试图从特定行中提取数据。

在我的 .h 文件中

@interface PunchClock : UIViewController{

    sqlite3 *db;

}
-(NSString *) filePath;
-(void)openDB;
-(void) createTable: (NSString *) tableName
         withField1:(NSString *) field1
         withField2:(NSString *) field2
         withField3:(NSString *) field3;

在我的 .m 文件中(不确定是否需要以下任何代码来破译我的问题，但无论如何我都会发布它)

-(void) createTable: (NSString *) tableName
         withField1:(NSString *) field1
         withField2:(NSString *) field2
         withField3:(NSString *) field3;
{
    char *err;
    NSString *sql=[NSString stringWithFormat:@" CREATE TABLE IF NOT EXISTS '%@'('%@' TEXT PRIMARY KEY,'%@' TEXT,'%@' TEXT);",tableName,field1,field2,field3];

    if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){
        sqlite3_close(db);
        NSAssert(0, @"Could not create table");
    }else{
        NSLog(@"table created");
    }
}



-(NSString *) filePath{
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentsDirectory = [paths objectAtIndex:0];

    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"PunchClock.sql"];

    return path;
}

-(void)openDB{
    if(sqlite3_open([[self filePath] UTF8String], &db) != SQLITE_OK){
        sqlite3_close(db);
        NSAssert(0, @"Database failed to open");
    }else{
        NSLog(@"database opened");
    }
}

这就是我的问题所在。我可以成功插入数据但是当用户在同一日期输入数据时(字段 1 是日期，我的主键)我想提取数据更新它并更新表。我卡在了从行中提取数据的部分(检查注释以进行澄清):

- (IBAction)save:(id)sender {
{
        NSString *HoursWorked = [NSString stringWithFormat:@"%f", final];
        NSString *TotalEarned = [NSString stringWithFormat:@"%f", paidFloat];

        NSString *sql = [NSString stringWithFormat:@"INSERT INTO HourLog ('Date', 'HoursWorked','TotalEarned') VALUES ('%@', '%@', '%@')", dbDate , HoursWorked, TotalEarned];

        char *err;
        if(sqlite3_exec(db, [sql UTF8String], NULL, NULL, &err) != SQLITE_OK){
            sqlite3_close(db);
           // NSAssert(0,@"Could not update table");

            //primary key already exists

            [self openDB];
            NSString *currentHoursWorked = HoursWorked;
            NSString *currentTotalEarned = TotalEarned;
            NSString *previousHoursWorked;
            NSString *previousTotalEarned;


            NSString *sql = [NSString stringWithFormat:@"SELECT FROM HourLog WHERE Date = '%@'", dbDate];

            sqlite3_stmt *statement;
            // the if statement below is not being executed for some reason? Can someone see why?
            if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)==SQLITE_OK){

                char *field2 = (char *) sqlite3_column_text(statement, 1);
                NSString *field2Str = [[NSString alloc]initWithUTF8String:field2];
                previousHoursWorked = field2Str;

                char *field3 = (char *) sqlite3_column_text(statement, 2);
                NSString *field3Str = [[NSString alloc]initWithUTF8String:field3];
                previousTotalEarned = field3Str;

            }

            sqlite3_finalize(statement);

           sqlite3_close(db);

            NSLog(@"%@", previousHoursWorked); 
            NSLog(@"%@", previousTotalEarned);



        }else{
            NSLog(@"table updated");

        }

    }





}

Answer 1

几个想法:

1. 如果您的 sqlite3_prepare_v2 失败，您不会记录 sqlite3_errmsg，我原以为会，因为您没有指定要返回的字段你的 SELECT 语句。

   无论何时遇到 SQLite 错误，都应确保记录 sqlite3_errmsg。

2. 您也永远不会调用 sqlite3_step 来实际执行 SELECT 语句。

3. 最后，sqlite3_column_text 返回从 0 开始的索引，而不是从 1 开始的索引，所以第一列是 0，第二列是 1 。作为the docs比如，“结果集最左边的列的索引为 0。”

所以，我以为你会想要这样的东西:

// replace column_a and column_b with your column names

NSString *sql = [NSString stringWithFormat:@"SELECT column_a, column_b FROM HourLog WHERE Date = '%@'", dbDate];

sqlite3_stmt *statement;

if(sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil) == SQLITE_OK){

    int rc;
    if ((rc = sqlite3_step(statement)) == SQLITE_ROW) {
        char *field2 = (char *) sqlite3_column_text(statement, 0);
        NSString *field2Str = [[NSString alloc]initWithUTF8String:field2];
        previousHoursWorked = field2Str;

        char *field3 = (char *) sqlite3_column_text(statement, 1);
        NSString *field3Str = [[NSString alloc]initWithUTF8String:field3];
        previousTotalEarned = field3Str;
    } else if (rc != SQLITE_DONE) {
        NSLog("%s: sqlite3_step failed: %s", __FUNCTION__, sqlite3_errmsg(db));
    }    
} else {
    NSLog("%s: sqlite3_prepare_v2 failed: %s", __FUNCTION__, sqlite3_errmsg(db));
}

---

顺便说一下，虽然您可以先执行 SELECT 以查看是否有匹配项，如果找到匹配项，则执行 UPDATE，然后执行 INSERT 如果没有，使用 SQLite INSERT OR REPLACE 语法可能会更容易。请参阅 INSERT documentation .