Calling Multiple DBs
Development and Programming
Ran into something very weird today and hoping someone might be able to help me puzzle it out.
I had a need to call a database OTHER than the default one in a module today.
So I did something I’d done before and created a function for that separate DB call like this:
function alternate_database_call($sql)
{
$adb_config = array(
'hostname' => 'SOME HOSTNAME HERE',
'username' => 'SOME USERNAME HERE',
'password' => 'SOME PASSWORD HERE',
'database' => 'SOME DATABASE NAME HERE',
'prefix' => 'exp'
);
$ADB = new DB($adb_config);
$ADB->debug = FALSE;
$ADB->enable_cache = FALSE;
return $ADB->query($sql);
}I tested this new code on our development site which has a different database server than this alternate database.
Then I uploaded the code thoroughly tested on the dev site to the live site where the alternate database and the main database were on the same server.
However, on the live site it failed badly. Database calls that weren’t being run through this function were also trying to use the alternate database.
As I’ve never had this happen before I’m hoping someone at EllisLab might be able to point me in the right direction.
For the record we’re on EE 1.6.8 Build 20100415
Would really appreciate someone helping me out on this. Its preventing me from using a piece of code that’s going to save me a serious amount of grief if I can get it to work.
Jamie
Don’t know why your seeing the results you are (esp between dev and live)… it almost sounds like your stomping on a global object, var or something.
Have you tried creating an instance of the class then setting your DB specific settings like this? (this is the path I usually take without having any issues)
$DB2 = new DB();
$DB2->hostname = "localhost";
$DB2->username = "username";
$DB2->password = "password";
$DB2->database = "db_name";
$query = $DB2->query("SELECT something_cool FROM the_db");I might as well try seeing if that makes any difference. It doesn’t seem like it should make any difference at all.
Anyone at EllisLab maybe have a thought?
Jamie
I would agree, I took a quick peak at the EE DB file, what your doing looks to me like it should work fine… (Didn’t really dig into it though)
So solved this this afternoon.
Turns out there’s a function for setting the database to pull from. When you instantiate the DB class and set a database then immediately after make a query it runs this function:
$DB->select_db();
But when the two databases are on the same server this apparently switches the database for subsequent $DB calls as well. When the two databases are on different servers it seems to work fine.
Anyway, adding these two lines after the query to the alternate database happened solved the issue:
$DB->database = $PREFS->ini('db_name');
$DB->select_db();Jamie
Sweet, that’s a good one for the ol’ bag of tricks.
Thanks for posting the solution.