ios - 从 sqlite3 表中提取数据

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

在我的 .h 文件中

@interface PunchClock : UIViewController{

    sqlite3 *db;

}
-(NSString *) filePath;
-(void)openDB;
-(void) createTable: (NSString *) tableName
         withField1NSString *) field1
         withField2NSString *) field2
         withField3NSString *) field3;

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

-(void) createTable: (NSString *) tableName
         withField1NSString *) field1
         withField2NSString *) field2
         withField3NSString *) 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"unchClock.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 是 date ，我的主键)我想拉出该数据更新它并更新表。我停留在我从行中提取数据的部分(检查评论以进行澄清):

- (IBAction)saveid)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");

        }

    }





}

---

Best Answer-推荐答案

一些想法:

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 .

关于ios - 从 sqlite3 表中提取数据，我们在Stack Overflow上找到一个类似的问题： https://stackoverflow.com/questions/19827046/