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

Shanty

Answer 1

I created a simple FMDB extension to solve the problem:

FMDB+InOperator on GitHub

Shanty

Answer 2

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.

Mccullough

Answer 3

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];

Shanty

But somehow this still doesn't work when the objects are NSStrings instead of NSNumbers. :-( – Shanty

This won't work. SQL sanitization will see this a single variable. – Nonresident

Answer 4

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];

Malvaceous

Answer 5

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.

Catabolism

Answer 6

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

Jackquelin