CODEWITHSUNDEEP
objective-carrayssqliteselectfmdb
CrazyGoose
CrazyGoose

Reputation: 51

FMDB - Select statement using an array of values

The array (arrayBottleID3) has a variable amount of INT values. I want to return (results / arrayBottleNames) to return the results.

arrayBottleID3 may have 3 INT values, I don't want to have to do 3 different query statements. I don't know what values or how many arrayBottleID3 has.

arrayBottleID3 could have 1 or 100 INT values.

Below does not work. I get errors or it doesnt return anything.

I just dont know what the exact syntax is for using withArugmentsInArray. I know it isn't .. bottleID = ? or bottleID (?).

Can someone please show me an example?

[self OpenDB]    


 results = [database executeQuery:@"SELECT friendlyname FROM Inventory WHERE bottleID = ?" withArgumentsInArray:@[arrayBottleID3]];

while([results next])
{
    [arrayBottleNames addObject:[results stringForColumn:@"friendlyname"]];

}

[database close];

NSLog(@"count %d", [arrayBottleNames count]);
NSLog(@"Names: %@",arrayBottleNames);

Upvotes: 1

Views: 3171

Answers (2)

David H.
David H.

Reputation: 2862

I use the following approach in Swift to create the ?,?,?,?... string:

let bindStr = join(",", Array(count: sourceArray.count, repeatedValue: "?"))
if let rs = db.executeQuery("SELECT * FROM MyTable WHERE myField IN (\(bindStr))", withArgumentsInArray: sourceArray) { ... handle result set... }

Just replace sourceArray with your own.

Upvotes: 2

Holly
Holly

Reputation: 5300

Your SQL should be:

SELECT friendlyname FROM Inventory WHERE bottleID IN (?, ?, ?)

You will need 1 ? for each element in the array, so you should do something like this:

NSMutableArray *placeHolders = [NSMutableArray arrayWithCapacity:[sourceArray count]];
for (int i=0; i<[sourceArray count]; i++) {
    [placeHolders addObject:@"?"];
}
NSString *placeHolderString = [placeHolders componentsJoinedByString:@", "];

NSString *query = [NSString stringWithFormat:@"SELECT * FROM MyTable WHERE myField IN (%@)", placeHolderString];
FMResultSet *rs = [db executeQuery:query withArgumentsInArray:sourceArray];

Upvotes: 6

Related Questions