FMDB: NULL Values Are Retrieved as Empty Strings

Question

I'm retrieving a customer record with FMDB and Swift using the (simplified) function below. When the optional value in the title column is NULLthe title member of the returned customer object is an empty string rather than nil, which is misleading. Can this be re-written such that NULL values are retrieved as nil? -- Ideally without testing for empty strings and setting nil explicitly (also wrong if the value is in fact an empty string)?

func getCustomerById(id: NSUUID) -> Customer? {

    let db = FMDatabase(path: dbPath as String)
    if db.open() {
        let queryStatement = "SELECT * FROM Customers WHERE id = ?"
        let result = db.executeQuery(queryStatement, withArgumentsInArray: [id.UUIDString])

        while result.next() {
            var customer = Customer();
            customer.id = NSUUID(UUIDString: result.stringForColumn("customerId"))
            customer.firstName = result.stringForColumn("firstName")
            customer.lastName = result.stringForColumn("lastName")
            customer.title = result.stringForColumn("title")
            return customer
        }
    }
    else {
        println("Error: \(db.lastErrorMessage())")
    }
    return nil
}

From the [source code](https://github.com/ccgus/fmdb/blob/master/src/fmdb/FMResultSet.m#L273) I would assume that a NULL value *is* returned as `nil`. — Martin R, Aug 03 '15 at 14:20
I thought I checked by inspecting the database. Checked again - and you are right. And I'm embarrassed. — , Aug 03 '15 at 14:59

score 0 · Accepted Answer · answered Aug 03 '15 at 15:03

The NULL values are returned as nil:

db.executeUpdate("create table foo (bar text)", withArgumentsInArray: nil)
db.executeUpdate("insert into foo (bar) values (?)", withArgumentsInArray: ["baz"])
db.executeUpdate("insert into foo (bar) values (?)", withArgumentsInArray: [NSNull()])

if let rs = db.executeQuery("select * from foo", withArgumentsInArray: nil) {
    while rs.next() {
        if let string = rs.stringForColumn("bar") {
            println("bar = \(string)")
        } else {
            println("bar is null")
        }
    }
}

That outputs:

bar = baz
bar is null

You might want to double check how the values were inserted. Specifically, were empty values added using NSNull? Or perhaps open the database in an external tool and verify that the columns are really NULL like you expected.

You are right, and new records are indeed inserted that way. I must have been looking at some older data that was added incorrectly. Thanks for explaining it here. I'll leave the question as your answer may me of use for others. — , Aug 03 '15 at 15:10

FMDB: NULL Values Are Retrieved as Empty Strings

1 Answers1

Linked