Update Statement in SQLite and make app work on device

gotenks05 · Apr 24, 2012

Hello, I am currently trying to update an app so that it has a database backend. However, I am having two problems. First, I cannot seem to get the update statements working. I would like to allow users to make changes in fields and with a push of the button, the app will compare what the user entered with what the database has and update the necessary fields. It saves and loads data fine (via two separate buttons), but when I tried my update method, I get a message saying:

Record could not be update

The app is supposed to show that message when an update did not go through for some reason. When I look at the database file that the simulator uses, the record is indeed not updated, so I am not too sure what is wrong here.

Second, my app runs fine on the simulator, but when I plug in my iPad and load the app on, XCodes displays

Program received signal: "EXC_BAD_ACCESS"

which points to the variable that sets the view controllers in the appdelegate file.

I am pretty sure these things have something to do with the database back-end as these issues do not happen if I do not use SQLite, nor any other method of saving data, so here are the source files.

rmdSQL.h:

Code:

#import <UIKit/UIKit.h>
#import <sqlite3.h>
@interface rmdSQL : NSObject
{
    NSString* dbpath;
    sqlite3 *mrdDB;
    NSString *birth;
    int records, year;
    double bal;
    
    // variables to get document directory
    NSArray *dirPaths;
    NSString *docsDir;
    
    const char* dpath;    
}

+(rmdSQL*) sql;
/* the following are setter methods or methods to perform SQL operations like UPDATE and INSERT */
-(void)setDBPath:(NSString*)f;
-(void)setDPath:(NSString*)g;
-(BOOL)saveData:(NSString*)a:(double)b:(int)c;
-(BOOL)updateData:(NSString*)d:(double)e:(int)f;
/* the following three are used to retrieve the currently saved data for input to programs */
-(NSString*)birth;
-(double)bal;
-(int)year;
-(BOOL)records; // method used to determine whether update is possible or not
// the following gets the database path
-(const char*)dpath;
-(NSString*)dbpath;
@end

rmdSQL.m:

Code:

#import "rmdSQL.h"

@implementation rmdSQL
+(rmdSQL*) sql
{
    rmdSQL *newSQL = [[rmdSQL alloc] init];
    return [newSQL autorelease];
}

- (id) init
{
    if (self = [super init])
    {
        [self setDBPath:nil];
        [self setDPath:nil];
        [self saveData:nil :0 :0];
        [self updateData:nil :0 :0];
        
        // set strings with no setter methods to zero or null
        birth = nil;
        bal = 0;
        year = 0;
        records = 0;
    }
    return self;
}

-(void)setDBPath:(NSString *)f
{
    // get document directory
    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];
    
    // build database path
    dbpath = [[NSString alloc] initWithString:[docsDir stringByAppendingPathComponent:f]];
}

-(void)setDPath:(NSString *)g
{
    dpath = [g UTF8String];
}

-(NSString*)dbpath
{
    return dbpath;
}

-(const char*)dpath
{
    return dpath;
}

-(BOOL)saveData:(NSString *)a :(double)b :(int)c
{
    int test;
    
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        sqlite3_stmt *stmt;
        NSString *insertSQL = [NSString stringWithFormat:@"INSERT INTO rmd (birth, bal, year) VALUES (?, ?, ?)"];
        const char *insert_stmt = [insertSQL UTF8String];
        
        sqlite3_prepare_v2(mrdDB, insert_stmt, -1, &stmt, NULL);
        
        sqlite3_bind_text(stmt, 1, [a UTF8String], -1, SQLITE_TRANSIENT);
        sqlite3_bind_double(stmt, 2, b);
        sqlite3_bind_int(stmt, 3, c);
        
        if (sqlite3_step(stmt) == SQLITE_DONE) 
        {
            test = 1;
        } else {
            test = 0;
        }
        
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);
    }
    
    if (test == 0) 
    {
        return NO;
    }
    return YES;
}

