在 Rails 中使用 oracle 将变量与 select_all 绑定

Question

我正在尝试利用Ruby on Rails中的绑定变量，它不需要实例化模型，所以我使用这样的select_all

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

但是我得到了这个很大的旧错误：

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代码，它似乎要采用此方法：

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

这表明我的binds哈希将进入name参数，但这让我更加困惑：

• name论点中有什么期望？
• 看起来binds应该是一个数组。

如何使用此方法来利用 Oracle 的绑定变量？

---

编辑：有人建议我将name设置为nil并将我的绑定表示为数组数组：

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

Answer 1

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

因此，您的语句应如下所示：

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

或者，由于您使用的是 oracle，您也可以直接使用ruby-oci8gem(而不是通用的活动记录包装器(：请参阅bind_param文档 其中ActiveRecord::Base.connection.raw_connection是 Ruby-OCI8 连接。不太确定我是否会建议这样做，但我希望完整，在某些情况下，如果您可以直接使用某些功能，它会有所帮助。