Passing an array to sqlite WHERE IN clause via FMDB?

Question

Is it possible to pass an array to a SELECT … WHERE … IN statement via FMDB? I tried to implode the array like this:

NSArray *mergeIds; // An array with NSNumber Objects
NSString *mergeIdString = [mergeIds componentsJoinedByString:@","];

NSString *query = @"SELECT * FROM items WHERE last_merge_id IN (?)";
FMResultSet *result = [database executeQuery:query, mergeIdString];

This only works if there is exactly 1 object in the array, which leads me to believe that FMDB adds quotes around the whole imploded string.

So I tried passing the array as is to FMDB's method:

NSArray *mergeIds; // An array with NSNumber Objects
NSString *query = @"SELECT * FROM items WHERE last_merge_id IN (?)";
FMResultSet *result = [database executeQuery:query, mergeIds];

Which doesn't work at all.

I didn't find anything about it in the README or the samples on FMDB's github page.

Thanks, Stefan

score 13 · Answer 1 · edited Sep 26 '13 at 17:02

I was having the same issue, and I figured it out, at least for my own app. First, structure your query like so, matching the number of question marks as the amount of data in the array:

NSString *getDataSql = @"SELECT * FROM data WHERE dataID IN (?, ?, ?)";

Then use the executeQuery:withArgumentsInArray call:

FMResultSet *results = [database executeQuery:getDataSql withArgumentsInArray:dataIDs];

In my case, I had an array of NSString objects inside the NSArray named dataIDs. I tried all sorts of things to get this SQL query working, and finally with this combination of sql / function call, I was able to get proper results.

score 10 · Answer 2 · edited Dec 12 '11 at 19:22

10

Well I guess I have to use executeQueryWithFormat (which, according to FMDB documentation is not the recommended way). Anyway, here's my solution:

NSArray *mergeIds; // An array of NSNumber Objects
NSString *mergeIdString = [mergeIds componentsJoinedByString:@","];

NSString *query = @"SELECT * FROM items WHERE last_merge_id IN (?)";
FMResultSet *res = [self.database executeQueryWithFormat:query, mergeIdString];

edited Dec 12 '11 at 19:22

Chris Cashwell

20,898
13
60
91

answered Dec 12 '11 at 18:46

Stefan

1,011
3
11
22

2

But somehow this still doesn't work when the objects are NSStrings instead of NSNumbers. :-( – Stefan Dec 21 '11 at 22:20
4

This won't work. SQL sanitization will see this a single variable. – Luke The Obscure Mar 28 '14 at 16:49

score 5 · Answer 3 · answered Feb 22 '14 at 18:51

Adding onto Wayne Liu, if you know that the strings do not contain single or double quotes, you could simply do:

NSString * delimitedString = [strArray componentsJoinedByString:@"','"];
NSString * sql = [NSString stringWithFormat:@"SELECT * FROM tableName WHERE fieldName IN ('%@')", delimitedString];

Wayne Liu · Answer 4 · 2012-02-15T08:36:30.157

If the keys are strings, I use the following code to generate the SQL command:

(assume strArray is an NSArray containing NSString elements)

NSString * strComma = [strArray componentsJoinedByString:@"\", \""];
NSString * sql = [NSString stringWithFormat:@"SELECT * FROM tableName WHERE fieldName IN (\"%@\")", strComma];

Please note: if any elements in strArray could potentially contain the "double quote" symbols, you need to write extra codes (before these 2 lines) to escape them by writing 2 double quotes instead.

score 2 · Accepted Answer · answered Oct 06 '15 at 20:01

2

I created a simple FMDB extension to solve the problem:

FMDB+InOperator on GitHub

answered Oct 06 '15 at 20:01

Stefan

1,011
3
11
22

score 0 · Answer 6 · answered Jun 09 '15 at 16:50

Here's a Swift extension for FMDatabase that breaks array query parameters into multiple named parameters.

extension FMDatabase {

    func executeQuery(query: String, params:[String: AnyObject]) -> FMResultSet? {

        var q = query
        var d = [String: AnyObject]()
        for (key, val) in params {
            if let arr = val as? [AnyObject] {
                var r = [String]()
                for var i = 0; i < arr.count; i++ {
                    let keyWithIndex = "\(key)_\(i)"
                    r.append(":\(keyWithIndex)")
                    d[keyWithIndex] = arr[i]
                }
                let replacement = ",".join(r)
                q = q.stringByReplacingOccurrencesOfString(":\(key)", withString: "(\(replacement))", options: NSStringCompareOptions.LiteralSearch, range: nil)
            }
            else {
                d[key] = val
            }
        }

        return executeQuery(q, withParameterDictionary: d)
    }

}

Example:

let sql = "SELECT * FROM things WHERE id IN :thing_ids"
let rs = db.executQuery(sql, params: ["thing_ids": [1, 2, 3]])

Passing an array to sqlite WHERE IN clause via FMDB?

6 Answers6

Linked