2

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.)

Community
  • 1
  • 1
marco
  • 900
  • 10
  • 21

3 Answers3

4

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.

marco
  • 900
  • 10
  • 21
1

Marco, your own answer is excellent: it describes exactly what is happening. Yet there are type-safer alternatives to ccgus/fmdb when coding in Swift. You could try https://github.com/stephencelis/SQLite.swift (pretty popular) or https://github.com/groue/GRDB.swift (closer to fmdb in the spirit).

Gwendal Roué
  • 3,949
  • 15
  • 34
  • Thanks for adding this very useful comment. I was aware and had a look at some of those, yet at the time was (and still am) not perfectly sure how stable these wrappers were given the rapid change of the language at the time (and still). It's always troublesome having to deal with discrepancies or upgrade delays. Also, there were cases in which these frameworks make implicit assumptions about the (somewhat odd) type handling of SQLite, in particular when it comes to timestamps. I would rather like to make sure types map the way I want. See: Timestamp mapped to strings and other types. – marco Oct 26 '15 at 14:22
  • I like this issue as well, a lot: as the author of GRDB, I really think now that I should stop letting sqlite silently converting NULL to 0 when the user writes `let i = row.value(named:"foo") as Int`. My goal is really to prevent nasty surprises, and this would be one :-) – Gwendal Roué Oct 26 '15 at 16:10
  • Can you elaborate on your pet peeve with timestamps? Meanwhile, I've updated GRDB.swift so that NULL can no longer be turned into non-optional values. I have tried to document as clearly as I could: https://github.com/groue/GRDB.swift#column-values. – Gwendal Roué Oct 26 '15 at 21:20
  • For details on those peevy pets, have a look at this (I believe incorrect) answer and my comments to it: http://stackoverflow.com/questions/7363112/best-way-to-work-with-dates-in-android-sqlite/22394601#22394601 I think SQLite with its auto-type conversion just works magically for most people that, however, don't realize what's actually being stored. In my case, I want to store potentially a lot of timestamps, and that at millisecond precision. Not all of the types SQLite can use support millisecond precision although the answer in question seems to suggest they do. – marco Oct 27 '15 at 16:37
  • What I would love a Swift-y framework for SQLite to do is this: Handle timestamps in *one*, well-defined, effective internal format with a predictable, consistent result. Text, to me, is out of the question. However, the other types don't support milliseconds, I recall. Something like a 64-bit epoch would be fine with me, but there is no support for that. In summary, I do feel like the auto-type conversion rolled by SQLite at this internal level is convenience gone wrong. Compare to: Swift's very rigid type system that doesn't even auto-convert between compatible Integer types. For a reason. – marco Oct 27 '15 at 16:45
  • GRDB.swift stores timestamps as *string*, the only format that goes to the millisecond, can't lose any precision, is trivially ordered, and is compatible with SQLite's CURRENT_TIMESTAMP. In the answer you link to in your previous comment, you put everything against you but mixing integers affinity columns and date functions. This is a useless fight. Use strings. Check https://github.com/groue/GRDB.swift#nsdate-and-nsdatecomponents, and be relieved. – Gwendal Roué Oct 27 '15 at 21:18
  • "I do feel like the auto-type conversion rolled by SQLite at this internal level is convenience gone wrong. Compare to: Swift's very rigid type system that doesn't even auto-convert between compatible Integer types. For a reason." Yes. You may have to look for another database, then. I think I have shipped a pretty sensible Swift API around those SQLite idiosyncrasies, though. – Gwendal Roué Oct 27 '15 at 21:24
0

I had a similar problem in Objective-C with FMDB. Also getting 0 instead of nil when using longForColumnIndex.

However using: NSNumber* object = [resultSet objectForColumnIndex:0]; worked as expected.

Gives a perfectly valid NSNumber object if a Value is stored, NSNull otherwise.

pre
  • 3,475
  • 2
  • 28
  • 43