How to perform raw sql update with dynamic snapping in rails

Question

I want to perform one raw sql update as below:

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

This SQL will be executed by ActiveRecord::Base.connection.execute , but I do not know how to pass the values ​​of dynamic parameters to the method.

Can anyone help me?

Answer 1

The Rails API does not seem to provide methods for this in general. You can try to access the base connection and use its methods, for example. 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 am not sure if there are other consequences to this (connections remain open, etc.). I would follow the Rails code for a normal update to see what it does besides the actual request.

Using prepared queries can save you a small amount of time in the database, but if you do it a million times in a row, you probably would be better off just creating an update with a regular Ruby replacement, for example.

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

or using ActiveRecord, as commentators noted.

Answer 2

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

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

Note that if you use a Rails migration or an ActiveRecord object, you can shorten it to:

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

Answer 3

You should just use something like:

 YourModel.update_all( ActiveRecord::Base.send(:sanitize_sql_for_assignment, {:value => "'wow'"}) ) 

That would do the trick. Using the ActiveRecord :: Base # send method to call sanitize_sql_for_assignment makes Ruby (at least version 1.8.7) skip the fact that sanitize_sql_for_assignment strong> is actually a protected method.

Answer 4

Someday it would be better to use the name of the parent class instead of the table name:

 # Refers to the current class self.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp) 

For example, the base class "Man", subclasses (and database tables) "Client" and "Seller", Instead, use:

 Client.where(self.class.primary_key => id).update_all(created _at: timestamp) Seller.where(self.class.primary_key => id).update_all(created _at: timestamp) 

You can use the base class object as follows:

 person.class.unscoped.where(self.class.primary_key => id).update_all(created _at: timestamp) 

Answer 5

I needed to use raw sql because I was unable to get compound_parameter_functions for working with activerecord 2.3.8. Therefore, raw sql is required to access the sqlserver 2000 table with a composite primary key.

 sql = "update [db].[dbo].[#{Contacts.table_name}] " + "set [COLUMN] = 0 " + "where [CLIENT_ID] = '#{contact.CLIENT_ID}' and CONTACT_ID = '#{contact.CONTACT_ID}'" st = ActiveRecord::Base.connection.raw_connection.prepare(sql) st.execute 

If the best solution is available, share it.

Answer 6

In Rails 3.1, you should use the request interface:

• new (attributes)
• create (attributes)
• create! (Attributes)
• find (id_or_array)
• destroy (id_or_array)
• destroy_all
• delete (id_or_array)
• delete_all
• update (ids, updates)
• update_all (updates)
• exist?

update and update_all are a necessary operation.

For more details see: http://m.onkey.org/active-record-query-interface