Database query using array in ruby

Question

I'm trying to find all rows with value in array, This is my code

require 'sqlite3'
db = SQLite3::Database.new('test.sqlite')
res = db.query("SELECT w1.synsetid
                FROM words w1
                WHERE w1.wordid IN (?)", arr)

arr: array of strings

And I get this error

SQLite3::RangeException: bind or column index out of range

Any Help?

Answer 1

The second argument to query is meant to be an array of placeholder values:

- (Object) query(sql, bind_vars = [], *args)

This is a convenience method for creating a statement, binding paramters to it, and calling execute:

The query method doesn't know that it should treat your arr array specially, it just sees one placeholder and multiple values.

I think you have to do this this hard way: build the appropriate number of placeholders and paste them into the SQL. Something like this:

placeholders = (['?'] * arr.length).join(',')
res = db.query("select ... where w1.wordid in (#{placeholders})", arr)

You know exactly what is in placeholders so you don't have to worry about using string interpolation and injection issues when building your SQL like this.

If you're using Rails already then you could also wrap your SQLite tables with ActiveRecord and then use the usual ActiveRecord interface:

words = Word.where(:wordid => arr)