Pull data from sqlite3 table

Question

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).

In my .h file

@interface PunchClock : UIViewController{

    sqlite3 *db;

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

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

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

        }

    }





}

Community · Accepted Answer · 2020-06-20 09:12:55Z

A couple of thoughts:

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.

Whenever you have a SQLite error, you should make sure you're logging sqlite3_errmsg.
You also never invoke sqlite3_step to actually perform the SELECT statement.
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));
}

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.

Collectives™ on Stack Overflow

Pull data from sqlite3 table

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related