Menu
For performance reasons, I need to write a new method in my Rails model that executes some arbitrary SQL:
UPDATE table
SET col1 = ? AND col2 = ?
WHERE id = ?
I understand I can use ActiveRecord::Base.connection.execute or ActiveRecord::Base.connection.update with a string of SQL to get the results I need, but what is the proper procedure for substituting the parameter placeholders (?) with the actual parameter values? Is there a Rails method for interpolating parameters into a SQL statement, or should it just be done by manual interpolation? The latter seems unsafe...
464
Declare statements start with the keyword DECLARE , followed by the name of the parameter (starting with a question mark) followed by the type of the parameter and an optional default value. The default value must be a literal value, either STRING , NUMERIC , BOOLEAN , DATE , or TIME .
Parameterized SQL queries allow you to place parameters in an SQL query instead of a constant value. A parameter takes a value only when the query is executed, which allows the query to be reused with different values and for different purposes.
Parameterized queries are generally the safest and most efficient way to pass user defined values in a query, however not every database driver supports them.
You could also do this:
updates = ActiveRecord::Base.send(:sanitize_sql_array, ["name = ? and category = ?", name, category])
ActiveRecord::Base.connection.execute("update table set #{updates} where id = #{id.to_s.to_i}")
to_s is being called on id before to_i in case it's nil.
74
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
