Ruby Forum Ruby on Rails > Database connections...so many connections!

Hello all.

I am currently running a very simple ruby app. It connects to an Oracle
server using the OCI8 adapter.

I have two controllers, each one connects to a different schema on the
server, I looked at the WIKI that had details of multiple database
connections
(http://wiki.rubyonrails.com/rails/pages/HowtoUseMultipleDatabases), I
moved the code from the application controller into each controller (as
I thought it was prettier / easier / simpler ) . In each controller is
the following code:

class XYZController < ApplicationController
  before_filter :change_database

  def change_database
    ActiveRecord::Base.establish_connection(
    :adapter  => "oci",
    :database => "",
    :host     => "******",
    :username => "******", # Differs between controller
    :password => "********" # Differs between controller
    )
  end

The problem is that with almost every action that performs a search a
new connection is made to the oracle server despite the fact that one
already exists for that schema (Looking at the oracle connection manager
you see 2 connections for ruby from my machine, then it keeps rising).

Does anyone know how to limit the connections to two (One for each
database, it is a low volume application). Is this a consequence of me
diverting from the code used in the WIKI?

Many thanks

Jeff

>   def change_database
>     ActiveRecord::Base.establish_connection(
>     :adapter  => "oci",
>     :database => "",
>     :host     => "******",
>     :username => "******", # Differs between controller
>     :password => "********" # Differs between controller
>     )
>   end
> 
> The problem is that with almost every action that performs a search a
> new connection is made to the oracle server despite the fact that one
> already exists for that schema (Looking at the oracle connection manager
> you see 2 connections for ruby from my machine, then it keeps rising).
> 
> Does anyone know how to limit the connections to two (One for each
> database, it is a low volume application). Is this a consequence of me
> diverting from the code used in the WIKI?
> 
> Many thanks
> 
> Jeff

To clarify one point. I seem to remember reading somewhere that if you 
use ActiveRecord::Base.establish_connection and a matching connection 
already exists in the hash of database connections then it would use 
that instead of creating a new one. Am I wrong?

Thanks

Jeff

>> 
>> Jeff
> 
> To clarify one point. I seem to remember reading somewhere that if you 
> use ActiveRecord::Base.establish_connection and a matching connection 
> already exists in the hash of database connections then it would use 
> that instead of creating a new one. Am I wrong?
> 
> Thanks
> 
> Jeff

Tried adding a remove_connection in an after_filter which to my mind 
should mean there are no connections active to the database unless 
WEBRICK is processing something but the inactive connections are still 
there.

I also tried using

  def database_connect
  unless ActiveRecord::Base.connected?
    ActiveRecord::Base.establish_connection(
    :adapter  => "oci",
    :database => "",
    :host     => "oracle7",
    :username => "vcgeneral",
    :password => "manhattan"
    )
  end
  end

But that would always return true so I can't use that as a control.

Any bright ideas to answer my relatively dim ones?

Thanks

Jeff

Ok, having done some more reading and shtuff.

It looks like I was trying to control the database connections in the 
wrong area. I was trying to control it in the controllers whereas I 
should have been using the models.

90% of my  models access one database. For that I am using the standard 
connection as setup in the YAML file.

For the one model that is in the other database I add

  unless connected?
    establish_connection(
      :adapter  => "oci",
      :database => "",
      :host     => "***",
      :username => "****",
      :password => "****"
    )
  end

I could also add this to a super class of the other models and have them 
all inherit from that just to make sure the connections are clear.

Thanks to all who read through this in hope of helping

Jeff

I't seems that the really smart ones, are able to answer their own 
questions
:).  Thanks for the thread.   One question though,  did you put this 
code in
a def in a model class or in the database.yml file?

-Larry

On 2/27/06, Jeff Jones <rurounijones@hotmail.com> wrote:
> For the one model that is in the other database I add
>
> Rails mailing list
> Rails@lists.rubyonrails.org
> http://lists.rubyonrails.org/mailman/listinfo/rails
>



--
Best Regards,
-Larry
"Work, work, work...there is no satisfactory alternative."
            --- E.Taft Benson

Larry Kelly wrote:
> I't seems that the really smart ones, are able to answer their own 
> questions
> :).  Thanks for the thread.   One question though,  did you put this 
> code in
> a def in a model class or in the database.yml file?
> 
> -Larry
> 
> On 2/27/06, Jeff Jones <rurounijones@hotmail.com> wrote:
>> For the one model that is in the other database I add
>>
>> Rails mailing list
>> Rails@lists.rubyonrails.org
>> http://lists.rubyonrails.org/mailman/listinfo/rails
>>
> 
> 
> 
> --
> Best Regards,
> -Larry
> "Work, work, work...there is no satisfactory alternative."
>             --- E.Taft Benson

Smart? No, sheer bloody mindedness and borderline psychotic inability to 
let a computer get the best of me?...oh yes, I showed it!

This code was in the model.

Class news < ActiveRecord::Base

  unless connected?
    establish_connection(
      :adapter  => "oci",
      :database => "",
      :host     => "***",
      :username => "****",
      :password => "****"
    )
  end

  def index
  end

etc.

end

Any model that doesn't specifically have an establish connection like 
that chunk code will use what is in the database.yml file which I have 
setup like a normal project ( translation, I did whatever was in the 
tutorials ;) ).

Jeff