I am creating a find by sql method to update various tables, but when
the values get inserted into the script there are surrounded by single
quotes, is there a way around this to get it to just enter in the
string?
The command
MassEffect.find_by_sql(['update ? set ? = ? where id = ? ',
a.table_name, me.attribute, me.new_value ,me.record])
and it issues this to my sql
update 'team_members' set 'permission_type_id' = '4' where id = 1 ;
and im looking to get
update team_members set permission_type_id = '4' where id = 1 ;
all help is appreciated
White Wizzard
You would need to hand craft the where clause leaving the ?'s in for the
values only. The ?'s (placeholders) are only for values and will always
get properly escaped.
White Wizzard wrote:
Hi all,
The problem with just creating the where clause is that I need to do
many updates on different tables is there any way i could force them
first two ? to be escaped?
White Wizzard
This will work as long as you are certain that there is no possibility of a sql injection:
MassEffect.find_by_sql(["update #{a.table_name} set #{me.attribute} = ?
where id = ? ", me.new_value ,me.record])
White Wizzard wrote:
Why use find_by_sql to do an update when that's exactly what update_all does ?
Fred
Update_all works on the main table in my project mass_effect,
mass_effect contains a tablename, table_column, a record number and
the new value of the table.
mass_effect.update_all would update everything in mass effect table
when I need to update everything based on what is in this table.
def mass_effect_update
MassEffect.find(:all).each{ |me|
a = Model.find(:first,:conditions => ['id = ?', me.model_id])
sql = "update #{a.table_name} set #{me.attribute} =
#{me.new_value} where id = #{me.record}"
ActiveRecord::Base.connection.update(sql, "MASS EFFECT
#{a.table_name} Update")
}
end
this is what I finally came up with it basically does this . ..
for every record in the mass_effect table
get the corresponding model name using the model id
update that table at the attribute - value at the record
getting that information from the mass_effect table
connect to the database and run the command, putting
"MASS EFFECT tablename Update" in the logge
end of loop
I know that there is probably not the best way to do this and there is
some code cleaning that needs to be done but thats what I have and it
works.
Hi all,
I eneded up using Base.connect to directly talk to the database, and
I did escape the values that needed to be "in the set and where
clause" and left the ones that needed not to have quotes alone.
White Wizzard