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?

... with a mixture of printf-formatting and bind variables?!? — trojanfoe, Oct 29 '12 at 09:13
@H2CO3 there are many examples with lowercase SQL query. Has it bad effect for performance? — RomanHouse, Oct 29 '12 at 09:48
@RomanHouse don't worry, it's fine. You just don't have enough experience with stuff yet to understand the punchline :) — , Oct 29 '12 at 09:49
@H2CO3 lets dont delay this :) I will be glad if you give me some reason to use uppercase queries :) — RomanHouse, Oct 29 '12 at 09:55
@RomanHouse It's just idiomatic. Lowercase is also fine, and there's no difference, but people tend to use SQL queries in all-caps. (Yeah, this pretty much indicates that database engineers are noobs. :P) — , Oct 29 '12 at 09:57
@H2CO3 ok, I will be use uppercase queries and become professional at once :D — RomanHouse, Oct 29 '12 at 10:02

score 5 · Answer 1 · answered Jul 12 '13 at 11:30

5

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!

answered Jul 12 '13 at 11:30

Joao Baltazar

566
9
14

You saved my day. Thank you. SQLite studio was in fact showing only 1000 records. Confirmed it with a query and then removed the limitation. – Ricardo Araújo Oct 16 '22 at 11:42

score 1 · Answer 2 · answered Oct 29 '12 at 10:58

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.

score 1 · Accepted Answer · answered Oct 29 '12 at 11:03

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");
}

You should only open your database only once, and don't forget to use `[self.db commit];` — Ravi Raman, Oct 29 '12 at 12:21

score 0 · Answer 4 · answered Oct 29 '12 at 09:13

0

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.

answered Oct 29 '12 at 09:13

Burhan Khalid

169,990
18
245
284

I already trying to commit each of transactions but it isn't help – RomanHouse Oct 29 '12 at 09:49

SQLite. Can't add more than 1000 rows

4 Answers4

Linked