Xcode Sqlite persistence problem

Question

Hi, I have a persistence problem in my application. I'm using sqlite database. when some insert queries executed results temporary added to the database. After restarting application the new values vanish! I think new values stored on RAM do not save on hard-disk.

    -(IBAction)add:(id)sender
{

NSString *myDB;
NSString *query;
myDB=[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Ssozluk.sql"];
database =[[Sqlite alloc] init];
[database open:myDB];
query=[NSString stringWithFormat:@"INSERT INTO words(col_1,col_2) VALUES('asd','asd2');"];

[database executeNonQuery:query];
[database commit];
[database close];

}

---

 -(IBAction)show:(id)sender
    {
    NSString *myDB;
    NSString *query;
    NSArray *asdasd;
    NSString *asd;
    myDB=[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Ssozluk.sql"];
    database =[[Sqlite alloc] init];
    [database open:myDB];
    query=[NSString stringWithFormat:@"Select col_2,col_1 FROM words"];

     asdasd=[database executeQuery:query];
     for(NSDictionary *row in kelimeler)
     {
     asd=[row valueForKey:@"col_2"];
     olabel1.text=asd;
     }


    [database commit];
    [database close];

    }

Answer 1

You need programmatically copy your database to Documents dir of application, and work with writable copy. Resources in bundle is readonly.

Answer 2

I use the following code to copy my database to the documents folder and than get the writable part:

- (void)createEditableCopyOfDatabaseIfNeeded {
// First, test for existence.
BOOL success;
NSFileManager *fileManager = [NSFileManager defaultManager];
NSError *error;
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *writableDBPath = [documentsDirectory stringByAppendingPathComponent:@"db.sqlite"];
//NSLog(writableDBPath);
success = [fileManager fileExistsAtPath:writableDBPath];
if (success) return;
// The writable database does not exist, so copy the default to the appropriate location.
NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"db.sqlite"];
success = [fileManager copyItemAtPath:defaultDBPath toPath:writableDBPath error:&error];
if (!success) {
    NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
}

}

- (void)initializeDatabase {
// The database is stored in the application bundle. 
NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
NSString *documentsDirectory = [paths objectAtIndex:0];
NSString *path = [documentsDirectory stringByAppendingPathComponent:@"db.sqlite"];
// Open the database. The database was prepared outside the application.
if (sqlite3_open([path UTF8String], &database) == SQLITE_OK) {
    //Add initial sql statements here

} else {
    // Even though the open failed, call close to properly clean up resources.
    sqlite3_close(database);
    NSAssert1(0, @"Failed to open database with message '%s'.", sqlite3_errmsg(database));
    // Additional error handling, as appropriate...
}

}

- (sqlite3*) getDB{
return database;

}

Insert the calls to your applicationDidFinishLaunching method

Answer 3

As far as I remember, when you use path to db the way you do, it should be added to project.

Answer 4

I am using these lines of code to insert data. But yet it is not workable for me. I used the same technique you told in this post. But acquiring same result means nothing. I am trying to explain you in code as well that where i am facing difficulties.

- (void)dataInsertion {
    NSString *query;
    NSString *querySelect;
    NSString *queryInsert;
    NSArray *asdasd;
    NSArray *asd;
    Sqlite *sqliteDatabase =[[Sqlite alloc] init];

    /***************************Database Enterance is working here for temporary base*************************/

/******After Applying these lines of code, i am able to get the result in console not in my database. But as soon as i restart my application, I have lost the data in console as well.******/
    myDB=[[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Test.sqlite"];
    Sqlite *sqliteDatabase =[[Sqlite alloc] init];
    [sqliteDatabase open:myDB];

    queryInsert=[NSString stringWithFormat:@"INSERT INTO Test VALUES('value1','Value2','Value3');"];    
    NSLog(@"Query is , %@",queryInsert);
    [sqliteDatabase executeNonQuery:queryInsert];

    query=[NSString stringWithFormat:@"Select * FROM Test"];
    NSLog(@"Query is , %@",query);
    asdasd=[sqliteDatabase executeQuery:query];
    NSLog(@"Array contains %@",asdasd);
    [asdasd release];
    /*****************************************************/

    /***************Other Way*************/
/**************By Applying these line, My Application get crashed on lines  [sqliteDatabase executeNonQuery:queryInsert];*******/
    NSArray *paths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *documentsDirectory = [paths objectAtIndex:0];
    NSString *path = [documentsDirectory stringByAppendingPathComponent:@"SohaibDb.sqlite"];

    NSFileManager *fileManager = [NSFileManager defaultManager];
    NSError *error;
    BOOL success;
    success = [fileManager fileExistsAtPath:path];
    if (success)
    {
        NSLog(@"Writable database already exists.");
        /***** My control is coming here****/       
    }
    else
    {   
    /******** Control is not coming here*********/
    NSString *defaultDBPath = [[[NSBundle mainBundle] resourcePath] stringByAppendingPathComponent:@"Test.sqlite"];
    success = [fileManager copyItemAtPath:defaultDBPath toPath:path error:&error];
        if (!success) 
        {
            NSAssert1(0, @"Failed to create writable database file with message '%@'.", [error localizedDescription]);
        }       
    }

    /******* Here it is giving me warning that Argument2 is from incompatible pointer type*****/
    if (sqlite3_open([path UTF8String], &sqliteDatabase) == SQLITE_OK)
    {
        /***** My control is coming here ******/
        queryInsert=[NSString stringWithFormat:@"INSERT INTO Test VALUES('value1','Value2','Value3');"];    
        NSLog(@"Query is , %@",queryInsert);
        [sqliteDatabase executeNonQuery:queryInsert];  //On this line Application is Crashing without giving an error

        query=[NSString stringWithFormat:@"Select * FROM Test"];
        NSLog(@"Query is , %@",query);
        asdasd=[sqliteDatabase executeQuery:query];
        NSLog(@"Array contains %@",asdasd);
        [asdasd release];       
    }
    else
    {
          /********** Control is not coming here ********/
        NSLog(@"Database is not Opening.");
    }
    /*******************************************/

    [sqliteDatabase commit];
    [sqliteDatabase close]; 
}