CODEWITHSUNDEEP
mysqlruby-on-railsrubyoracle-databaserake
Catherine
Catherine

Reputation: 95

How to establish connection from a rake task?

I need to create a rake task that gets the table rows from a MySQL database, parse the data, and insert into an Oracle database. The databases are on two different hosts.

My current attempt:

namespace :import_from_mysql do
  class MySQLConnection < ActiveRecord::Base
    self.abstract_class = true
    establish_connection({
    :adapter  => 'mysql',
    :host     => 'xxx.xxx.com',
    :database => 'sample_database',
    :username => 'username',
    :password => 'password'
    })
  end

  class MySQLTable < MySQLConnection
    self.table_name = "users"
    self.primary_key = "id"
    self.inheritance_column = "something_unique_here"
  end

  desc "Parse data before inserting to oracle database"
  task :insert_to_oracle => :environment do |t|
    puts "Rake task has begun...\n\n"
    puts "Parsing data from MYSQL...\n\n"

    MySQLTable.establish_connection

    puts "Rake task has completed!"
  end
end

But the MySQLTable.establish_connection establishes a connection to my local database which is sqlite even though I'm trying to connect to a mysql_adapter.

When I tried to establish a connection using the command below, I was able to connect to a MySQL adapter but I don't know how I can access my tables after the connection was established:

ActiveRecord::Base.establish_connection({:adapter => "mysql", :database => "sample_database", :host => "xxx.xxx.com", :username => "username", :password => "password" })

Any idea on why it keeps on connecting to sqlite? And after successfully establishing a connection to mysql, how do I select table rows after the MySQLTable.establish_connection statement?

Upvotes: 3

Views: 4771

Answers (4)

halfbit
halfbit

Reputation: 3954

although I am a noob with connections and "database back office" my trivial approach works like a charm, and there is no need with parameters in rake task that is for updating "domain tables" that contain only defining data like legal or help texts:

pre_db = ActiveRecord::Base.establish_connection('preproduction').connection

prod_db = ActiveRecord::Base.establish_connection('online').connection

and in database.yml I defined the databases. ("preproduction" is a step between dev and prod in my case and a real environment like "development". where 'online' is almost the same as 'production' (I made a different entry only for security reasons - not to destroy production if database.yml gets uploaded)

Upvotes: 1

Mounika
Mounika

Reputation: 451

Thought it might be helpful for someone else. The following worked for me!

connection = ActiveRecord::Base.establish_connection(
  :adapter  => "mysql",
  :host     => "faraway",
  :username => "myuser",
  :password => "mypass",
  :database => "somedatabase"
)

@connection = ActiveRecord::Base.connection
result = @connection.exec_query('SELECT * FROM users')

result.each do |row|
puts row
end

Here, users is an already existing table in the "somedatabase".

Upvotes: 1

wurde
wurde

Reputation: 2627

With the connection generated using ActiveRecord::Base you can execute SQL statements against whatever database you connect to. Like so:

connection = ActiveRecord::Base.establish_connection(
  :adapter  => "mysql",
  :host     => "faraway",
  :username => "myuser",
  :password => "mypass",
  :database => "somedatabase"
)
connection.execute('SELECT * FROM users')

Once established, the connection can also be referenced from ActiveRecord::Base class.

ActiveRecord::Base.establish_connection(...)
ActiveRecord::Base.connection.execute(...)

Upvotes: 2

adamliesko
adamliesko

Reputation: 1915

You can use mysql2 gem (https://github.com/brianmario/mysql2) inside your rake task.

client = Mysql2::Client.new(:host => "localhost", :username => "username", :database => "sample_database", :password => "password")
users = client.query("SELECT * FROM users")

Upvotes: 2

Related Questions