passing array as parameter in sqlite Swift

Question

I implemented Sqlite in my project by adding #import <sqlite3.h> in my header.h file and libsqlite3.dylib.

How can I pass an array as parameter to my query, here is what I thought:

var arrayId = [1,2] // array with interested Id 
var query:NSString = "Select id from Product where id IN \(arrayId)" // I want to select Products with id that are in the array

Edit: Does it change if arrayId is NSArray ? Because I also need arrayId as NSArray.

then I proceed with open sqlite database, prepare query and so on.

Thank you in advance.

Since this post is a top ten search engine result for the same question, note that since 2016 there is a loadable table-valued function extension (not compiled into sqlite by default) called carray() that lets you bind an array as an inline table against which to join your query (instead of an 'IN' clause). — mdisibio
– mdisibio, Commented Jul 21, 2021 at 18:03

rakeshbs · Accepted Answer · 2015-01-08 16:36:12Z

6

You can easily combine the array into a string with join function.

var arrayId = [1,2] // array with interested Id
var inExpression = ",".join(map(arrayId) { "\($0)"})
// inExpression = "1,2"
var query = "Select id from Product where id IN (\(inExpression))"

answered Jan 8, 2015 at 16:36

rakeshbs

24.6k7 gold badges75 silver badges63 bronze badges

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

1 Comment

rakeshbs Over a year ago

Even NSArray can be used the same way.

ArgaPK · Accepted Answer · 2018-04-20 15:22:32Z

2

Update for Swift3:

var arrayId = [1,2] // array with interested Id
var inExpression = arrayId.flatMap{ String($0) }.joined(separator: ",")
// inExpression = "1,2"
var query = "SELECT id FROM Product WHERE id IN (\(inExpression))"

answered Apr 20, 2018 at 15:22

ArgaPK

4551 gold badge9 silver badges22 bronze badges

Comments

Jacob Budin · Accepted Answer · 2015-01-08 16:34:33Z

0

You need to accomplish two things: convert your array of Ints to Strings and then implode the array into a string, by joining them with commas (as you would want to do with an IN SQL statement).

Here's a rudimentary function that does just that:

func implode(ints: [Int]) -> String {
    // Convert to Strings
    let strs = ints.map { String($0) }
    // Join Strings with commas
    return ",".join(strs)
}

And then in use:

"WHERE id IN (\(implode(arrayId)))"

answered Jan 8, 2015 at 16:34

Jacob Budin

10k4 gold badges35 silver badges37 bronze badges

Comments

David Berry · Accepted Answer · 2015-01-08 16:34:49Z

0

I'd probably use something like:

var arrayId = [1,2] // array with interested Id
var str = ",".join(arrayId.map { return "\($0)" })
var query = "SELECT id FROM Product WHERE id IN [\(str)]"

answered Jan 8, 2015 at 16:34

David Berry

41.3k12 gold badges87 silver badges96 bronze badges

2 Comments

Chongzl Over a year ago

in this way my query is SELECT id FROM Product WHERE id IN [1,2], I execute this on SQLite Manager, there is an error: no such table: 1,2

David Berry Over a year ago

Try it with parenthesis around the \(str) instead of brackets. I was guessing at the actual SQL syntax.

xpqz · Accepted Answer · 2015-01-09 12:07:13Z

0

Using Swift's own string interpolation to create SQL statements can be risky (as with any language). The sqlite3 library provides parameter binding for this purpose:

if (statement.prepare("SELECT name FROM products WHERE id = ?") != .Ok) {
    // Deal with error here
}

// Bind the question mark to your value
statement.bindInt(1, value: 8766)

if (statement.step() == .Row) {
    let name = statement.getStringAt(1)
    // ...do something with your data from the statement
}

// Done.
statement.finalizeStatement()

EDIT:

For the comment below, you need () brackets, not []:

select id, body from test where id in (1,2);

not

select id, body from test where id in [1,2];

edited Jan 9, 2015 at 12:07

answered Jan 8, 2015 at 16:37

xpqz

3,73713 silver badges17 bronze badges

2 Comments

David Berry Over a year ago

Your comment is valid, but doesn't really answer the question of how to pass in an Array of Int's as an IN parameter.

Chongzl Over a year ago

As @David said, it doesn't answer the question. Thank you anyway.

Collectives™ on Stack Overflow

passing array as parameter in sqlite Swift

5 Answers 5

1 Comment

Comments

Comments

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related