Difficulty in fetching specific data from database

Question

I have a table having two columns as combination(text), flag(number). I am trying retrieve value of flag depending upon combination value using where clause in select query.

Problem is that when combination value is starting from 1 e.g. where combination='1001' then it retrieves correct value of flag from table but when it is starting from 0 e.g. where combination='0010' then nothing is retrieved from database. I debugged the code but not getting the reason for this. following is database image.

  -(void)fetchfromDatabase
{
      sqlite3 *database;

NSString *strPath = [[NSBundle mainBundle]pathForResource:@"ken" ofType:@"db"];
NSLog(@"DBPath : %@",strPath);

const char *dbPath = [strPath UTF8String];

if(sqlite3_open(dbPath, &database)==SQLITE_OK)
{
    NSLog(@"Database is Ok");

   // const char *cConcate = [concate UTF8String];
    NSString *query = [NSString stringWithFormat:@"select * from kentable where combination =%@",concate];
    NSLog(@"final concate in database:%@",concate);
    NSLog(@"Query:%@",query);
    const char *sqlQuery =[query cStringUsingEncoding:NSASCIIStringEncoding];
    NSLog(@"char query %s", sqlQuery);
    sqlite3_stmt *queryStatement;

    if(sqlite3_prepare_v2(database, sqlQuery,-1,&queryStatement, NULL)==SQLITE_OK)
    {
        NSLog(@"Conversion is Ok");


        while (sqlite3_step(queryStatement)==SQLITE_ROW) 
        {
            NSLog(@"in While Statement of row");
            int flag=sqlite3_column_int(queryStatement, 1);

            NSLog(@"question 5:%d",flag);
        }
        sqlite3_reset(queryStatement);
    }
    else 
    {
        NSLog(@"ERROR");
    }

    sqlite3_close(database);

}

}

Answer 1

Try changing code as following. Don't mind but preparing sql query as you did is bad practice better follow standard practice. You can bind string using sqlite3_bind_text function.

if(sqlite3_open(dbPath, &database)==SQLITE_OK)
{
    const char *sqlQuery = "select * from kentable where combination = ?";
    sqlite3_stmt *queryStatement;

    if(sqlite3_prepare_v2(database, sqlQuery,-1,&queryStatement, NULL)==SQLITE_OK)
    {
        NSLog(@"Conversion is Ok");
        sqlite3_bind_text(insertStmt,1, [concate UTF8String], -1, SQLITE_TRANSIENT);    

        while (sqlite3_step(queryStatement)==SQLITE_ROW) 
        {
            NSLog(@"in While Statement of row");
            int flag=sqlite3_column_int(queryStatement, 1);

            NSLog(@"question 5:%d",flag);
        }
        sqlite3_reset(queryStatement);
    }
    else 
    {
        NSLog(@"ERROR");
    }
    sqlite3_close(database);
}

Answer 2

I am also having this kind of tasks.. it's works well...Just change your code by

NSString * data=[self status:combination];

    -(NSString *) status:(NSString *)concate
    {
    databasePath = [[NSString alloc] initWithString: [[NSBundle mainBundle] pathForResource:@"ken" ofType:@"db"]];
        sqlite3_stmt    *statement;
        NSString *aName;
        const char *dbpath = [databasePath UTF8String];

        if (sqlite3_open(dbpath, &database) == SQLITE_OK)
        {
            NSString *querySQL = [NSString stringWithFormat: 
                                  @"SELECT combination, flag FROM details WHERE combination =\"%@\"", 
                                  concate];

            const char *query_stmt = [querySQL UTF8String];
            sqlite3_prepare_v2(database, 
                               query_stmt, -1, &statement, NULL);
            if (sqlite3_step(statement) == SQLITE_ROW)
            {
                // Read the data from the result row
                aName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(statement, 1)];

                NSLog(@"same %@" ,aName) ;

            }
            else
            {
                aName=nil;
            }

    sqlite3_finalize(statement);
            sqlite3_close(database);

        }


 [databasePath release];
    return  aName;
}