CODEWITHSUNDEEP
iphoneiosdatabasesqlite
Rahul
Rahul

Reputation: 509

Sql Query Giving NULL as Result (Expecting Text)

-(NSMutableArray*) fetchMakeNamesYear1:(int) year1 Year2:(int) year2 compileStaement:    (sqlite3_stmt*)compiledStatement
{
    NSMutableArray *makeNames=[NSMutableArray array];
    NSString *selectQuery=[NSString stringWithFormat:@"SELECT DISTINCT Make FROM Inventory  WHERE   [Year] BETWEEN 2009 AND 2012 AND Make IS NOT NULL ORDER BY Make",year1 ,year2 ];
//   if (sqlite3_exec(((StorageManager*)[StorageManager sharedStorageManager]).database,  [selectQuery cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL) == SQLITE_OK) 
//   {
//       
//   }
    if(sqlite3_prepare_v2(((StorageManager*)[StorageManager sharedStorageManager]).database,  [selectQuery UTF8String] , -1, &compiledStatement, NULL) != SQLITE_OK) 
    {
        return nil;

    }
    while (sqlite3_step(compiledStatement) == SQLITE_ROW)
   {

        NSLog(@"return TYpe: %d",sqlite3_column_type(compiledStatement, 5));

        if (sqlite3_column_text(compiledStatement, 5)!= NULL)
        { 
            **NSString *makeName = [NSString  stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 6)];**
            [makeNames addObject:makeName];

        } 
       sqlite3_reset(compiledStatement);   
}
[self finalize:compiledStatement];

return  makeNames  ;

}

The value returned by the sqlite3_column_text(compiledStatement, 5) is NULL.

In my database the data type of the column no 5 is nvarchar[50].

Its column name is "Make"

My table name is "Inventory"

In above example I have hardcoded value for to and from year.

I tried the same sql query in the sqlite manager , and I found the data is showing for all make names. I also found that the sqlite3_column_type(compileStatement,5) retuns 5 (SQLITE_NULL 5) but according to my Column type it should be 3 (SQLITE_TEXT 3)

Can someone give an insight of above behavior ?

Upvotes: 0

Views: 884

Answers (3)

Nuzhat Zari
Nuzhat Zari

Reputation: 3408

In your select statement you are selecting only one column that is Make, so column index in sqlite3_column_text(compiledStatement, 0) should be 0 not 5.It is not the index of column(Make) in database but index of column you gave in select statement.

Upvotes: 0

parag
parag

Reputation: 657

-(NSMutableArray*) fetchMakeNamesYear1:(int) year1 Year2:(int) year2 compileStaement:    (sqlite3_stmt*)compiledStatement
{
NSMutableArray *makeNames=[NSMutableArray array];
NSString *selectQuery=[NSString stringWithFormat:@"SELECT DISTINCT Make FROM Inventory  WHERE   [Year] BETWEEN 2009 AND 2012 AND Make IS NOT NULL ORDER BY Make",year1 ,year2 ];
//   if (sqlite3_exec(((StorageManager*)[StorageManager sharedStorageManager]).database,  [selectQuery cStringUsingEncoding:NSUTF8StringEncoding], NULL, NULL, NULL) == SQLITE_OK) 
//   {
//       
//   }
if(sqlite3_prepare_v2(((StorageManager*)[StorageManager sharedStorageManager]).database,  [selectQuery UTF8String] , -1, &compiledStatement, NULL) != SQLITE_OK) 
{
    return nil;

}
while (sqlite3_step(compiledStatement) == SQLITE_ROW)
{

    NSLog(@"return TYpe: %d",sqlite3_column_type(compiledStatement, 0));

    if (sqlite3_column_text(compiledStatement, 0)!= NULL)
    { 
        **NSString *makeName = [NSString  stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];**
        [makeNames addObject:makeName];

    } 
   sqlite3_reset(compiledStatement);   
}
[self finalize:compiledStatement];

return  makeNames  ;

}

Upvotes: 1

Frank Schmitt
Frank Schmitt

Reputation: 30765

You are using the wrong column index in sqlite3_column_text() - column 5 doesn't exist in your query, try 0 instead (see SQLite documentation )

Upvotes: 1

Related Questions