Rails - Jan 2006 - multiple database in the same actions?

Hi, 

I read the example in
http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases,
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 
http://mail.yahoo.com

Saiho Yuen <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
http://damonclinkscales.com/

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 Clinkscales <scales@pobox.com> wrote:
> Saiho Yuen <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
> http://damonclinkscales.com/
> 
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com

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
http://damonclinkscales.com/

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

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 Clinkscales <scales@pobox.com> wrote:
> 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
> http://damonclinkscales.com/
> 
> -- 
> Posted via http://www.ruby-forum.com/.
> _______________________________________________
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
> 

__________________________________________________
Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
http://mail.yahoo.com