CODEWITHSUNDEEP
iossqlite
Rashid
Rashid

Reputation: 804

Batch insert values to a specific column in sqlite3 on iOS

I have a table in my database. I want to add values to a specific column in the table. Since there are like thousands plus rows in my table i figured i have to use batch update. The code is this but i get syntax error and a message saying there is no such row:

NSString *addProductColQuery = @"ALTER TABLE Sale ADD COLUMN Product text";
[self.dbManager loadDataFromDB:addProductColQuery];

NSString *batchstart = @"BEGIN IMMEDIATE TRANSACTION";
[self.dbManager loadDataFromDB:batchstart];
for (int i = 0; i<self.productInfo.count; i++)
{
    NSString *addValue = [NSString stringWithFormat:@"INSERT INTO Sale (Product) VALUES (%@)",[tempProductArray objectAtIndex:i]];
     [self.dbManager loadDataFromDB:addValue];
}
NSString *batchComit = @"COMMIT TRANSACTION";
[self.dbManager loadDataFromDB:batchComit];

Update: I have managed to get the above code to work but now i am getting 90% CPU Usage on iphone6! I actually saw this coming since im using a for loop which is dead wrong to loop a query. Is there a way to batch insert the values to the rows in a specific column?

Upvotes: 0

Views: 86

Answers (1)

Rob
Rob

Reputation: 437882

Are the values in tempProductArray text values? If so, you have to quote them in your SQL:

NSString *addValue = [NSString stringWithFormat:@"INSERT INTO Sale (Product) VALUES ('%@')", tempProductArray[i]];

Having shown you a quick solution, that's actually the wrong way to do it. It is inadvisable to use stringWithFormat to build SQL with text values. If any of those strings themselves have apostrophe characters in them, the SQL will fail. Also, if any of this data was provided by the end-user or from the network, you're exposed to SQL injection attacks.

The correct solution is to use sqlite3_prepare_v2 of the SQL without quotes, but with ? placeholder:

NSString *addValue = @"INSERT INTO Sale (Product) VALUES (?)";

And then, before calling sqlite3_step, you would call sqlite3_bind_text to bind a text value to the ? placeholder.

If your dbManager doesn't offer that capability, you'll have to add it. Or use a library, like FMDB, that offers this capability out of the box.

Upvotes: 1

Related Questions