Getting results from arbitrary SQL statements with correct binding in SQLite.swift

Question

The SQLite.swift documentation says about executing arbitrary SQL:

let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    for (index, name) in stmt.columnNames.enumerate() {
        print ("\(name)=\(row[index]!)")
        // id: Optional(1), email: Optional("[email protected]")
    }
}

I wanted to get the values directly like this

let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    let myInt: Int64 = row[0] // error: Cannot convert value of type 'Binding?' to specified type 'Int64'
    let myString: String = row[1] // error: Cannot convert value of type 'Binding?' to specified type 'String'
}

but the row index is of type Binding? and I can't figure out how to convert that to the type I need. I see there is a Statement.bind method in the source code but I am still not discovering how to apply it.

Did you try using Expression?, (eg. let myInt: Expression<Int64> = ...) — l'L'l
– l'L'l, Commented Aug 15, 2016 at 17:37
@l'L'l, Good idea. Unfortunately it gives the same error (can't convert Binding? to Expression<Int64>). I've been successful using Expression in the past but now that I am trying to get arbitrary SQL to work (because of my problem with this), I've been having a lot of trouble. — Suragch
– Suragch, Commented Aug 15, 2016 at 18:19

Martin R · Accepted Answer · 2016-08-15 19:02:22Z

8

You can retrieve correctly typed selected columns from a table like this:

// The database.
let db = try Connection(...)

// The table.
let users = Table("users")

// Typed column expressions.
let id = Expression<Int64>("id")
let email = Expression<String>("email")

// The query: "SELECT id, email FROM users"
for user in try db.prepare(users.select(id, email)) {
    let id = user[id]       // Int64
    let mail = user[email]  // String
    print(id, mail)
}

An alternative is to (optionally) cast the Binding values to the correct type:

let stmt = try db.prepare("SELECT id, email FROM users")
for row in stmt {
    if let id = row[0] as? Int64,
        let mail = row[1] as? String {
        print(id, mail)
    }
}

edited Aug 15, 2016 at 19:02

answered Aug 15, 2016 at 18:35

Martin R

541k98 gold badges1.3k silver badges1.4k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Suragch Over a year ago

The second part of your answer solved my problem. The first part of your answer is surely better for the standard case (and I have done it like this before), but I am specifically wondering how to do it when executing arbitrary SQL. (I updated my question slightly to reflect that.) The reason I am resorting to arbitrary SQL is because of this as of yet unsolved problem.

Suragch Over a year ago

Is there any way to bind the column names using Expression when accessing the row data after an arbitrary SQL query? I can see bugs coming in the future when I forget to update the hard coded column index numbers.

Martin R Over a year ago

@Suragch: I doubt it. That would require to analyze the query string.

Collectives™ on Stack Overflow

Getting results from arbitrary SQL statements with correct binding in SQLite.swift

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related