# Multiple database in the same actions?

**URL:** <https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840>\
**Category:** Rails\
**Created:** [January 19, 2006, 5:51pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840 "2006-01-19T17:51:22Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sayoyo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ba8739/32.png) [@sayoyo](https://www.ruby-forum.com/u/sayoyo)\
**Post date:** [January 19, 2006, 5:51pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840/1 "2006-01-19T17:51:22Z")

</div>

Hi,

I read the example in  
[Peak Obsession](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

> **[Yahoo Mail - Organized Email](https://mail.yahoo.com)**
>
> It's time to get stuff done with Yahoo Mail. Just add your Gmail, Outlook, AOL or Yahoo Mail to get going. We automatically organize all the things life throws at you, like receipts and attachments, so you can find what you need fast. Plus, we've got...

---

<div class="post-metadata">

**Author:** ![sayoyo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ba8739/32.png) [@sayoyo](https://www.ruby-forum.com/u/sayoyo)\
**Post date:** [January 19, 2006, 8:07pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840/2 "2006-01-19T20:07:22Z")

</div>

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

> **[Damon Clinkscales](https://damonc.onuniverse.com/)**
>
> Damon Clinkscales

---

<div class="post-metadata">

**Author:** ![sayoyo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ba8739/32.png) [@sayoyo](https://www.ruby-forum.com/u/sayoyo)\
**Post date:** [January 19, 2006, 8:55pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840/3 "2006-01-19T20:55:37Z")

</div>

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](mailto:removed_email_address@domain.invalid) wrote:

> databases? Or, two different  
> model classes, two different databases?
> 
> -damon  
> [http://damonclinkscales.com/](http://damonclinkscales.com/)
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

* * *

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

> **[\[object](https://mail.yahoo.com/)**
>
> \[object

---

<div class="post-metadata">

**Author:** ![sayoyo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ba8739/32.png) [@sayoyo](https://www.ruby-forum.com/u/sayoyo)\
**Post date:** [January 20, 2006, 4:10pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840/4 "2006-01-20T16:10:06Z")

</div>

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

> **[Damon Clinkscales](https://damonc.onuniverse.com/)**
>
> Damon Clinkscales

---

<div class="post-metadata">

**Author:** ![sayoyo](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/ba8739/32.png) [@sayoyo](https://www.ruby-forum.com/u/sayoyo)\
**Post date:** [January 20, 2006, 9:03pm UTC](https://www.ruby-forum.com/t/multiple-database-in-the-same-actions/52840/5 "2006-01-20T21:03:58Z")

</div>

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](mailto: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/](http://www.ruby-forum.com/).
> 
> * * *
> 
> Rails mailing list  
> removed\_email\_address@domain.invalid  
> [http://lists.rubyonrails.org/mailman/listinfo/rails](http://lists.rubyonrails.org/mailman/listinfo/rails)

* * *

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

> **[\[object](https://mail.yahoo.com/)**
>
> \[object