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