Raw DB querying in Rails

pg, postgresql, ruby-on-rails, ruby-on-rails-4, sql

Solution

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

Problem

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`

Original source