从 sqlite3 表中提取数据 [英] Pull data from sqlite3 table

问题描述

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

I am trying to pull data from a specific row when the user tries to make two or more entries on the same date(date being the primary key).

在我的 .h 文件中

@interface PunchClock : UIViewController{

    sqlite3 *db;

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

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

In my .m file (not sure if any of the follow code is needed for deciphering my problem, but ill post it anyways)

-(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 是 date ，我的主键)我想提取该数据更新它并更新表.我卡在我从行中提取数据的部分(检查注释以进行澄清):

Now here is where my problem is. I can successfully insert data BUT when the user inputs data on the same date(field 1 being date , my primary key) I want to pull that data out update it and update the table. Im stuck at the part where I pull the data from the row(check comments for clarification):

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

        }

    }





}

推荐答案

一些想法:

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

1. You're not logging sqlite3_errmsg if your sqlite3_prepare_v2 failed, and I would have thought it would, as you don't specify what fields to return from your SELECT statement.

每当您遇到 SQLite 错误时，您都应该确保您正在记录 sqlite3_errmsg.

Whenever you have a SQLite error, you should make sure you're logging sqlite3_errmsg.

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

You also never invoke sqlite3_step to actually perform the SELECT statement.

最后，sqlite3_column_text返回的是基于0的索引，而不是基于1的索引，所以第一列是0，第二列是1.正如 文档 所说，结果集最左边的列的索引为 0."

Finally, sqlite3_column_text returns 0-based index, not 1-based index, so the first column is 0 and the second one is 1. As the docs say, "The leftmost column of the result set has the index 0."

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

So, I would have thought that you'd want something like:

// 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 文档.

---

By the way, while you can do this SELECT first to see if there's a match and do an UPDATE if you find one, and do an INSERT if not, it might just be easier to use the SQLite INSERT OR REPLACE syntax. See the INSERT documentation.

这篇关于从 sqlite3 表中提取数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！