Skip to main content

This site requires you to update your browser. Your browsing experience maybe affected by not having the most up to date version.

Archive

Our old forums are still available as a read-only archive.

Moderators: martimiz, Sean, biapar, Willr, Ingo, simon_w

Connecting to an external secondary database


Reply

6 Posts   3711 Views

Avatar
Alex

24 November 2006 at 12:08pm Community Member, 6 Posts

I'm trying to use the Silverstripe DB class to access data from other existing databases. I've tried the following code snippet but it doesn't restore the previous connection causing all normal SilverStripe queries to try (and fail) to act on the second database. Is this possible? Should I just give up and use adodb?

class Product extends Page {

function test1() {
$oldConn = DB:getConn();
$otherDB = array(
         "type" => "MySQLDatabase",
         "server" => "localhost",
         "username" => "******",
         "password" => "******",
         "database" => "TestDatabase",
      );
DB::connect($otherDB);
$rs = DB::query("SELECT * FROM products");
Debug::show($rs->record());
DB::setConn($oldConn);
}
}

Avatar
Simon

24 November 2006 at 2:43pm Core Development Team, 27 Posts

All that the DB class does is provide static methods for accessing a global Database object.

To access a second database, you want to create a database object, specifically, a MySQLDatabase object.

Here's a code snippet to illustrate what I mean:

$db = new MySQLDatabase(array(
"server" => "localhost",
"username" => "******",
"password" => "******",
"database" => "TestDatabase",
));

$query = $db->query("SELECT * FROM Products");

foreach($query as $row) {
Debug::show($row);
}

/*
* Even better, here's how to turn it into SilverStripe objects
*/

class MyProduct extends ViewableData {
// ... make your own methods in here
}

$result = new DataObjectSet();
foreach($query as $row) {
$result->push(new MyProductDataObject($row));

}
return $result;

// Now $result can be inserted into a .ss template

Avatar
Alex

24 November 2006 at 5:33pm Community Member, 6 Posts

That is actually what I tried to do initially but found that SilverStripe tried to keep using the external database. I just tried your exact code snippet (changing the DB params of course) and received the same error. This project is currently using Beta1 so I'll upgrade and see if that fixes anything.

Avatar
Alex

24 November 2006 at 5:44pm Community Member, 6 Posts

Just upgraded to Beta2 and still have the same error. My query executes perfectly but is immediately followed by:

FATAL ERROR: DATABASE ERROR: Couldn't run query: SELECT `SiteTree_Live`.*, `UserDefinedForm_Live`.*, ...SNIP... ORDER BY Sort | Table 'vitaltree_db2.SiteTree_Live' doesn't exist
At line 185 in /home/vitaltreess2/public_html/sapphire/core/model/Database.php

I'm running in dev mod with debug=1&flush=1

Avatar
Alex

24 November 2006 at 9:30pm Community Member, 6 Posts

I've found a solution but I'm not sure why it works. I've set the new_link (4th) parameter to TRUE in the mysql_connect statement in the MySQLDatabase class (line 21).
I'm using the same server/username/password for both databases so it shouldn't need to create a new connection but...

... sometime later ....

Ok I've done some research on the php.net website

It appears that the mysql_select_db function sometimes does not change the active DB when mysql_connect recycles it's connection. You can find more details in the comments here -> http://ca.php.net/manual/en/function.mysql-select-db.php

Avatar
Sam

25 November 2006 at 2:18pm Administrator, 685 Posts

Setting the new_link parameter seems appropriate; the alternative would be to call "USE xxx" before each database query which would get annoying, especially given that multi-database access is a pretty rare scenario.