Adding NULL Values to SQLite Database with FMDB and NSDictionary

Question

I have run into a bit of a catch 22. I am using FMDB's fancy withParameterDictionary method to insert data into my SQLite database like this:

NSDictionary *aircraftDict = [NSDictionary dictionaryWithObjectsAndKeys:
                              self.aircraftIdTextField.text, @"aircraft_id",
                              self.makeModelTextField.text, @"make_model",
                              self.categoryClassTextField.text, @"category_class",                                  
                              @YES, @"updated_flag",                                 
                              nil];

NSString *addAircraftQuery = @"INSERT INTO aircrafts (aircraft_id, make_model, category_class, updated_flag) VALUES (:aircraft_id, :make_model, :category_class, :updated_flag)";

[db executeUpdate:addAircraftQuery withParameterDictionary:aircraftDict];

The problem is that when one of those text fields is blank, it truncates the NSDictionary since a nil value tells the dictionary that it has arrived at the end of its list of values.

I can work around that issue just fine by making sure I have a blank object for each value in the dictionary (e.g. like a string @"").

But then my values arrive in my SQLite database as a "blank" value instead of a NULL. This may not be a big deal, but I've heard that using NULL will take up less space in the database.

How can I insert NULL into my database without prematurely truncating my NSDictionary?

Answer 1

Spent a while researching this one too. The solution for me (using Swift, but Objective C should be the same) is to use NSNull instead of nil

Answer 2

You can do it by not using the "fancy" withParameterDictionary method, and instead using the boring executeUpdate like this:

NSString *addAircraftQuery = @"INSERT INTO aircrafts (aircraft_id, make_model, category_class, updated_flag) VALUES (?, ?, ?, ?)";

[db executeUpdate:addAircraftQuery, self.aircraftDict, self.makeModelTextField.text, self.categoryClassTextField.text, @YES];