NSData is being truncated when stored in sqlite database.

Question

I am attempting to store NSMutableDictionaries as a blobs in sqlite, by first converting it into NSData via either NSPropertyListSerialization or NSKeyedArchiver.

When I store the blob, the NSData object's length is in the thousands (KB range). When I get it back, it's been truncated to 10 bytes. When I check the DB through SQLite Browser, most of the data is gone (I can recognize the keys in the record if I store the dictionary as NSPropertyListSerialization, but the values of the dictionary are gone). This happens regardless of whether I use NSPropertyListSerialization or NSKeyedArchiver to serialize my data.

NSMutableDictionary* item = [items objectAtIndex:i];
NSData *dictionary  = [NSKeyedArchiver archivedDataWithRootObject:item];
sqlite3_bind_blob(  compiledStatement, 5, [dictionary bytes], [dictionary length], SQLITE_TRANSIENT);

This is actually a snippet from my code, the complete section of which I posted in another related question.

http://stackoverflow.com/questions/2445915/bulk-inserts-into-sqlite-db-on-the-iphone

Checking the value of [dictionary length] using gdb or NSLog yields the same result: The data length is in the KB range.

When I check retrieve the data later on:

NSData* raw = [[NSData alloc] initWithBytes:sqlite3_column_blob(compiledStatement, 1) length:sqlite3_column_bytes(compiledStatement, 1)];
[raw release];

Checking [raw length] gives me a mere 10 bytes. This is true for every instance of data that I've attempted to store in this column, whatever their starting size might be, they end up 10 bytes in the end. There's nothing wrong with my retrieval query. I've run it in the command line and in SQL Browser, and I am getting the correct records and columns, but the data that's been stored in the record for this specific column is incorrect.

What's happened to the rest of my data? Is there something wrong with the way I'm using sqlite3_bind_blob? I've checked the sqlite documentation for terminating characters or max size limits. My data is well within the maximum size of a blob entry, and I can find no information on terminals that might cut my data to size.

Answer 1

Have you checked the return type of that bind? It is possible that it returns an error code (the statement will still execute possibly in this event, just incorrectly).

As a workaround, have you tried creating a ZEROBLOB and then writing to it using the BLOB I/O routines described in the SQLite documentation? That would be the next thing I try after looking at error codes.

Answer 2

I've found the perpetrator in this wild good chase... I was taking the value of column 1 instead of column 0, which was the actual column that contained my data. I assumed that since binding values to your query starts at column 1, retrieving columns from query results would also start at 1. My mistake.

The 10 bytes per row were in fact NOT my dictionary data, but the timestamp I was using to sort my results.