Forum: Ruby on Rails multiple database in the same actions?

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
sayoyo S. (Guest)
on 2006-01-19 18:51
(Received via mailing list)
Hi,

I read the example in
http://wiki.rubyonrails.com/rails/pages/HowtoUseMu...,
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
Damon C. (Guest)
on 2006-01-19 21:07
(Received via mailing list)
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
http://damonclinkscales.com/
sayoyo S. (Guest)
on 2006-01-19 21:55
(Received via mailing list)
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. <removed_email_address@domain.invalid> wrote:

> databases? Or, two different
> model classes, two different databases?
>
> -damon
> http://damonclinkscales.com/
>
> _______________________________________________
> Rails mailing list
> removed_email_address@domain.invalid
> 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
Damon C. (Guest)
on 2006-01-20 17:10
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/
sayoyo S. (Guest)
on 2006-01-20 22:03
(Received via mailing list)
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. <removed_email_address@domain.invalid> 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
> removed_email_address@domain.invalid
> 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
This topic is locked and can not be replied to.