Using oracle bind variables with select_all in Rails

Question

I'm trying to take advantage of bind variables in Ruby on Rails, and it doesn't need to instantiate a model, so I'm using select_all like so

ActiveRecord::Base.connection.select_all(
  'select * from users where id = :test', 
  {test: 'foo'}
)

But I get this big old error:

ActiveRecord::StatementInvalid: OCIError: ORA-01008: not all variables bound: select * from users where id = :test
    from stmt.c:243:in oci8lib_220.so
    from /usr/local/rvm/gems/ruby-2.2.2/gems/ruby-oci8-2.2.4.1/lib/oci8/cursor.rb:127:in `exec'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-oracle_enhanced-adapter-1.4.3.61/lib/active_record/connection_adapters/oracle_enhanced_oci_connection.rb:149:in `exec'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-oracle_enhanced-adapter-1.4.3.61/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:696:in `block in exec_query'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/abstract_adapter.rb:280:in `block in log'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activesupport-3.2.22.5/lib/active_support/notifications/instrumenter.rb:20:in `instrument'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/abstract_adapter.rb:275:in `log'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-oracle_enhanced-adapter-1.4.3.61/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1505:in `log'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-oracle_enhanced-adapter-1.4.3.61/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:676:in `exec_query'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-oracle_enhanced-adapter-1.4.3.61/lib/active_record/connection_adapters/oracle_enhanced_adapter.rb:1457:in `select'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/abstract/database_statements.rb:18:in `select_all'
    from /usr/local/rvm/gems/ruby-2.2.2/gems/activerecord-3.2.22.5/lib/active_record/connection_adapters/abstract/query_cache.rb:63:in `select_all'

Digging into the ActiveRecord code, it seems to be going to this method:

def select_all(arel, name = nil, binds = [])
  select(to_sql(arel, binds), name, binds)
end

This suggests that my binds hash is going to the name argument, but this puzzles me further:

What is expected in the name argument?
It looks like binds is expected to be an array.

How can I use this method to take advantage of Oracle's bind variables?

Edit: It was suggested that I set the name to nil and express my binds as an array of arrays:

ActiveRecord::Base.connection.select_all(
  'select * from users where id = :test', 
  nil, 
  {test: 'foo'}.to_a
)

I see this error in return

Could not log "sql.active_record" event. NoMethodError: undefined method `name' for :test:Symbol
ActiveRecord::StatementInvalid: NoMethodError: undefined method `type' for :test:Symbol: select * from users where id = :test

nathanvda · Accepted Answer · 2018-05-10 16:22:50Z

0

The syntax might a bit weird, I guess the name is an explicit name for the prepared statement, which you can leave nil. The array is a bit weird as well, but it allows for "unnamed" bind variables.

So your statement should look as follows:

ActiveRecord::Base.connection.select_all(
  'select * from users where id = :test', 
  nil, 
  {test: 'foo'}.to_a
)

Alternatively, since you are using oracle, you could also directly use the ruby-oci8 gem (instead of the general activerecord wrapper): see the bind_param documentation where ActiveRecord::Base.connection.raw_connection is a ruby-oci8 connection. Not really sure if I would advise to do this, but I wanted to be complete, and in some cases it helps if you can use some features directly.

answered May 10, 2018 at 16:22

nathanvda

50k14 gold badges120 silver badges140 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

nathanvda Over a year ago

On which rails version are you? I see it recently got a bit more complicated: github.com/rails/rails/issues/27923

nathanvda Over a year ago

From the rails test-case I do not immediately see named bind parameters?? github.com/rails/rails/blob/… So if you really need that use ruby-oci8 directly?

AJFaraday Over a year ago

Rails 3.2.2 at the moment (my company is a little behind(“)

Collectives™ on Stack Overflow

Using oracle bind variables with select_all in Rails

1 Answer 1

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related