1

I'm trying to save an audio file in my SQLite DB and restore it.

I stored this way to NSData:

NSData *audioData = [NSData dataWithContentsOfFile:[audios objectAtIndex:A]];
insert_stmt = "INSERT INTO audios (fecha, audio) VALUES (?, ?)";

if( sqlite3_prepare_v2(contactDB, insert_stmt, -1, &statement, NULL) == SQLITE_OK )
{
    sqlite3_bind_text(statement, 1, [Fecha UTF8String], -1, SQLITE_TRANSIENT);
    sqlite3_bind_blob(statement, 2, [audioData bytes], [audioData length], SQLITE_TRANSIENT);
    sqlite3_step(statement);
}
else NSLog( @"SaveBody: Failed from sqlite3_prepare_v2. Error is:  %s", sqlite3_errmsg(contactDB) );
sqlite3_finalize(statement);

And I get it back in this way:

while (sqlite3_step(SQLstatement) == SQLITE_ROW)
{
    int length = sqlite3_column_bytes(SQLstatement, 2);
    NSData* data = nil;
    data       = [NSData dataWithBytes:sqlite3_column_blob(SQLstatement, 2) length:length];

    NSArray *dirPaths;
    NSString *docsDir;

    dirPaths = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    docsDir = [dirPaths objectAtIndex:0];

    NSString *ruta = [NSString stringWithFormat:@"%@-%d-sound.caf", fecha, contador];
    NSString *soundFilePath = [docsDir stringByAppendingPathComponent:ruta];

    [[NSFileManager defaultManager] createFileAtPath:soundFilePath contents:data attributes:nil];
}

But when I create the audio file that only contains 0 Kb

rmaddy
  • 314,917
  • 42
  • 532
  • 579
Woch Ncnddnd
  • 25
  • 4
  • Its pretty unusual to save a data file like this to your SQLite. Usually people save these files, and then just store a link to the file. – HalR Oct 24 '13 at 15:44
  • 1
    Show the query for reading the data. Keep in mind that the column index for `sqlite3_column_xxx` is 0-based while the index for the `sqlite3_bind_xxx` is 1-based. – rmaddy Oct 24 '13 at 15:47
  • @HalR,You're right usually just save the file and path in the DB, but in this case I work best all in the DB. – Woch Ncnddnd Oct 24 '13 at 16:00
  • @rmaddy This is the query: NSString *ConsultaSQL = [NSString stringWithFormat:@"SELECT fecha, audio FROM audios WHERE fecha = '%@'", fecha]; sqlite3_stmt *SQLstatement; query_stmt = [ConsultaSQL UTF8String]; sqlite3_prepare_v2(contactDB, query_stmt, -1, &SQLstatement, NULL); – Woch Ncnddnd Oct 24 '13 at 16:01
  • Did you inspect the sqlite data file to make sure the data is properly in there? (I hate to ask, but you never know) – HalR Oct 24 '13 at 16:02
  • @WochNcnddnd Update your question with the additional code. It is too hard to read in a comment. – rmaddy Oct 24 '13 at 16:05
  • @HalR Yes, With SQlite Manager, use the option "Save Blob data as a file" and the *. Caf archived and plays correctly. – Woch Ncnddnd Oct 24 '13 at 16:10

1 Answers1

0

As stated, you are using the wrong column index. In your query to load the data you use:

SELECT fecha, audio FROM audios WHERE fecha = XXX;

This means you need to use column index 1, not 2 to access the audio column.

This is the worst part of the sqlite3 C-API. The sqlite3_bind_xxx functions use a 1-based column index while the sqlite3_column_xxx functions use a 0-based column index.

rmaddy
  • 314,917
  • 42
  • 532
  • 579