2

Using MacOS Terminal I created a database name database.sql and inserted some records. Using iOS I can retrive the inserted values.

But using iOS code I tried to insert the record to the database and it does not enter the record in the database.

Should I set the need to set some permission? This is my code:

- (void)viewDidLoad
{
    [super viewDidLoad];
    [self openDB];
}

-(IBAction)save:(id)sender{

    [self insertRecordIntoTableNamed:@"Contacts" 
                         field1Value:fname.text
                         field2Value:lname.text
                         field3Value:comp.text 
                         field4Value:email.text 
                         field5Value:pnumber.text 
                         field6Value:mnumber.text 
                         field7Value:add.text 
                         field8Value:city.text 
                         field9Value:state.text];
}


-(void)openDB{
    NSString *sqlfile=[[NSBundle mainBundle]pathForResource:@"database" ofType:@"sql"];

    if(sqlite3_open([sqlfile UTF8String], &db)!= SQLITE_OK){
        sqlite3_close(db);
        NSLog(@"Database connected");
        NSAssert(0,@"Database failed to open");
    }
    else
    {
        NSLog(@"Database connected");
    }
}


-(void) insertRecordIntoTableNamed:(NSString *) tableName
                       field1Value:(NSString *) field1Value
                       field2Value:(NSString *) field2Value
                       field3Value:(NSString *) field3Value
                       field4Value:(NSString *) field4Value
                       field5Value:(NSString *) field5Value
                       field6Value:(NSString *) field6Value
                       field7Value:(NSString *) field7Value
                       field8Value:(NSString *) field8Value
                       field9Value:(NSString *) field9Value {

    NSString *sql = [NSString stringWithFormat:@"INSERT INTO %@ VALUES ('%@','%@','%@','%@',%@,%@,'%@','%@','%@');",tableName, field1Value, field2Value,field3Value,field4Value,field5Value,field6Value,field7Value ,field8Value,field9Value];


    NSLog(@"%@",sql);

    // char *err;
    sqlite3_stmt *statement;
    if (sqlite3_prepare_v2(db, [sql UTF8String], -1, &statement, nil)== SQLITE_OK)
    {
        if (SQLITE_DONE!=sqlite3_step(statement))
        {
            sqlite3_close(db);
            NSAssert(0, @"Error updating table.");
        }
        else{
            NSLog(@"Success");
            NSLog(@"%@",sql);
        }
    }
    sqlite3_finalize(statement);
}

@end

Here is what i have done:

  1. change the folder and path permissions using CHMOD

  2. split the sqlite3_exec() into sqlite3_prepare(), sqlite3_step() and sqlite3_finalize() - I get the same output - query is created but record is not created in database

  3. I am able to retrieve record information
Improve this question
5
  • 1
    Which, if any, step reports that it failed, and what error information did you get back? Commented Jan 10, 2013 at 12:20
  • i am not getting any error, after running my project if i insert the data and retrieve it, it shows the data but if i run the program and again try to retrieve the data which i entered earlier it does not shows any result do you have any idea why this happens Thanks for your response Commented Jan 10, 2013 at 12:27
  • check path,refer stackoverflow.com/questions/14207782/… Commented Jan 10, 2013 at 12:45
  • Yep, you're almost certainly trying to operate on the file in the resource bundle, and that's read-only. It must be copied into a writeable directory owned by your app. Commented Jan 10, 2013 at 13:41
  • @chiron, did you get the copy working? Did the answer & code below help? Commented Jan 10, 2013 at 23:34

2 Answers 2

Reset to default
2

You can't write to a database in the resource bundle. You need to copy it and then use it.

Here's some code I've successfully used to do that (key is ensureDatabasePrepared where it is copied from resources):

- (BOOL)ensureDatabaseOpen: (NSError **)error
{
    // already created db connection
    if (_contactDb != nil)
    {
        return YES;
    }

    NSLog(@">> ContactManager::ensureDatabaseOpen");    
    if (![self ensureDatabasePrepared:error])
    {
        return NO;
    }

    const char *dbpath = [_dbPath UTF8String]; 
    if (sqlite3_open(dbpath, &_contactDb) != SQLITE_OK &&
        error != nil)
    {
        *error = [[[NSError alloc] initWithDomain:@"ContactsManager" code:1000 userInfo:nil] autorelease];
        return NO;
    }

   NSLog(@"opened");

    return YES;
}

- (BOOL)ensureDatabasePrepared: (NSError **)error
{
    // already prepared
    if ((_dbPath != nil) &&
        ([[NSFileManager defaultManager] fileExistsAtPath:_dbPath]))
    {
        return YES;
    }

    // db in main bundle - cant edit.  copy to library if !exist
    NSString *dbTemplatePath = [[NSBundle mainBundle] pathForResource:@"contacts" ofType:@"db"];
    NSLog(@"%@", dbTemplatePath);

    NSString *libraryPath = [NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES) lastObject];
    _dbPath = [libraryPath stringByAppendingPathComponent:@"contacts.db"];

    NSLog(@"dbPath: %@", _dbPath);

    // copy db from template to library
    if (![[NSFileManager defaultManager] fileExistsAtPath:_dbPath])
    {
        NSLog(@"db not exists");
        NSError *error = nil;
        if (![[NSFileManager defaultManager] copyItemAtPath:dbTemplatePath toPath:_dbPath error:&error])
        {
            return NO;
        }

        NSLog(@"copied");
    }    

    return YES;    
}
Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

If you are interacting with the database only from the code you posted - you are missing a sqlite3_close. Most likely the changes are not getting flushed onto disk

Improve this answer

1 Comment

sqlite doesn't only flush writes on close. As a matter of fact, many single user mobile apps keep the connection open for the lifetime of the app and avoid the open/close cost.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.