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);
}
}

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

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.

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

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

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.