Jump to:

17452 Posts in 4473 Topics by 1971 members

Archive

SilverStripe Forums » Archive » Connecting to an external secondary database

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

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

Page: 1
Go to End
Author Topic: 3677 Views
  • Alex
    Avatar
    Community Member
    6 Posts

    Connecting to an external secondary database Link to this post

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

  • Simon
    Avatar
    Core Development Team
    27 Posts

    Re: Connecting to an external secondary database Link to this post

    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

  • Alex
    Avatar
    Community Member
    6 Posts

    Re: Connecting to an external secondary database Link to this post

    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.

  • Alex
    Avatar
    Community Member
    6 Posts

    Re: Connecting to an external secondary database Link to this post

    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

  • Alex
    Avatar
    Community Member
    6 Posts

    Re: Connecting to an external secondary database Link to this post

    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

  • Sam
    Avatar
    Administrator
    679 Posts

    Re: Connecting to an external secondary database Link to this post

    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.

    3677 Views
Page: 1
Go to Top

Want to know more about the company that brought you SilverStripe? Then check out SilverStripe.com

Comments on this website? Please give feedback.