I'm trying to run the following raw query in rails, only to see it fail:
query = 'SELECT * FROM users WHERE id IN ($1);'
results = ActiveRecord::Base.connection.exec_query(query, "My query", [ [1,2] ]);
What am I doing wrong?
The error I'm getting starts with this:
Could not log "sql.active_record" event. NoMethodError: undefined method `binary?' for 1:Fixnum
Clearly, I'm misusing [1, 2] bind params somehow, but I couldn't find a proper example myself.
P.S. This is minimal failing example, derived of a much more advanced query that can't be turned into ActiveRecord chain of calls. In other words – I can't rely on Arel when building my query.
P.P.S. I'm using rails 4.0.1 and postgresql 9.3
I'm pretty sure your NoMethodError is coming from the logging stuff. If we look exec_query, we see this:
def exec_query(sql, name = 'SQL', binds = [])
log(sql, name, binds) do
# call exec_no_cache(sql, binds) or exec_cache(sql, binds)...
Then if we look at exec_cache, we see this:
def exec_cache(sql, binds)
#..
@connection.send_query_prepared(stmt_key, binds.map { |col, val|
type_cast(val, col)
})
so the binds are supposed to be column/value pairs. The PostgreSQL driver expects col to be a column object so that it can ask it what its name is and how it should format the val, that information is used by the log call in exec_query to produce something pretty and human-readable in the Rails logs. A bit of experimentation suggests that you can use nil as the col and everything is happy.
That means that we've moved on to this:
exec_query(
'SELECT * FROM users WHERE id IN ($1)',
'my query',
[ [nil, [1,2]] ]
)
The underlying driver may or may not know what to do with the [1,2] array, I only have Rails3 with the PostgreSQL extensions available to test and it doesn't like the [1,2]. If Rails4 also doesn't like the array then you can pass the arguments one-by-one:
exec_query(
'SELECT * FROM users WHERE id IN ($1, $2)',
'my query',
[ [nil,1], [nil,2] ]
)