How to execute a raw update sql with dynamic binding in rails

Question

I want to execute one update raw sql like below:

update table set f1=? where f2=? and f3=? 

This SQL will be executed by ActiveRecord::Base.connection.execute, but I don't know how to pass the dynamic parameter values into the method.

Could someone give me any help on it?

Answer 1

It doesn't look like the Rails API exposes methods to do this generically. You could try accessing the underlying connection and using it's methods, e.g. for MySQL:

st = ActiveRecord::Base.connection.raw_connection.prepare("update table set f1=? where f2=? and f3=?") st.execute(f1, f2, f3) st.close 

I'm not sure if there are other ramifications to doing this (connections left open, etc). I would trace the Rails code for a normal update to see what it's doing aside from the actual query.

Using prepared queries can save you a small amount of time in the database, but unless you're doing this a million times in a row, you'd probably be better off just building the update with normal Ruby substitution, e.g.

ActiveRecord::Base.connection.execute("update table set f1=#{ActiveRecord::Base.sanitize(f1)}") 

or using ActiveRecord like the commenters said.

Answer 2

ActiveRecord::Base.connection has a quote method that takes a string value (and optionally the column object). So you can say this:

ActiveRecord::Base.connection.execute(<<-EOQ)   UPDATE  foo   SET     bar = #{ActiveRecord::Base.connection.quote(baz)} EOQ 

Note if you're in a Rails migration or an ActiveRecord object you can shorten that to:

connection.execute(<<-EOQ)   UPDATE  foo   SET     bar = #{connection.quote(baz)} EOQ 

UPDATE: As @kolen points out, you should use exec_update instead. This will handle the quoting for you and also avoid leaking memory. The signature works a bit differently though:

connection.exec_update(<<-EOQ, "SQL", [[nil, baz]])   UPDATE  foo   SET     bar = $1 EOQ 

Here the last param is a array of tuples representing bind parameters. In each tuple, the first entry is the column type and the second is the value. You can give nil for the column type and Rails will usually do the right thing though.

There are also exec_query, exec_insert, and exec_delete, depending on what you need.