How to save & retrieve NSdata into sqlite through FMDB

Question

How can I save NSData into sqlite, I am using FMDB wrapper for saving data.

Below is the code which I have tried so far
For saving

NSData *data = [NSKeyedArchiver archivedDataWithRootObject:model.expertArray];; 
NSString *query = [NSString stringWithFormat:@"insert into save_article values ('%@','%@','%@','%@','%@','%@')",
                       model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

For Retriving

while([results next]) {
  NSData *data = [results dataForColumn:@"experts"];
        NSMutableArray *photoArray = [NSKeyedUnarchiver unarchiveObjectWithData:data];
 }     

I have tried both datatype blob & text but no luck so far, can anybody guide me how to do it?

Flea

Generally NSData should be stored as a blob. And, with a blob you pretty much must use bound parameters. Otherwise the (non-ASCII) blob data will cause a corrupted insert statement to be generated. – Drench

I tried with blob also but that doesnt work – Flea

(BTW, if you're building your statements that way you're not really using FMDB, you'll only working around it.) – Drench

You must use bound parameters. Simply inserting a blob with stringWithFormat results in a corrupted SQL statement. – Drench

Can you give example on bound parameters ? – Flea

What rmaddy said. Though there are several ways to do it. – Drench

Answer 1

Below is the Snippet for all who may face the same issue while inserting NSData to Sqlite using FMDB.

In my Case I wanted to store NSArray in Sqlite So i first convert the NSArray into NSData & then store it in Sqlite.

Converting NSArray into NSData

NSData *data = [NSKeyedArchiver archivedDataWithRootObject:YourNSarray];;

Saving NSData into Sqlite

[database executeQuery:@"insert into save_article values (?,?,?,?,?,?)", model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

Note:- Don't build a query using stringWithFormat[below is the code which you should not use]:. Thanks to @rmaddy & @hotlicks for pointing me to this :)

NSString *query = [NSString stringWithFormat:@"insert into user values ('%@', %d)",
@"brandontreb", 25];
[database executeUpdate:query];

and now the last step i.e retrieving NSData back to NSArray

NSArray *array = [NSKeyedUnarchiver unarchiveObjectWithData:[database dataForColumn:@"yourcololumname"]];

Hope this will help the needy & beginner :)

Flea

What is "data" columns data type? – Schleicher

Use blob for data columns – Flea

@Flea I have tried this but it is not working for me even i have the data type blob. Is there any more clarification you can give ? Also, dont you think query should be like this Insert into user (coulmn1, 2,3) values(?,?,?)" ... – Massive

Answer 2

Don't build a query using stringWithFormat:. This is a bad idea for several reasons.

Use the executeQuery method where you put a ? for each value to be bound to the query.

Something like this:

[database executeQuery:@"insert into save_article values (?,?,?,?,?,?)", model.Id, model.title, model.earliestKnownDate, data, model.excerpt,model.image_url];

Crew

which datatype should i use then blob or text – Flea

Use a blob for NSData. – Crew

@rmaddy, can you please elaborate the reasons for not using stringWithFormat? – Millett

@EricChuang The problem is that if you use stringWithFormat:, the values may contain special characters that aren't properly escaped. This leads to bugs and crashes. It also can lead to SQL injection attacks (search on that term if you don't know what that is). By properly binding values you eliminate all of these issues. – Crew

@Crew i faced same issue for stringWithFormat: while inserting string like ex. That's. this ' is creating issue while inserting how to fix that? – Wellborn

the ' needs to be escaped, in sqlite it's escaped with an apostrophe, so it ends up looking like this: '' (2 apostrophes not a double quote) or you can use SQLite's built in formatters: char *result = sqlite3_vmprintf("%Q", variadic params);. %q and %Q are likely what you want. sqlite has a few different sqlite3_*printf versions. As others noted, it's safer to just use statement binding then you don't need to worry about it. sqlite.org/c3ref/mprintf.html – Unnerve

So if I used to build the query like in your example, I have to pass the values in the order they are in table? @Crew – Chiller

@Chiller The best solution is to list the column names in the SQL: INSERT INTO table_name (col1, col2, col3) VALUES (?, ?, ?). Then the values should be passed in the order listed in the INSERT statement. – Crew

executeUpdate, not executeQuery – Retiform