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

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("alice@mac.com")
    }
}

I wanted to get the values directly like this

  • NSFetchedResultsController not sorting on encrypted attributes
  • Sync Sqlite database with iCloud?
  • Where is myProject.sqlite located in Lion OSX Xcode 4.1?
  • Objective-C and sqlite's DATETIME type
  • What is the fastest way to load a large CSV file into core data
  • Best Cocoa/Objective-C Wrapper Library for SQLite on iPhone
  • 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.

    Solutions Collect From Internet About “Getting results from arbitrary SQL statements with correct binding in SQLite.swift”

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