iphone SQLite Select Query doesn't work

Question

Hi, In the code below, I am connecting to an SQLite Database, the SELECT query didn't work.

I hope you can help me.

Thanks

if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK) {
 // Setup the SQL Statement and compile it for faster access
 const char *sqlStatement = "select name,score from game Where name='interclock'";
 sqlite3_stmt *compiledStatement;
 if(sqlite3_prepare_v2(database, sqlStatement, -1, &compiledStatement, NULL) == SQLITE_OK) {



  // Loop through the results and add them to the feeds array
  //while(sqlite3_step(compiledStatement) == SQLITE_ROW) {
  if(sqlite3_step(compiledStatement) == SQLITE_ROW) {
   // Read the data from the result row

   NSString *aName =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
   NSString *aScore =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];

   UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Sonuç" message:[NSString stringWithFormat:@"Oyun adı %s Skor:%s",aName,aScore] 
                 delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];
   [alert show]; 

   //NSString *aName = [NSString stringWithString:(NSString *)sqlite3_column_text(compiledStatement, 2)];
   //NSString *aScore = [NSString stringWithString:(NSString *)sqlite3_column_text(compiledStatement, 3)];



   // Create a new animal object with the data from the database
   DatabaseClass *dbOBJ = [[DatabaseClass alloc] initWithName:aName score:aScore];

   // Add the animal object to the animals Array
   [scores addObject:dbOBJ];

   [dbOBJ release];
  } else {
   UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Error" message:@"SQL Query Dont Work" 
                 delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];
   [alert show];
  }
 }
 // Release the compiled statement from memory
 sqlite3_finalize(compiledStatement);

} else {
 UIAlertView *alert = [[UIAlertView alloc] initWithTitle:@"Error" message:@"No Connection" 
               delegate:self cancelButtonTitle:@"OK" otherButtonTitles: nil];
 [alert show];
}

Answer 1

This is incorrect

 NSString *aName =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
 NSString *aScore =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];

must be

 NSString *aName =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
 NSString *aScore =[NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];

Answer 2

Use this ccode... can indeed help you...

// Creates a writable copy of the bundled default database in the application Documents directory.
- (void)createEditableCopyOfDatabaseIfNeeded {
    // First, test for existence.
    BOOL success;
    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"DBNAME.sqlite"];
    success = [fileManager fileExistsAtPath:writableDBPath];
    if (success) return;
    // The writable database does not exist, so copy the default to the appropriate location.
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"DBNAME.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
    if (!success) {
        NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
    }
}

// Open the database connection and retrieve minimal information for all objects.
- (void)initializeDatabase {    

    // The database is stored in the application bundle. 
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"DBNAME.sqlite"];

    // Open the database. The database was prepared outside the application.
    if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) 
    {      
        //TRUE
    } 
    else 
    {
        // Even though the open failed, call close to properly clean up resources.
        sqlite3_close(database);
        NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
        // Additional error handling, as appropriate...
    }
}


//Method for Datbase
-(BOOL)UpdateData:(NSMutableDictionary*)objDic :(NSString*)PrimaryKey :(NSString*)TABLE_NAME
{
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];

    NSString* SQLColumns=@"";   
    NSString* SQLValues=@"";
    NSString* SQL=@"";

    //Chekc Wheather Insert or update?
    BOOL IsNew=NO;;
    if([[objDic valueForKey:PrimaryKey] intValue]==0)
    {
        IsNew=YES;
    }

    NSArray* Keys=[objDic allKeys];

    if(IsNew)
    {
        for(int i=0;i<Keys.count;i++)
        {
            if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
            {
                SQLColumns=[NSString stringWithFormat:@"%@%@,",SQLColumns,[Keys objectAtIndex:i]];
                SQLValues=[NSString stringWithFormat:@"%@?,",SQLValues];
            }
        }

        if([SQLColumns length]>0)
        {
            SQLColumns=[SQLColumns substringToIndex:[SQLColumns length]-1];
            SQLValues=[SQLValues substringToIndex:[SQLValues length]-1];
        }

        SQL=[NSString stringWithFormat:@"INSERT INTO %@ (%@) Values(%@)",TABLE_NAME,SQLColumns,SQLValues];
    }
    else
    {
        for(int i=0;i<Keys.count;i++)
        {
            if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
            {
                SQLColumns=[NSString stringWithFormat:@"%@%@=?,",SQLColumns,[Keys objectAtIndex:i]];
            }
        }

        if([SQLColumns length]>0)
        {
            SQLColumns=[SQLColumns substringToIndex:[SQLColumns length]-1];
        }

        SQL=[NSString stringWithFormat:@"UPDATE %@ SET %@ WHERE %@=?",TABLE_NAME,SQLColumns,PrimaryKey];
    }

    sqlite3_stmt *insert_statement=nil; 

    if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &insert_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }                

    int intBindIndex=1;
    for(int i=0;i<Keys.count;i++)
    {
        if(![[Keys objectAtIndex:i] isEqual:PrimaryKey])
        {
            sqlite3_bind_text(insert_statement,intBindIndex,[[objDic valueForKey:[Keys objectAtIndex:i]] UTF8String],-1, SQLITE_STATIC);
            intBindIndex++;
        }
    }

    if(!IsNew)
    {
        sqlite3_bind_text(insert_statement,Keys.count,[[objDic valueForKey:PrimaryKey] UTF8String],-1, SQLITE_STATIC);
    }

    int result;
    result=sqlite3_step(insert_statement);

    if(IsNew)
    {
        [objDic setObject:[NSString stringWithFormat:@"%d",sqlite3_last_insert_rowid(database)] forKey:PrimaryKey];     
    }

    sqlite3_finalize(insert_statement); 

    [pool release];

    if(result==SQLITE_DONE)
        return YES;
    else    
        return NO;
}

