在Rails中将oracle绑定变量与select_all一起使用 [英] Using oracle bind variables with select_all in Rails

问题描述

我试图利用Ruby on Rails中的绑定变量，并且不需要实例化模型，所以我像这样使用select_all

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'

深入研究ActiveRecord代码，似乎要使用此方法:

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

这表明我的binds哈希将用于name参数，但这进一步困扰了我:

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

• name自变量期望什么?
• 看起来binds应该是一个数组.

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

如何使用这种方法来利用Oracle的绑定变量?

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

建议将name设置为nil并将绑定表示为数组数组:

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
)

我看到了这个错误

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

推荐答案

语法可能有点怪异，我想name是准备好的语句的显式名称，您可以保留nil.数组也有点奇怪，但是它允许未命名"的绑定变量.

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
)

或者，由于您使用的是oracle，因此您也可以直接使用ruby-oci8 gem(而不是常规的activerecord包装器):请参见

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.

这篇关于在Rails中将oracle绑定变量与select_all一起使用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！