-(NSString*)birth
{
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        sqlite3_stmt *stmt;
        NSString* birth_sql = [NSString stringWithFormat:@"SELECT date(birth) FROM rmd LIMIT 1"];
        const char* birth_stmt = [birth_sql UTF8String];
        
        sqlite3_prepare_v2(mrdDB, birth_stmt, -1, &stmt, NULL);
        if (sqlite3_step(stmt) == SQLITE_ROW) 
        {
            if ((char *)sqlite3_column_text(stmt, 0))
            {
                birth = [NSString stringWithUTF8String:(char *)sqlite3_column_text(stmt, 0)];
            }
        } else;
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);        
    }
    return birth;
}
-(double)bal
{
    sqlite3_stmt *stmt;
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        NSString* ysql = [NSString stringWithFormat:@"SELECT bal FROM rmd LIMIT 1"];
        const char* y_stmt = [ysql UTF8String];
        
        sqlite3_prepare_v2(mrdDB, y_stmt, -1, &stmt, NULL);
        if (sqlite3_step(stmt) == SQLITE_ROW) 
        {
            if (sqlite3_column_double(stmt, 0))
            {
                bal = sqlite3_column_double(stmt, 0);
            }
        } else;
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);
    }
    return bal;
}

-(int)year
{
    sqlite3_stmt *stmt;
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        NSString* ysql = [NSString stringWithFormat:@"SELECT year FROM rmd LIMIT 1"];
        const char* y_stmt = [ysql UTF8String];
        
        sqlite3_prepare_v2(mrdDB, y_stmt, -1, &stmt, NULL);
        if (sqlite3_step(stmt) == SQLITE_ROW) 
        {
            if (sqlite3_column_int(stmt, 0))
            {
                year = sqlite3_column_int(stmt, 0);
            }
        } else;
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);
    }
    return year;
}

-(BOOL)records
{
    sqlite3_stmt *stmt;
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        NSString* rsql = [NSString stringWithFormat:@"SELECT  COUNT(*) rmd"];
        const char* r_stmt = [rsql UTF8String];
        
        sqlite3_prepare_v2(mrdDB, r_stmt, -1, &stmt, NULL);
        if (sqlite3_step(stmt) == SQLITE_ROW) 
        {
            records = sqlite3_column_int(stmt, 0);
            return records;
        } else;
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);
    }
    return NO;
}
-(BOOL)updateData:(NSString *)d :(double)e :(int)f
{
    int test;
    if (sqlite3_open([self dpath], &mrdDB) == SQLITE_OK) 
    {
        sqlite3_stmt *stmt;
        NSString* updateSQL;
        const char *update_stmt;
        
        if ([[self birth] isEqualToString:d] != YES) 
        {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET birth =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_text(stmt, 1, [d UTF8String], -1, SQLITE_TRANSIENT);
        } else if ([self bal] != e) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET bal =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_double(stmt, 1, e);
        } else if ([self year] != f) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET year =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_int(stmt, 1, f);
        } else if ([[self birth] isEqualToString:d] != YES && [self bal] != e) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET birth =?, bal =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_text(stmt, 1, [d UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_double(stmt, 2, e);
        } else if ([[self birth] isEqualToString:d] != YES && [self year] != f) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET birth =?, year =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_text(stmt, 1, [d UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_int(stmt, 2, f);
        } else if ([self bal] != e && [self year] != f) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET bal =?, year =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_double(stmt, 1, e);
            sqlite3_bind_int(stmt, 2, f);
        } else if ([[self birth] isEqualToString:d] != YES && [self bal] != e && [self year] != f) {
            updateSQL = [NSString stringWithFormat:@"UPDATE rmd SET birth =?, bal =?, year =? WHERE id = 1"];
            update_stmt = [updateSQL UTF8String];
            sqlite3_prepare_v2(mrdDB, update_stmt, -1, &stmt, NULL);
            
            sqlite3_bind_text(stmt, 1, [d UTF8String], -1, SQLITE_TRANSIENT);
            sqlite3_bind_double(stmt, 2, e);
            sqlite3_bind_int(stmt, 3, f);
        } else;
        
        if (sqlite3_step(stmt) == SQLITE_DONE) 
        {
            test = 1;
        } else {
            test = 0;
        }
        
        sqlite3_finalize(stmt);
        sqlite3_close(mrdDB);
    }
    
    if (test == 0) 
    {
        return NO;
    }
    return YES;
}

-(void) dealloc
{
    [self setDBPath:nil];
    [self setDPath:nil];
    [self saveData:nil :0 :0];
    [self updateData:nil :0 :0];
    [super dealloc]; // free memory
}

If you guys need the other source code files, I will provide more.

Update Statement in SQLite and make app work on device

gotenks05

Shop Amazon