Faster way of inserting bunch of records into SQLite database on the fly using Objective-C-iPhone/iPad

Hi Guys,i am back here with one more helful information regarding transaction with SQLite.

Before i start talking about faster way of inserting records into the database,lets see the basics first.

1) Copying your SQLite database from the bundle(in case you have it in your bundle)  to DOCUMENT/CACHE directory is important,before playing with it.

NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);

    NSString *documentsDirectory = [paths objectAtIndex:0];

    NSString *my_DatabasePath = [documentsDirectory stringByAppendingPathComponent:@"URDatabase.sqlite"];

my_DatabasePath will get you the path of the database.Now lets check whether the database with my_DatabasePath is already present in your document directory,if not lets copy it.

The below method will check out whether your database is present in the directoryif not then it will copy from bundle and place it in the directory.

- (void) checkAndCreateDatabase:(NSString *)databasePath {

    NSError * error;

    //Check if the database has been saved to the users phone, if not then copy it over

    BOOL Success;

    //Create a file manager object, we will use this to check the status

    //of the databse and to copy it over if required

    NSFileManager *fileManager = [NSFileManager defaultManager];

    //Check if the database has already been created in the users filesystem

    Success = [fileManager fileExistsAtPath:databasePath];

    //If the database already exists then return without doing anything

    if(Success)

        return;

    //If not then proceed to copy the database from the application to the users filesystem

    //Get the path to the database in the application package

    NSString *databasePathFromApp = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:m_DatabaseName];

    //Copy the database from the package to the usrrs filesystem

    [fileManager copyItemAtPath:databasePathFromApp toPath:databasePath error:&error];

}

This is all about the basics.

2) Now lets move on to the topic what i mentioned as the title of article.

There exist many ways to insert data into your database,have anyone asked the following question with themselves?

(i) how much they are effective for the mobile devices like iPhone?

(ii) how long it will take to insert?

(iii) doest it block the main thead?

Ofcourse the answer is they are not effective,it will take so much time to insert the bunch of records into the sqlite database and YES,it does block the main thread until its done with the transaction.

So lets not worry about it,i have overcome from this issue with some R&D.And below is the result of it.

Have a look at the below code of inserting records into the sqlite database.

arrayOfRecords ==>Say i have huge records thats been stored in one global array.

myTable==> table name of my sqlite database.

databasePathVDB==> path of the database.

recordno,record1==>table field name.

-(void)insertAllRecordsIntoDB:(NSString *) databasePathVDB 

{ 

char* errorMessage; 

sqlite3_stmt* stmt = NULL; 

sqlite3 *mDb; 

if(sqlite3_open([databasePathVDB UTF8String], &mDb) == SQLITE_OK) 

{ 

sqlite3_exec(mDb, "BEGIN THE TRANSACTION", NULL, NULL, &errorMessage); 

char buffer[] = "INSERT/REPLACE INTO myTable(recordno, record1) Values(?,?)"; 

sqlite3_prepare_v2(mDb, buffer, strlen(buffer), &stmt, NULL); 

for (unsigned i = 0; i < arrayOfRecords.count; i++) { 

//std::string id = getID(); 

sqlite3_bind_int(stmt, 0,i); 

sqlite3_bind_text(stmt, 1, [[arrayOfRecords objectAtIndex:i]UTF8String] ,-1, SQLITE_STATIC); 

if (sqlite3_step(stmt) != SQLITE_DONE) 

{ 

NSLog(@"Commit Failed!\n"); 

} 

sqlite3_reset(stmt); 

} 

} 

sqlite3_exec(mDb, "COMMIT TRANSACTION", NULL, NULL, &errorMessage); 

sqlite3_finalize(stmt); 

sqlite3_close(mDb); 

}

That's it.The above code looks more sort of a normal C-code :-).If you still feel sheepy for using this method,then compare the other general methods with the above effective method.

Happy coding... :-)