Use NSArray in SQLite Statement

Question

I use SQLite.swift and want to replace the question marks in a statement. This can be done for single entries like this:

let stmt = db.prepare("INSERT INTO users (email) VALUES (?)")
for email in ["betty@icloud.com", "cathy@icloud.com"] {
    stmt.run(email)
}

I did not see how I can do the following to use a array like:

var values:[String] = ["test1","test2", "test3"]

in a Statement like:

let stmt = db.prepare("SELECT * from users where email in (?)")

The following does not work:

stmt.run(values)

How do I use an NSArray as an argument for a statement?

how about `for email in values { stmt.run(email) }` Sorry if I misinterpret your question.... — Dean, Apr 16 '15 at 15:18
@deanware Won't this generate one query for each Array entry? — Michael, Apr 16 '15 at 15:19
ahh yes...your right...this is not what your looking for....sorry... — Dean, Apr 16 '15 at 15:23

score 1 · Accepted Answer · answered Apr 16 '15 at 17:04

1

If you're using the type-safe layer, you can use contains():

users.filter(contains(["test1", "test2"], email))

// the above assumes this boilerplate precedes it:
let email = Expression<String>("email")
let users = db["users"]

If you're using the raw SQLite3 API, it does not support arrays, so you'll need to format those yourself:

let emails = ["test1", "test2"]
let template = join(", ", [String](count: count(emails), repeatedValue: "?"))
let stmt = db.prepare(
    "SELECT * FROM users WHERE email IN (\(template))", emails
)

answered Apr 16 '15 at 17:04

stephencelis

4,954
2
29
22

Why is the last ``, emails`` ? What does this mean? – Michael Apr 17 '15 at 10:05
1

It's defined immediately above it. `template` basically expands the number of `emails` to the number of needed `?`s, in this case `?, ?`. This basically makes the following: `db.prepare("SELECT * FROM users WHERE email IN (?, ?)", ["test1", "test2"])` – stephencelis Apr 17 '15 at 14:53

Use NSArray in SQLite Statement

1 Answers1