SQLite. Can't add more than 1000 rows

Question

I'm trying to add to my SQLite database (with fmdb) 10k rows but writing is stopped on 1000 row. And I have no any errors or warnings. My code:

NSString *queryString = [NSString stringWithFormat:@"insert into histories (storyid, text, date, storyurl) values (%li, ?, ?, ?)",history.storyIndex];
if ([self.db open]) {
    if([self.db executeUpdate:queryString, history.storyText, history.storyDate, history.storyUrl]) {
        NSLog(@"history added");
    } 
}

Any suggestions?

Answer 1

Sounds like you need to commit your transactions. It appears that you have reached some limit on the amount of rows in a single transaction.

Answer 2

Can you try the following code and see if there is any change:

NSString *queryString = @"INSERT INTO histories (storyid, text, date, storyurl) 
VALUES ( ?, ?, ?, ?)";

BOOL executeUpdateStatus = NO;

if ([self.db open]) 
{
    executeUpdateStatus = [self.db executeUpdate:queryString, 
    [NSNumber numberWithLong:history.storyIndex], history.storyText,
    history.storyDate, history.storyUrl];


        if(executeUpdateStatus == YES)   
            NSLog(@"history added");
        else
            NSLog(@"history NOT added");
}

Answer 3

Put the database open outside of the loop, so that it is called just the once. Calling it repeatedly inside the loop of 10,000 rows might be causing memory problems.

Check for lastErrorCode on the db object after each executeUpdate and NSLog the lastErrorMessage if it is non zero.

Commit at intervals by incrementing a counter and using modulus arithmetic, e.g.

counter++;
if (mod(counter,500) ){
    [self.db commit];
}

Consider using python to bulk load the database outside of the iOS project.

Answer 4

For anyone with the same problem... make sure your sqlite viewer is not limiting the output of your query to 1000 results.

Just lost a few minutes with the same problem and the answer was that simple!