Topic: Experts Please Help! Connecting to Multiple Databases!

Help please!  Big time Rails newbie smack in the middle of trying to make production level changes...long story...

So I'm trying to connect to two different databases, within one application...  I've read on how to do this, but it only gets me so far (as in assuming I know what I'm doing!  Ha!)...

The object is to validate if a user exists in, first, the prime database, and, if not, check to see if they exist in the legacy database...at which point the member is redirected to a partner site.

I have all of this working specific to the prime database, it's when I then try to validate in the legacy database that the wheels fall off the wagon.

So I've created a class as follows:
class LegacyDB < ActiveRecord::Base

  self.abstract_class = true

  config = YAML::load(File.open(

Re: Experts Please Help! Connecting to Multiple Databases!

I would create a new model specifically for the legacy database. I think you are heading for a lot of pain if try to do this all in one model
To set up a new model you could descend it from the model that attaches to the main database thereby inheriting all the logic and just set up the database connection. I did a short tutorial on this a while back here http://railsforum.com/viewtopic.php?id=42143

Then if your main user fails to validate you can call the validation on the legacy model. Should keep things a lot cleaner.
Hope that helps

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

Thanks!  I think that is what I did, but I created the 'model', and, being a newbie, I'm not sure if that would normally be generated by Rails...

Still failing, would you mind taking a look at the following and see if you see what I'm doing wrong?

First, this is the 'legacy' database model for the second database that I want to connect to in the existing application (Note that doing the 'Fileload' was the only way I could get this to not error out):


class MMSDB < ActiveRecord::Base

  self.abstract_class = true #important!

  config = YAML::load(File.open(

Re: Experts Please Help! Connecting to Multiple Databases!

Your post has been truncated but the first thought is that your class name is wrong
Rails conventions state that the class name should be cammel cased after the file name
So in your case

class MMSDB < ActiveRecord::Base

should be

class Mmsdb < ActiveRecord::Base

Assuming that the file this code belongs in is called
mmsdb.rb

Secondly I was trying to point you in the direction of this class being descended from the user class, so I would call it legacy_user.rb and descend it from the User model. Does that sound right to you? It's difficult to real;ly advise with so little information.


I would then use establish_connection "foreign_#{Rails.env}" in the legacy user model replacing "foreign_" with whatever you have in your database.yml settings for the legacy database connector, maybe call it legacy_#{Rails.env}

You shouldn't need to load any yaml files. The database.yml file is initialised automatically by Rails.

If you get any specific errors then post the stack trace from your log file

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

Thanks!

Let's make sure I'm tracking:

So this is the first model/class:

class Mmsdb < ActiveRecord::Base

  self.abstract_class = true #important!

  #config = YAML::load(File.open("#{RAILS_ROOT}/config/database.yml"))

  establish_connection deals_#{Rails.env}
 
end

Re: Experts Please Help! Connecting to Multiple Databases!

Sorry doing this is separate replies so that it doesn't get truncated.

This is the second model / class, which calls the Mmsdb class

class Redirect < Mmsdb
  def initialize
   
  end

set_table_name 'members'

def email_exists?(email)
    if find_by_email(email)
       user = find_by_email(email)
       user.redirect_count += 1
       user.save
    end
end

end

Re: Experts Please Help! Connecting to Multiple Databases!

...and this is the code snippet in the 'Account' Controller that is checking to ensure that the user, via email address, doesn't exist in the 'Legacy' aka 'deals' database:

else
            if Redirect::User.email_exists?(@email)
                #@Redirect_Flag = [0,1,2].include? user.redirect_count
                @Costco_Flag = true
                @Redirect_Flag = true
            else
                flash.now[:error] = 'Invalid email or password; please try again.'
            end
        end

I haven't tried these changes yet, because, being the newest of newbies, I'm assuming that I probably have some other issues!  Ha!

Re: Experts Please Help! Connecting to Multiple Databases!

Just ran it, and this is the error that was kicked back:  'undefined local variable or method `deals_' for Mmsdb(abstract):Class'.

I'm going to review, made these changes quickly looking for immediate gratification!  Have been working on this far too long, even for a newbie, and pressure is mounting!

Re: Experts Please Help! Connecting to Multiple Databases!

First error was a simple syntax error, this next one is one that I've encountered before and 'fixed it' by loading the database.yml file...

There appears to be a database problem.
Your config/database.yml may not be written correctly. Please check it and fix any errors.
Your database schema may be out of date or nonexistant. Please run rake db:migrate to ensure that the database schema is up-to-date.
The database server may not be running. Please check whether it's running, and start it if it isn't.
Error message:
deals_qa database is not configured (ActiveRecord::AdapterNotSpecified)

Here is the section of the database.yml file:

deals_qa:
  adapter: mysql
  encoding: utf8
  reconnect: true
  database: deals_qa
  username: name
  password: password
  host: host ip address
  port: port number

Re: Experts Please Help! Connecting to Multiple Databases!

Can you post the complete contents of your database.yml (*** out your user names and pwds)

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

Sure...let me clean it up and then I will post it.  In the meantime, I started with a brand-new class (just to be clean) and 'hard-coded' the config as follows:

# To change this template, choose Tools | Templates
# and open the template in the editor.

class Mms_connect < ActiveRecord::Base
 
  def initialize
   
  end

  self.abstract_class = true #important!

  conn = { :adapter => 'mysql',
    :encoding => 'utf8',
    :reconnect => 'true',
    :database => 'deals_qa',
    :username => 'username',
    :password => 'password'
  }

  establish_connection(conn)

end

Re: Experts Please Help! Connecting to Multiple Databases!

...and received the error:

Error message:
superclass must be a Class (Module given)

These are the possible causes:

There may be a syntax error in the application's code. Please check for such errors and fix them.

A required library may not installed. Please install all libraries that this application requires.

The application may not be properly configured. Please check whether all configuration files are written correctly, fix any incorrect configurations, and restart this application.

A service that the application relies on (such as the database server or the Ferret search engine server) may not have been started. Please start that service.

Further information about the error may have been written to the application's log file. Please check it in order to analyse the problem.

Re: Experts Please Help! Connecting to Multiple Databases!

Can you post the stack trace from your log file so we can see what the error actually is and where it is

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

...and this is the corresponding code snippet in the existing Account Controller file, file name Account_Controller.rb. Note that I'm just trying to get past the first condition.

if Dealsdb::Members.email_exists?(@email)
            @Redirect_Flag = true
        else
            flash.now[:error] = 'Didnt MMS database check didnt work'
        end
Thank You!

Re: Experts Please Help! Connecting to Multiple Databases!

So I've made changes, based on some additional research.  Note that I could not get the application to read the 'deals_qa' environment from the database.yml, or at least this is how it appeared.

Right now, the end result is that it's sending me to an Application generated 'Oops' page, which, I'm assuming, was created to account for any errors, so nothing specific as far as why this isn't working.

Please review and provide any suggestions, or other things that I should could be looking at to get this working... Thanks!

Here is the code changes based on previous recommendation:

This is the new model file, named 'dealsdb':

module Dealsdb

@abstract_class = true

conn = { :adapter => 'mysql',
:encoding => 'utf8',
:reconnect => 'true',
:database => 'deals_qa',
:username => 'xxxxxx',
:password => 'xxxxxx',
:host => 'xx.xx.xx.xx',
:port => 'xxxx'
}

establish_connection(conn["deals_qa"])

end
end

class Members < Dealsdb::Base

  def email_exists?(email)
    unless email.blank? || SYSTEM_EMAILS.include?(email)
    returning find_by_email(email) do |user|
    end
    end
  end
end

Re: Experts Please Help! Connecting to Multiple Databases!

I can't tell what is going on until; you post the stack trace from your log file. This will at least give me enough information to be able to start asking you to show me the right bits of code that are causing you problems. your model doesn't look right to me and your database.yml file isn;t complete, nor does it seem to have the right environent sections for the connections.
you are only giving me a small part of the picture that I need in order to be able to give you a clear and concise solution so please can you

1) Show me the FULL stack traces from your log file detailiong the errors you are getting (That's 3 times I've asked for this and it is impossible to progress effectively without this.
2) show me the full class definitions for the models involved
3) Tell me what the table names are in the relative databses for the models

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

Apologies, not trying to ignore or frustrate you...  Trust me I do understand, frustrated as well having been pulled into this without knowledge or experience...

So I would be happy to send you a full stack trace, but you would need to direct me where to find these log files.  My access to files is only via Filezilla...

As for the full class definitions, again, not sure exactly what you are looking for on this one either...?

This one I can do...  The tablename that I'm trying to access, via the 'deals_qa' database is 'members'...

Re: Experts Please Help! Connecting to Multiple Databases!

Woooaaahh! Stop right there!
You are working on LIVE code?
That's seriously WRONG!

You should be running this code locally and testing by running your test suite and ONLY sending your code live AFTER your test suite passes ALL tests, you have commited the code into your source code (git?) repository then using capistrano to send it live!!!!!

I'm seriously worried that you may be out of your depth on mission critical software!

Before you go ANY further I really need to understand exactly what your situation is with this project, what support you have and how much support you have available to you within the organisation you are working for. This is pretty serious stuff!

I'm more than happy to continue to point you in the right direction but there is the potential to seriously stuff up if you continue your current approach.

I am hoping I have totally misunderstood your situation and possibly an open forum is not the place to really discuss this.

You can find your log files in the your_app_root/log folder named development.log, test.log and production.log name obvously after the environment you are running under

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

I've now been able to digest your code.
The relevant parts are

module Dealsdb

@abstract_class = true

conn = { :adapter => 'mysql',
:encoding => 'utf8',
:reconnect => 'true',
:database => 'deals_qa',
:username => 'xxxxxx',
:password => 'xxxxxx',
:host => 'xx.xx.xx.xx',
:port => 'xxxx'
}

establish_connection(conn["deals_qa"])

end
end

and

class Members < Dealsdb::Base

  def email_exists?(email)
    unless email.blank? || SYSTEM_EMAILS.include?(email)
    returning find_by_email(email) do |user|
    end
    end
  end
end

Members class is expecting to access the dabase right?
Therefore Members needs to descend from ActiveRecord not Module
So ignore the module for the moment and change the class definition of members.rb to be

class Members < ActiveRecord::Base

  establish_connection deals_#{Rails.env}

  def email_exists?(email)
    unless email.blank? || SYSTEM_EMAILS.include?(email)
    returning find_by_email(email) do |user|
    end
    end
  end
end

Your database.yml file should be

deals_test:
  adapter: mysql #Should probably be SQLite3 database settings
  encoding: utf8
  reconnect: true
  database: deals_qa
  username: name
  password: password
  host: host ip address
  port: port number

deals_development:
  adapter: mysql #Again, Should possibly be SQLite3 database settings
  encoding: utf8
  reconnect: true
  database: deals_qa
  username: name
  password: password
  host: host ip address
  port: port number

deals_production:
  adapter: mysql
  encoding: utf8
  reconnect: true
  database: deals_qa
  username: name
  password: password
  host: host ip address
  port: port number

With the appropriate settings for each environment and should pretty much mirror the settings you have for the main database.

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)

Re: Experts Please Help! Connecting to Multiple Databases!

An explanation of the above

The line establish_connection deals_#{Rails.env} causes the value of Rails.env to be injected (appended) into the string deals_
Therefore when your server is running in testmode i.e. when running your test suite this will result in the establish_connection string becoming

establish_connection deals_test

and when the server is running in production mode will give you the value of

establish_connection deals_production

and again when running in development mode

establish_connection deals_development

establish_connection is not normally needed because rails conventions will automatically be set to just development, production or test automatically. It's part of ActiveRecord base class. so is there in all models thropugh inheritance, you just don't see it.

Now that is all explained, it is easier to understand the following.
establish_connection in an AR model causes rails to look in the database.yml under the section named in the qualifying string e.g.
When

establish_connection production

is defined Rails will look for the connection values in your database.yml under the production section and equally when it is set to

establish_connection deals_production

Rails will use the connection values under the deals_production section in your database.yml file.

Therefore

establish_connection deals_#{Rails.env} 

will always make sure you are connected to the correct database regardless of the environment your server is running under providing you have the correct database.yml settings

Hope that makes things clearer
You were getting the error

Error message:
superclass must be a Class (Module given)

These are the possible causes:

There may be a syntax error in the application's code. Please check for such errors and fix them.

A required library may not installed. Please install all libraries that this application requires.

The application may not be properly configured. Please check whether all configuration files are written correctly, fix any incorrect configurations, and restart this application.

A service that the application relies on (such as the database server or the Ferret search engine server) may not have been started. Please start that service.

Further information about the error may have been written to the application's log file. Please check it in order to analyse the problem.

because you had class Members < Dealsdb::Base and Dealsdb::Base is a module NOT an ActiveRecord class

What you want and what you need are too often not the same thing!
When your head is hurting from trying to solve a problem, stop standing on it. When you are the right way up you will see the problem differently and you just might find the solution.
(Quote by me 15th July 2009)