FMDB: Retrieval of NULL values for value types in Swift

Using FMDB, bridged for use in Swift, I retrieved long integer values for a SQLite column definition like this

myColumn   BigInt NULL UNIQUE

with a line of Swift code for an FMResultSet (based on a straightforward select query left out here) like this

let value = resultSet.longForColumnName("myColumn")

This worked fine. Yet, when I retrieved and then updated multiple records involving this column, I ran into a Unique Key Index violation. As it turned out, for NULL values, the above line of Swift code returned a value of 0, and I couldn't see a quick way to detect NULL values properly.

When searching for a proper way to handle this, the only related question I could find is this one concerning empty strings being returned for text columns with Null values. The answer didn't apply here. So, I'm adding the results of my research here, should they be useful to somebody else.

(The underlying problem turns out to not be specific to having the Unique constraint.)

Solution

The FMDB API, when bridged from the Objective-C version, seemingly has no direct way to check for NULL values (correct me, if I'm wrong, please). So, for a database column defined as, for example,

myColumn   BigInt NULL

a NULL value will appear as value 0 in any FMResultSet involving this column with Swift code as shown in the question.

(This will have particularly surprising results when there happens to be a UNIQUE constraint on top. The NULL value will be retrieved as 0 from the database to be potentially updated as such with the next save operation, violating the Unique constraint when multiple entities are involved, as in my case. However, the underlyling problem is independent of the constraint. So, I'll focus on the problem of NULL values in general.)

To avoid this problem, we have to retrieve the column's value as an object from a respective FMResultSet first like so:

let objectValue = resultSet.objectForColumn("myColumn")

If objectValue happens to be of type/value NSNull(), then we have a NULL value and can handle it accordingly. Otherwise, we can then use the normal longForColumnName method. (For object types such as Strings, however, the FMDB implementation naturally returns an optional, which will be nil for database values of NULL!)

To make this easier, I use an extension of the FMResultSet class (for retrieval by index, which I prefer) like so:

extension FMResultSet {
    func isNullForColumnIndex(columnIdx: Int32) -> Bool {
        let value = self.objectForColumnIndex(columnIdx)
        if let nullValue = value as? NSNull {
            return true
        } else {
            return (value == nil)
        }
    }
}

This reduces the value extraction for a number type like in the example above to a line like this, assuming "myColumn" would appear at index 0 in the result set:

let num: Int64? = (result.isNullForColumnIndex(0) ? nil : Int64(result.longForColumnIndex(0)))

Surely, I could as well have added a method such as, for example, optionalLongForColumnIndex(columnIndex: Int32) -> Int64? that would include both the NULL-check and value retrieval. That would just require one such method for every value-type, which I have avoided so far.