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 ["[email protected]", "[email protected]"] {
    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
– Dean, Commented Apr 16, 2015 at 15:18
@deanware Won't this generate one query for each Array entry? — Michael
– Michael, Commented Apr 16, 2015 at 15:19
ahh yes...your right...this is not what your looking for....sorry... — Dean
– Dean, Commented Apr 16, 2015 at 15:23

stephencelis · Accepted Answer · 2015-04-16 17:04:57Z

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, 2015 at 17:04

stephencelis

4,9642 gold badges31 silver badges22 bronze badges

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

2 Comments

Michael Over a year ago

Why is the last , emails ? What does this mean?

stephencelis Over a year ago

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"])

Collectives™ on Stack Overflow

Use NSArray in SQLite Statement

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related