Krunal Krunal - 7 months ago 19
SQL Question

Unable to update column in sqlite

I am developing app, in which i am storing data in DB,

While storing data to DB, I am unable to update my column value in DB,

Here is my Code:

I am passing my Update query to this method:

QUERY = UPDATE Tbl SET favStatus ='0' WHERE Merchant_ID='1522' and Voucher_Id='5038'


//Calling method..

[self UpdateStatus:QUERY];

-(void)UpdateStatus:(NSString*)Query{

// Get the documents directory
databasePath=[self GetDBPath];

if(sqlite3_open([databasePath UTF8String],&contactDB)==SQLITE_OK)
{
sqlite3_stmt *compiledStmt;

if(sqlite3_prepare_v2(contactDB, [Query UTF8String],-1,&compiledStmt, NULL)==SQLITE_OK)
{
NSLog(@"Successful update");
}
sqlite3_step(compiledStmt);
sqlite3_close(contactDB);
}
}


My Tbl contains
Col1
,
Col2
,
Col3
,
Col4
,
favStatus
,
Col6
6 columns, i want to update value inside
favStatus
column.

After writing this,
favStatus
column value doesn't get updated even after Log shows
Successful update


Where is my mistake? please help.

Thanks in advnace.

Answer

Can you check, please the col named favStatus is bool type or string type defined in your DB. If you enter string value on db , but column fvStatus is bool type. and you enter VoucherId and merchant ID value as a string. Please check both the value in DB as a string or not.

Can you share your Tb1 Schema structure.

Updated:

//
//  VMViewController.m
//  iDev_Sqlite
//
//  Created by Pathik on 3/14/14.
//  Copyright (c) 2014 Pathik. All rights reserved.
//

#import "VMViewController.h"
#import <sqlite3.h>

@interface VMViewController ()
{
    sqlite3     *sqliteObj;
    NSString    *databasePath;
}
@end

@implementation VMViewController

- (void)viewDidLoad
{
    [super viewDidLoad];
    // Do any additional setup after loading the view, typically from a nib.
    [self createDatabase];
   [self insertValuesInTable];

    [self updateValuesInTable];
}

- (void)didReceiveMemoryWarning
{
    [super didReceiveMemoryWarning];
    // Dispose of any resources that can be recreated.
}

#pragma mark - SQLITE Actions

-(void)createDatabase{

    NSString *docsDir;
    NSArray *dirPaths;

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    docsDir = [dirPaths objectAtIndex:0];

    // Build the path to the database file
    databasePath = [[NSString alloc] initWithString: [docsDir stringByAppendingPathComponent: @"VM_TEST_DB.sqlite"]];

    NSFileManager *filemgr = [NSFileManager defaultManager];

    if ([filemgr fileExistsAtPath: databasePath ] == NO)
    {
        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &sqliteObj) == SQLITE_OK)
        {
            char *errMsg;
            const char *sql_stmt = "CREATE TABLE IF NOT EXISTS tbl1 (ID INTEGER PRIMARY KEY AUTOINCREMENT, favStatus INTEGER, Voucher_ID TEXT, Merchent_ID TEXT)";

            if (sqlite3_exec(sqliteObj, sql_stmt, NULL, NULL, &errMsg) != SQLITE_OK)
            {
                NSLog(@"Failed to create table");
            }

            sqlite3_close(sqliteObj);

        } else {
            NSLog(@"Failed to open/create database");
        }
    }
}




-(void)insertValuesInTable{
    sqlite3_stmt    *statement;
    const char *dbpath = [databasePath UTF8String];

    if (sqlite3_open(dbpath, &sqliteObj) == SQLITE_OK) {
        NSString *insertSQL = [NSString stringWithFormat:
                               @"INSERT INTO tbl1 (favStatus,Voucher_ID,Merchent_ID) VALUES (0,'102','102')"];

        const char*insert_stmt = [insertSQL UTF8String];
        sqlite3_prepare_v2(sqliteObj, insert_stmt,  -1, &statement, NULL);
        if (sqlite3_step(statement) == SQLITE_DONE) {
            NSLog(@"Insert Successfully||");
        } else {
            NSLog(@"INSERT OPERTAION FALIED||");
        }


        sqlite3_finalize(statement);
        sqlite3_close(sqliteObj);
    }
}

-(void)updateValuesInTable{

    if(sqlite3_open([databasePath UTF8String], &sqliteObj) == SQLITE_OK) {

        NSString *cmd = [NSString stringWithFormat:@"UPDATE tbl1 SET favStatus=1 WHERE Voucher_ID='101' AND Merchent_ID='101';"];
        const char * sql = [cmd UTF8String];
        sqlite3_stmt *compiledStatement;
        if(sqlite3_prepare_v2(sqliteObj, sql, -1, &compiledStatement, NULL) == SQLITE_OK) {
            sqlite3_step(compiledStatement); // Here is the added step.
            NSLog(@"update SUCCESS - executed command %@",cmd);
        }
        else {
            NSLog(@"update FAILED - failed to execute command %@",cmd);
        }

        sqlite3_finalize(compiledStatement);

    }
    else {
        NSLog(@" FAILED - failed to open database");
    }

    sqlite3_close(sqliteObj);


}


![output][1]
@end


  [1]: http://i.stack.imgur.com/q0RTU.png
Comments