Tutorial for saving a data-file in SQLite3

[shnaps]

Hi

May I request someone to create a tutorial that shows how to save a data file (image, sound, text etc.) in SQLite3 database?


If there is one already, please point me to it.

Thanks in advance.
Sam

[mr-sk]

Off the top of the head, so there could be errors:

1. You need to include libsqlite3 in your frameworks dir
2. Include sqlite3.h in your controller
3. Make the db writable by copying to your app sandbox

    NSString *dbFilePath;
    NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentFolderPath = [searchPaths objectAtIndex:0];
    dbFilePath = [documentFolderPath stringByAppendingPathComponent:DATABASE_NAME_EXT];
    NSLog(dbFilePath);
    if (![[NSFileManager defaultManager] fileExistsAtPath:dbFilePath])
    {
        // copy the db
        NSString *backupDbPath = [[NSBundle mainBundle] pathForResource:DATABASE_NAME ofType:DATABASE_EXT];
        NSLog(backupDbPath);
        if (backupDbPath == nil)
        {
            NSLog(@"viewDidLoad.backupDbPath is nil");
        }
        else
        {
            BOOL copiedBackupDb = [[NSFileManager defaultManager] copyItemAtPath:backupDbPath toPath:dbFilePath error:nil];
            if (!copiedBackupDb)
            {
                NSLog(@"viewDidLoad.copiedBackupDb is NO");
            }
        }
    }

4. Write to the db (insert/update query)

DATABASE_NAME_EXT & DATABASE_EXT are defined in my .h file for convenience. A NSString will do.

-(void)insertIntoDB:(NSString *)sqlStr
{
    NSArray *searchPaths = NSSearchPathForDirectoriesInDomains(NSLibraryDirectory, NSUserDomainMask, YES);
    NSString *documentFolderPath = [searchPaths objectAtIndex:0];
    NSString *dbFilePath = [documentFolderPath stringByAppendingPathComponent:DATABASE_NAME_EXT];
    
    sqlite3 *dbHandle;
    int dbrc;
    dbrc = sqlite3_open([dbFilePath UTF8String], &dbHandle);
    
    sqlite3_stmt *preparedStatement;
    const char* queryStatement = [sqlStr UTF8String];
    dbrc = sqlite3_prepare_v2(dbHandle, queryStatement, -1, &preparedStatement, NULL);
    dbrc = sqlite3_step(preparedStatement);
    sqlite3_finalize(preparedStatement);
    sqlite3_close(dbHandle);    
}

You'll probably want to add in some error checking as well.
Hope that helps a bit -