-(NSMutableArray*)GetList:(NSString*)TABLE_NAME
{   
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];

    NSMutableArray* Array;

    Array=[[NSMutableArray alloc]init];

    sqlite3_stmt *select_statement=nil;

    NSString *sql =[NSString stringWithFormat:@"select * from %@",TABLE_NAME];
    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }

    int columncount=sqlite3_column_count(select_statement);

    NSMutableDictionary* dic;

    while (sqlite3_step(select_statement) == SQLITE_ROW) 
    {   
        dic=[[NSMutableDictionary alloc]init];

        for(int j=0;j<columncount;j++)
        {
            [dic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
        }

        [Array addObject:dic];
        [dic release];      
    }

    sqlite3_finalize(select_statement);

    [Array retain];

    [pool release];

    return Array;
}


-(BOOL)DeleteDetail:(NSString*)TABLE_NAME :(NSString*)PrimaryKey :(NSString*)Value
{
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];

    sqlite3_stmt *select_statement=nil;

    NSString *sql =[NSString stringWithFormat:@"Delete from %@ where %@=?",TABLE_NAME,PrimaryKey];
    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }

    sqlite3_bind_text(select_statement,1,[Value UTF8String],-1, SQLITE_STATIC);

    int result;

    result=sqlite3_step(select_statement);

    sqlite3_finalize(select_statement); 

    [pool release];

    if(result==SQLITE_DONE)
        return YES;
    else    
        return NO;
}

-(NSMutableDictionary*)GetDetails:(NSString*)TABLE_NAME :(NSString*)PrimaryKey :(NSString*)Value
{
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];

    NSMutableDictionary* dicInfo=[[NSMutableDictionary alloc]init];

    sqlite3_stmt *select_statement=nil;

    NSString *sql =[NSString stringWithFormat:@"select * from %@ where %@=?",TABLE_NAME,PrimaryKey];
    if (sqlite3_prepare_v2(database, [sql UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }

    sqlite3_bind_text(select_statement,1,[Value UTF8String],-1, SQLITE_STATIC);

    int columncount=sqlite3_column_count(select_statement);

    if (sqlite3_step(select_statement) == SQLITE_ROW) 
    {   
        for(int j=0;j<columncount;j++)
        {
            [dicInfo setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
        }       
    }

    sqlite3_finalize(select_statement);

    [dicInfo retain];

    [pool release];

    return dicInfo;
}

-(NSMutableArray*)GetDetailsBySQL:(NSString*)SQL
{
    NSAutoreleasePool* pool=[[NSAutoreleasePool alloc]init];

    NSMutableArray* Array;

    Array=[[NSMutableArray alloc]init];



    sqlite3_stmt *select_statement=nil;

    if (sqlite3_prepare_v2(database, [SQL UTF8String], -1, &select_statement, NULL) != SQLITE_OK) {
        NSAssert1(0, @"Error: failed to prepare statement with message '%s'.", sqlite3_errmsg(database));
    }

    int columncount=sqlite3_column_count(select_statement);

    NSMutableDictionary* dic;

    while (sqlite3_step(select_statement) == SQLITE_ROW) 
    {   
        dic=[[NSMutableDictionary alloc]init];

        for(int j=0;j<columncount;j++)
        {
            //NSLog(sqlite3_column_value(select_statement,j));
            if(sqlite3_column_text(select_statement, j)!=nil)
                [dic setObject:[NSString stringWithUTF8String:(char *)sqlite3_column_text(select_statement, j)] forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];
            else
                [dic setObject:@"" forKey:[NSString stringWithUTF8String:(char *)sqlite3_column_name(select_statement,j)]];     
        }

        [Array addObject:dic];
        [dic release];      
    }

    sqlite3_finalize(select_statement);

    [Array retain];

    [pool release];

    return Array;
}

Thanx