How do you speak SQL directly with MySQL from Ruby?

Question

I want to write a script in Ruby to clear some corrupted keys in multiple instances of the same MySQL schema. I would like to do something like SHOW CREATE TABLE, and then see what is returned and delete the keys, if they exist.

I know that in a Rails environment you can do this ...

ActiveRecord::Base.connection.execute( some sql ) 

But you will return to the Result object. For this task, I need a line so that I can parse it and act accordingly.

Answer 1

You can check mysql-ruby gem.

Here's a record on how to use it: Using the MySQL Ruby Module

Learn more through google

Answer 2

This should help you:

 >> result = ActiveRecord::Base.connection.execute("SHOW TABLES") => #<Mysql::Result:0x37ecb30> >> result.class.instance_methods - Object.instance_methods => ["all_hashes", "field_seek", "row_tell", "fetch_field_direct", "free", "field_tell", "fetch_lengths", "num_fields", "data_seek", "fetch_row", "num_rows", "fetch_field", "each", "each_hash", "fetch_hash", "row_seek", "fetch_fields"] 

Look at #all_hashes in an instance of MySql :: Result

Answer 3

I would use the mysql-ruby gem and you would do something like this:

 require 'mysql' m = MySQL.new("localhost", "username", "password", "database") r = m.query("SELECT * FROM people ORDER BY name") r.each_hash do |f| print "#{f['name']} - #{f['email']}" end 

Answer 4

If you do not want to use ActiveRecord, ORM may be a little more complicated for your use right now), you can still use the ruby-mysql library or even better IMHO - use the Ruby DBI / DBD library ( here ), which has DBD drivers for mysql and postgresql out of the box.

So you can issue direct SQL statements like this

 require "dbi" require "dbi/dbrc" # == Configuration DB = "sympa" HOST = "saphir" cnt = 0 dup = 0 # == Crude option processing # list_name = ARGV.shift.to_s file = ARGV.shift.to_s db = DBI::DBRC.new(DB) DBI.connect(db.dsn + ":#{HOST}", db.user, db.password) do |dbh| date = Time.now.asctime if not list_name or list_name == "" then puts "List name is mandatory" exit 1 end req1 = <<-"EOR" insert into user_table (email_user,lang_user) values (?, ?) EOR ... req2 = <<-"EOR" insert into subscriber_table (user_subscriber, list_subscriber, visibility_subscriber, date_subscriber, reception_subscriber) values (?, ?, ?, NOW(), ?) EOR sth1 = dbh.prepare(req1) sth2 = dbh.prepare(req2) ... # # Insertion in user_table # begin sth1.execute(line, "en") cnt += 1 rescue DBI::DatabaseError => err $stderr.puts("DBI: #{err}") end 

dbi / dbrc is a useful module that allows you not to enter the login and password directly into the script. See there .

Answer 5

There is probably a better way to do this programmatically, however, if you really want to manage interactive commands and analyze the results, then expect might be more appropriate. You can still expect a ruby ​​script.

Answer 6

Use mysql2

Update this thread: now I suggest Mysql2: http://rubygems.org/gems/mysql2