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?

Post code as text, not an image. Could you print the variable returnStrs and include the result? — Joakim Danielson, Dec 15 '19 at 20:05
Run the query directly on your database, using `sqlite3`. What does it return? — Gereon, Dec 15 '19 at 20:17
Posts should be self-contained as far as practically possible. Do not rely on links to external sites for content that could be included, quoted or paraphrased in the post itself. Do not use images of text (e.g. screenshots or scans of printed pages) for material that could instead be directly typed into the post; https://meta.stackoverflow.com/a/346503/1180728 — Blazej SLEBODA, Dec 16 '19 at 21:28
Guys, I've corrected as per the comments. Please stop down voting my original question. — Stephen Clark, Dec 17 '19 at 12:16

Gwendal Roué · Accepted Answer · 2019-12-16T10:53:39.453

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

GRDB in Swift - .fetchAll Only Ever Returns One Column

My Original Code

My Question

1 Answers1