2

I am using mysql2 adaptor with activerecords in rails 3.2.6

I would like to use bind variables while doing a raw insert. I can not use a regular model since I am doing an insert into select.

Something like:

insert into t(col1, col2)
select ?, c
from t1
where t1.x = ?

I am new to Rails and was surprised how hard it is to do this. I understand this is not a regular rails convention of using Model classes etc. I would like to use bind variables for performance as well as security reasons. Ideally I do not want to use raw_connection as specified at How to execute a raw update sql with dynamic binding in rails

Community
  • 1
  • 1
serverman
  • 1,314
  • 5
  • 22
  • 39

1 Answers1

1

Try this:

query = <<-SQL
insert into t(col1, col2)
select ?, c
from t1
where t1.x = ?
SQL

col1 = Model.columns.find{|x| x.name == 'col1'}
col2 = Model.columns.find{|x| x.name == 'col2'}

ActiveRecord::Base.connection.insert(query, nil, nil, nil, nil, [[col1, col1_value], [col2, col2_value]])

My source for how to make binds: https://github.com/rails/rails/blob/master/activerecord/test/cases/bind_parameter_test.rb

My source for how to do an insert: http://api.rubyonrails.org/classes/ActiveRecord/ConnectionAdapters/DatabaseStatements.html#method-i-insert

Taylor Williams
  • 256
  • 4
  • 22