I have a bunch of records found with this SQL
select id from employee_holiday_years where start_date = '2015-01-01' and employee_id in(select id from employees where join_date > '2014-12-31') and allowance_from_last_year <> 0;
I need to iterate through the records returned by this SQL using Ruby and run a couple of Ruby commands against each one. Is this possible?
By ruby you can try this lets you have mysql adapter and ruby installed Also you have
Database Name TESTDB
username testuser
password test123
host: localhost
require "dbi"
begin
dbh = DBI.connect("DBI:Mysql:TESTDB:localhost",
"testuser", "test123")
sth = dbh.prepare("select id from employee_holiday_years where start_date = '2015-01-01' and employee_id in(select id from employees where join_date > '2014-12-31') and allowance_from_last_year <> 0;")
sth.execute(1000)
#iterate through the return records
sth.fetch do |row|
printf row[0], row[1] #to print the row
end
sth.finish
rescue DBI::DatabaseError => e
puts "An error occurred"
puts "Error code: #{e.err}"
puts "Error message: #{e.errstr}"
ensure
# disconnect from server
dbh.disconnect if dbh
end
For more Ruby Database Access
sql return Array so use Array iteration.
e.g
recoreds.each do |record|
p record.inspect
end
This is what I came up with
client = Mysql2::Client.new(:host => "localhost", :username => "root", :database => "mydatabase")
results = client.query("select * from mytable where...", :symbolise_keys => true)
results.each do |row|
do stuff...
end