unable to open database

Question

I am using below code for inserting data in the database. and i am inserting aprox 15000 records but after 245 records it throws the error "Unable to open database"

 +(void)addGeoRegions:(const char *)query geoId:(int)geoId geoFatherId:(int)geoFatherId geoName:(NSString *)geoName 
      geoTypeRegionId:(NSString *)geoTypeRegionId geoZone:(int)geoZone
    {
    sqlite3_stmt *dataRows = nil;
     @try {



     if(sqlite3_open([[self getDBPath] UTF8String],&PatientDatabase) == SQLITE_OK)
     {

      if (sqlite3_prepare_v2(PatientDatabase, query, -1, &dataRows, NULL)!=SQLITE_OK) 
      {
       NSAssert1(0,@"error while preparing  %s",sqlite3_errmsg(PatientDatabase));
      }

      sqlite3_bind_int(dataRows, 1, geoId);
      sqlite3_bind_int(dataRows, 2, geoFatherId);
      sqlite3_bind_text(dataRows, 3, [geoName UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_text(dataRows, 4, [geoTypeRegionId UTF8String], -1, SQLITE_TRANSIENT);
      sqlite3_bind_int(dataRows, 5, geoZone);

      if (SQLITE_DONE!=sqlite3_step(dataRows))
      {
       char *err;
       err=(char *) sqlite3_errmsg(PatientDatabase);
       if (err)
        sqlite3_free(err);
       NSAssert1(0,@"error while inserting geo regions. %s",sqlite3_errmsg(PatientDatabase)); 

      }


     }

     }
     @catch (NSException * e) {

     }
     @finally 
     {
      sqlite3_close(PatientDatabase);
      sqlite3_finalize(dataRows);
      PatientDatabase=nil;
     }

    }

so please can any one suggest why this problem is occur.

Answer 1

Firstly, think about Mark's answer, you'll get better performance if you open the database once and close it once.

Anyway, that was a suggestion for a design improvement. What is actually wrong in your code is the finally block:

 @finally 
 {
  sqlite3_close(PatientDatabase);  // will fail!
  sqlite3_finalize(dataRows);
  PatientDatabase=nil;
 }

Here is the relevant line from the sqlite3_close() docs.

Applications must finalize all prepared statements and close all BLOB handles associated with the sqlite3 object prior to attempting to close the object. If sqlite3_close() is called on a database connection that still has outstanding prepared statements or BLOB handles, then it returns SQLITE_BUSY.

You need to run the finalize before closing the database. As things stand, the close call fails and you end up with 245 open database handles.

So, reverse the order of the two statements and check your return codes for failure.

By the way, NSAssert is not an appropriate way to report errors. Throw an exception or return an error, or just log it. NSAssert is designed to catch programming errors. It won't even be compiled into your release code.

Answer 2

You are opening the database on each call it would take less resource to open it once then add all the rows before closing it. In theory what you are doing should work but it is not a way I would even start using.

Answer 3

Sqlite has much better performance "in transation" on inserts without transaction. I particularly, massive use transaction processes, or failure comes randomly at some point with error "unable to open database file"