Fetch data from sqlite in parts

Question

I have 10000+ of data in SQLite database. I am trying to fetch data in parts like I am trying to fetch first 100 records of data and store in an array and than try to fetch next 100 records of data but I am not able to make the query like that which can fetch first 100 records than fetch next 100 records and so on.. Here is my code of fetchAllData

- (void)getAllData {
    NSString * convertInttoStr = [NSString stringWithFormat:@"%d", rowNumber];
    // Getting the database path.
    NSArray  *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *docsPath = [paths objectAtIndex:0];
    NSString *dbPath = [docsPath stringByAppendingPathComponent:@"iandroidquran_database 3.sqlite"];

    FMDatabase *database = [FMDatabase databaseWithPath:dbPath];
    [database open];
    NSString *sqlSelectQuery = [NSString stringWithFormat:
                                @"SELECT * FROM qSurahContent WHERE surahID=%@ LIMIT 100 " ,
                                convertInttoStr];


    // Query result
    FMResultSet *resultsWithNameLocation = [database executeQuery:sqlSelectQuery];
    while([resultsWithNameLocation next]) {
        NSString *strID = [NSString stringWithFormat:@"%d",[resultsWithNameLocation intForColumn:@"surahID"]]

[surahContentArabic addObject:strName];

    }
    if (rowNumber == 1) {
        [surahContentArabic removeObjectAtIndex:0];
    }
    [self.tblView reloadData];

    [database close];
}

and Here is the method on which populate it in a tableview.

-(UITableViewCell *) tableView:(UITableView *)tableView cellForRowAtIndexPath:(NSIndexPath *)indexPath
{
    NSString * cellIdentifier = @"Cell";
    NSString * cellIdentifierImage = @"ImageCell";
    if(indexPath.row == 0)
    {
        CustomTableViewCell * cell = [self.tblView dequeueReusableCellWithIdentifier:cellIdentifierImage];
        cell.imageView.image = [UIImage imageNamed:@""];
        return cell;
    }
    else
    {
    CustomTableViewCell * cell = [self.tblView dequeueReusableCellWithIdentifier:cellIdentifier];
       cell.surahContenArab.text = [surahContentArabic objectAtIndex:(indexPath.row-1)];
        cell.ayahLbl.text = [ayahId objectAtIndex:(indexPath.row-1)];

        return cell;
    }


}

-(void)tableView:(UITableView *)tableView willDisplayCell:(UITableViewCell *) cell forRowAtIndexPath:(NSIndexPath *)indexPath
{
    if(indexPath.row + 100 > [self.surahContentArabic count])
    {
        [self getAllData];
    }
}

Please tell me the solution how I can fetch data in parts . Thanks

Please See This Link :- http://www.tutorialspoint.com/sqlite/sqlite_limit_clause.htm — Rajesh, May 02 '16 at 12:29

score 0 · Accepted Answer · answered May 02 '16 at 14:04

0

If you want to select records 100-200 (inclusive) you have to use the LIMIT OFFSET clause.

SELECT * FROM qSurahContent WHERE surahID=%@ LIMIT 100 OFFSET 100

The above query returns 100 records, start from record 101 (OFFSET 100).

You can change OFFSET value 200 for 3rd set of records.

Hope this helps.

answered May 02 '16 at 14:04

Surya Subenthiran

2,217
1
15
22

Yes I am trying that but it if I fetched all the records than the empty cell appears at the end because I am trying to fetch the record in this method -(void)tableView:(UITableView *)tableView willDisplayCell:(UITableViewCell *) cell forRowAtIndexPath:(NSIndexPath *)indexPath { } – Muhammad Salman May 02 '16 at 14:28
Refer the link http://stackoverflow.com/questions/22528800/ios-how-to-make-the-tableview-use-paginated-api-output – Surya Subenthiran May 02 '16 at 14:36

Fetch data from sqlite in parts

1 Answers1