retrieve data from SQLite with multiple records

Question

was wondering if anyone could share how to retrieve data from SQLite with multiple records, i.e I have a table with 5 fields each has multiple data.

NSString *str = [[NSString alloc] initWithFormat:@"%@ %@ etc...", oneString,two...];

[entry addObject:str];

I get only the first record

One more thing the below string works

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM abc"];

but this one doesn't, I get a crash!

NSString *sql = [NSString stringWithFormat:@"SELECT * FROM abc WHERE name= \"john\""];

Gaurav Rastogi · Accepted Answer · 2013-05-01 17:16:25Z

-(NSMutableArray *)readInformationFromDatabase
{
NSMutableArray *array = [[NSMutableArray alloc] init];

// Setup the database object
sqlite3 *database;

// Open the database from the users filessytem
if(sqlite3_open([databasePath UTF8String], &database) == SQLITE_OK)
{
    // Setup the SQL Statement and compile it for faster access

    //SQLIte Statement 
    NSString *sqlStatement_userInfo =[NSString stringWithFormat:@"Select * from TableName"];

    sqlite3_stmt *compiledStatement;


    if(sqlite3_prepare_v2(database, [sqlStatement_userInfo UTF8String], -1, &compiledStatement, NULL) == SQLITE_OK)
    {

        // Loop through the results and add them to the feeds array
        while(sqlite3_step(compiledStatement) == SQLITE_ROW)
        {
            // Init the Data Dictionary 
            NSMutableDictionary *_dataDictionary=[[NSMutableDictionary alloc] init];

            NSString *_userName = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 0)];
           // NSLog(@"_userName = %@",_userName);

            NSString *_emailID = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 2)];
           // NSLog(@"_emailID = %@",_emailID);

            NSString *_contactNumber = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 1)];
           // NSLog(@"_contactNumber = %@",_contactNumber);

            NSString *_address = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 3)];
           // NSLog(@"_address = %@",_address);

            NSString *_zipCode = [NSString stringWithUTF8String:(char *)sqlite3_column_text(compiledStatement, 4)];
           // NSLog(@"_zipCode = %@",_zipCode);

            [_dataDictionary setObject:[NSString stringWithFormat:@"%@",_userName] forKey:@"UserName"];
            [_dataDictionary setObject:[NSString stringWithFormat:@"%@",_emailID] forKey:@"EmailId"];
            [_dataDictionary setObject:[NSString stringWithFormat:@"%@",_contactNumber] forKey:@"ContactNumber"];
            [_dataDictionary setObject:[NSString stringWithFormat:@"%@",_address] forKey:@"Address"];
            [_dataDictionary setObject:[NSString stringWithFormat:@"%@",_zipCode] forKey:@"ZIPCode"];

            [array addObject:_dataDictionary]; 
        }
    }
    else
    {
        NSLog(@"No Data Found");
    }

    // Release the compiled statement from memory
    sqlite3_finalize(compiledStatement);
}

sqlite3_close(database);

return array;
}

Nishant Tyagi · Accepted Answer · 2013-05-01 18:26:42Z

You can use this as below :

/================================================================
METHOD FOR Fetching Data FROM DATABASE ==================================================================/

-(NSMutableArray *)getData:(NSString *)query
{


//NSLog(@"QUERY : %@",query);

NSString *idToReturn=@"";
NSMutableArray *returnArray = [NSMutableArray new];
if(sqlite3_open([[self dataFilePath] UTF8String], &database) == SQLITE_OK)
{
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(database, [query UTF8String], -1, &statement, nil)==SQLITE_OK)
    {
        while(sqlite3_step(statement)==SQLITE_ROW)
        {
            NSMutableDictionary *temp= [NSMutableDictionary new];
            const char *s;



            s=(char *)sqlite3_column_text(statement, 0); // Here 0 is the first column index 
            if(s==NULL)
            {
                idToReturn=@"";
            }
            else
            {
                idToReturn =[NSString stringWithUTF8String:s];
            }
            [temp setObject:idToReturn forKey:@"firstData"];

            s=(char *)sqlite3_column_text(statement, 1);
            if(s==NULL)
            {
                idToReturn=@"";
            }
            else
            {
                idToReturn =[NSString stringWithUTF8String:s];
            }
            [temp setObject:idToReturn forKey:@"secondData"];

            s=(char *)sqlite3_column_text(statement, 2);
            if(s==NULL)
            {
                idToReturn=@"";
            }
            else
            {
                idToReturn =[NSString stringWithUTF8String:s];
            }
            [temp setObject:idToReturn forKey:@"thirdData"];

            s=(char *)sqlite3_column_text(statement, 3);
            if(s==NULL)
            {
                idToReturn=@"";
            }
            else
            {
                idToReturn =[NSString stringWithUTF8String:s];
            }
            [temp setObject:idToReturn forKey:@"forthData"];

            s=(char *)sqlite3_column_text(statement, 5);
            if(s==NULL)
            {
                idToReturn=@"";
            }
            else
            {
                idToReturn =[NSString stringWithUTF8String:s];
            }
            [temp setObject:idToReturn forKey:@"fifthData"];

            if (temp != nil)
            {
                [returnArray addObject:temp];

                temp = nil;
            }

        }
        sqlite3_finalize(statement);
        sqlite3_close(database);
    }
}
return returnArray;

}

Then call this as :

NSString *query  = [NSString stringWithFormat:@"select * from tablename"];
NSMutableArray *data = [self getData:query];

Hope it helps you.

Thanks, but how could I know which record am I at the moment? also if I have 100 records how can I seek to that specific row. would anyone know whats wrong with the below sqlite command? NSString *sql = [NSString stringWithFormat:@"SELECT * FROM abc WHERE name= \"john\""] it crashes when I execute it. thanks again for the help.
Hi Nishant, not sure if the forum allows to contact you privately? if yes I appropriate your email, I would like to ask and offer. thanks.

Collectives™ on Stack Overflow

retrieve data from SQLite with multiple records

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related