vendredi 24 avril 2015

How do I pass a variable into a prepared statement in Ruby?

I have a method in Ruby to query a database and print out some data, and I'm trying to use a prepared statement instead.

Here's the functioning method without the prepared statement:

def print_state_speakers(*states)
  puts "STATE SPEAKERS"
  state_string = "'#{states.*"', '"}'"
  state_speakers = $db.execute("
    SELECT name, location 
    FROM congress_members 
    WHERE location IN (#{state_string})
    ORDER BY location")
  state_speakers.each { |rep, location| puts "#{rep} - #{location}" }
end

Here's my attempt at the same method using a prepared statement:

def print_state_speakers(*states)
  puts "STATE SPEAKERS"
  state_string = "'#{states.*"', '"}'"
  begin
    pst = $db.prepare "SELECT name, location 
    FROM congress_members 
    WHERE location IN (?)
    ORDER BY location"
    state_speakers = pst.execute state_string
  end
  state_speakers.each { |rep, location| puts "#{rep} - #{location}" }
end

Here's where I call the method:

 print_state_speakers('NJ', 'NY' , 'ME', 'FL', 'AK')

When I run the file with the 1st method it shows the data, when I use the 2nd, it shows nothing. It doesn't throw an error. I feel like the syntax needs to be different to account for the string being passed in, but I've been searching online and messing around with it for a while and can't get it to work. Any insight as to how to fix the prepared statement would be appreciated.

Aucun commentaire:

Enregistrer un commentaire