Multiple database in the same actions?

Rails
1

Hi,

I read the example in
Peak Obsession,
it show us how to connect to other database, each time
we start a new action, however, this doesn’t work
while you try to connect to two different database
within the same action.

SO I wonder is it possible to bind to two or more
database within the same action???

Thanks you very much

Saiho

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around

2

Saiho Y. <sayoyo@…> writes:

SO I wonder is it possible to bind to two or more
database within the same action???

Saiho,

Yes. It’s not typical to do this, but it can be done.

What’s the scenario? Same model class, different databases? Or, two
different
model classes, two different databases?

-damon

3

Hi, Damon

Actually, we try to create a “generic”
activerecord::base class that can be bind to different
databases within the same action. It means the same
subclass of activerecord::base can link(and connect)
to different tables of different database at runtime.
Insteads of predefine the (model)class with the
scarffold. Here is an example of what we are trying to
do:

class GentestController < ApplicationController
def index
#1st connection
GenTableAs.establish_connection(:adapter =>
“postgresql”, :host => “1.2.3.4”, :port => 5432,
:database => “db1”, :username => “user1”, :password =>
“pwd1”) @col1 = GenTableAs.retrieve_connection
GenTableAs.set_table_name “Table1”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@colnames1 = obj1.class.column_names()
obj1 = nil
GenTableAs.remove_connection()

# 2nd connection
GenTableAs.establish_connection(:adapter =>

“postgresql”,:host => “1.5.3.5”,:port =>
5432,:database => “db2”,:username => “user2”,
:password => “user2”)
@col2 = GenTableAs.retrieve_connection
GenTableAs.set_table_name “table2”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@col1 = obj1.class.column_names()
obj1[“colaa”] = 102
obj1[“colab”] = “102”
obj1.save
end
end

If it is possible to link with two databases within
the same action, how can we do this???

Thanks you very much for your help!!!

Saiho

— Damon C. [email protected] wrote:

databases? Or, two different
model classes, two different databases?

-damon
http://damonclinkscales.com/

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around

4

Saiho sayoyo wrote:

class GentestController < ApplicationController
def index
#1st connection
GenTableAs.establish_connection(:adapter =>
“postgresql”, :host => “1.2.3.4”, :port => 5432,
:database => “db1”, :username => “user1”, :password =>
“pwd1”) @col1 = GenTableAs.retrieve_connection
GenTableAs.set_table_name “Table1”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@colnames1 = obj1.class.column_names()
obj1 = nil
GenTableAs.remove_connection()

  # 2nd connection

GenTableAs.establish_connection(:adapter =>
“postgresql”,:host => “1.5.3.5”,:port =>
5432,:database => “db2”,:username => “user2”,
:password => “user2”)
@col2 = GenTableAs.retrieve_connection
GenTableAs.set_table_name “table2”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@col1 = obj1.class.column_names()
obj1[“colaa”] = 102
obj1[“colab”] = “102”
obj1.save
end
end

Saiho,

Leaving things pretty much as you had them, this works for me. I
removed some unnecessary statemetnts. Also, I changed the database to
mysql and am running it as a script for testing.

Here’s the code:
----script/gentest----
#!/usr/bin/env ruby
require File.dirname(FILE) + ‘/…/config/boot’

class GenTest #< ApplicationController
def GenTest.index
#1st connection - saiho1
GenTableAs.establish_connection(:adapter =>
“mysql”, :host => “localhost”, :port => 3306,
:database => “saiho1”, :username => “root”, :password => “”)
GenTableAs.set_table_name “Table1”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@colnames1 = obj1.class.column_names()
puts @colnames1
GenTableAs.remove_connection()
puts ‘–’
# 2nd connection - saiho2
GenTableAs.establish_connection(:adapter =>
“mysql”,:host => “localhost”,:port =>
3306,:database => “saiho2”,:username => “root”,
:password => “”)
GenTableAs.set_table_name “table2”
GenTableAs.reset_column_information()
obj1 = GenTableAs.new
@colnames1 = obj1.class.column_names()
puts @colnames1
obj1[“bar”] = “baz”
obj1.save
obj1 = GenTableAs.find_by_bar(“baz”)
puts obj1.inspect
GenTableAs.remove_connection()
end
end
GenTest.index

----saiho1.sql----
CREATE TABLE table1 (
id int(11) NOT NULL auto_increment,
foo varchar(10) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

----saiho2.sql----
CREATE TABLE table2 (
id int(11) NOT NULL auto_increment,
bar varchar(10) default NULL,
PRIMARY KEY (id)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

----A sample run----
id
foo

id
bar
#<GenTableAs:0x23d9250 @attributes={“id”=>“1”, “bar”=>“baz”}>

Honestly, I wouldn’t want to re-establish this connection all the time,
but I’m not really sure how dynamic this is going to be. For example,
is it 2 databases or will there be 100 databases? If you are going to
be reusing connections, you should stash them somewhere and then make
them available with connection=.

HTH,
-damon

5

Hi Damon,

I have try your code, but I got an error(I think it is
an error, it may be not) here is:

#<GenTableAs:0x2aaaac4bc110
@errors=#<ActiveRecord::Errors:0x2aaaac4b9f00
@base=#<GenTableAs:0x2aaaac4bc110 …>, @errors={}>,
@attributes={“colaa”=>102, “id”=>10, “colab”=>“102”},
@new_record=false>

and when I execute the same code inside a
controller/action I got the following error:

ActiveRecord::StatementInvalid in Gentest#index3

ERROR: relation “gen_table_as” does not exist
: SELECT a.attname,
format_type(a.atttypid, a.atttypmod), d.adsrc,
a.attnotnull
FROM pg_attribute a LEFT JOIN pg_attrdef
d
ON a.attrelid = d.adrelid AND a.attnum
= d.adnum
WHERE a.attrelid =
‘gen_table_as’::regclass
AND a.attnum > 0 AND NOT a.attisdropped
ORDER BY a.attnum

RAILS_ROOT: .
Application Trace | Framework Trace | Full Trace

#{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/connection_adapters/abstract_adapter.rb:67:in
log' #{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/connection_adapters/postgresql_adapter.rb:109:in query’
#{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/connection_adapters/postgresql_adapter.rb:377:in
column_definitions' #{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/connection_adapters/postgresql_adapter.rb:182:in columns’
#{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/base.rb:708:in
columns' #{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/base.rb:1565:in attributes_from_column_definition’
#{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/base.rb:1104:in
initialize_without_callbacks' #{RAILS_ROOT}usr/local/lib/ruby/gems/1.8/gems/activerecord-1.12.2/lib/active_record/callbacks.rb:236:in initialize’
#{RAILS_ROOT}/app/controllers/gentest_controller.rb:51:in
`index3’

Do you have any Idea?

ny the way, which version of Ruby-rails that you are
using?
I’m using
The using version ruby-rails are :
Rails 0.14.2 and ruby 1.8.3 (2005-09-21)
[x86_64-linux]
We are running on Linux KDE 3.4, and postgresql

Thanks you very much for your helps!!!

Saiho

— Damon C. [email protected] wrote:

GenTableAs.retrieve_connection

5432,:database => “db2”,:username => “user2”,
end
----script/gentest----
GenTableAs.set_table_name “Table1”
:password => “”)
end

id
but I’m not really sure how dynamic this is going to

–
Posted via http://www.ruby-forum.com/.

Rails mailing list
[email protected]
http://lists.rubyonrails.org/mailman/listinfo/rails

Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around