GRDB in Swift - .fetchAll Only Ever Returns One Column

Question

My Original Code

func getUserByEmpNum(_ id: Int) -> String {
    let nameQuery: String = "SELECT fld_str_firstname, fld_str_lastName FROM userView WHERE fld_int_id = \(id);"

    var returnStr = ""
    do {
        let dbQueue = try DatabaseQueue(path: MYCDatabase.pathToDatabase)
        try dbQueue.inTransaction(.none) { (db) -> Database.TransactionCompletion in
            let returnStrs = try String.fetchAll(db, sql: nameQuery)
            // Placing a breakpoint here, returnStrs only has one element?
            return .commit
        }
    } catch {
        print (error)
    }

    return returnStr
}

My Question

In this code if I do a query like select fld_str_firstname from myOwnUserView where fld_int_id = 2; I get one element in my returnStrs array, which is as expected. Then selecting the two fields, as in nameQuery, I still only ever get one string in the returnStrs array.

Why is this, and how do I fit it to get all the selected columns in the response?

Answer 1

String.fetchAll returns an array of Strings extracted from the leftmost selected column, as documented. One string for each fetched row. Not one string for each selected column.

If you want to grab strings from several columns, use Row.fetchAll, which returns an array of database rows. From those rows, you can extract each column you are interested into:

let rows = try Row.fetchAll(db, sql: "SELECT fld_str_firstname, fld_str_lastName FROM ...")
for row in rows {
    let firstName: String = row["fld_str_firstname"]
    let lastName: String = row["fld_str_lastName"]
}

See this chapter of the documentation for more information about extracting values from database rows.

Since you are reading the name from a single row identified with its id, you may prefer the fetchOne method, which consumes a single database row (see Fetching Methods):

if let row = try Row.fetchOne(db, sql: "SELECT ... WHERE fld_int_id = ?", arguments: [id]) {
    let firstName: String = row["fld_str_firstname"]
    let lastName: String = row["fld_str_lastName"]
    // Use first and last name 
} else {
    // ID does not exist in the database: do what is appropriate.
}