Please upgrade here. These earlier versions are no longer being updated and have security issues.
HackerOne users: Testing against this community violates our program's Terms of Service and will result in your bounty being denied.

[Documentation] Guidance needed on reading from an External Database within a plugin.

peregrineperegrine MVP
edited March 2012 in Vanilla 2.0 - 2.8

I am developing a plugin that reads data from an External database.

I have been able to do an external database read by modifying a plugin and using the PDO prepare fetch and execute statements outside of the extends plugin class but still residing in the plugin.

The only solution I have found is setting the variables outside of the class and putting the functions outside of the class (and I suspect this not the correct way to do things).

However, since I am a novice at oop and vanilla, when I try to put it inside extends plugin class I get a PDO error (if I remember correctly it said can't find PDO).

Can someone guide me through an example, give me a few tips, or point me to a plugin where I can achieve using PDO statement to an External Database (not vanilla) to execute some prepare, execute and fetch commands).

Thanks for any assistance on this.

I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

Tagged:

Best Answers

  • ToddTodd Chief Product Officer Vanilla Staff
    Answer ✓

    I'm a little perplexed by this problem. If you can find PDO outside of Vanilla then you should be able to find it inside of it.

    If you want to use Vanilla, but query an external database in a free form way you can also try the following.

    // I'm going to assume you are on the same server as Vanilla.
    $Db = new Gdn_Database(array('Dbname' => 'Database Name'));
    $Data = $Db->Query('select * from something')->ResultArray();
    var_dump($Data);
    
  • ToddTodd Chief Product Officer Vanilla Staff
    Answer ✓

    Okay here's the code in Vanilla:

    $Db = new Gdn_Database(array('Host' => 'localhost', 'Dbname' => 'external', 'User' => 'external', 'Password' => 'mypass'));
    $Results = $Db->Query("SELECT * FROM table WHERE example=:example", array(':example' => $example))->Result();
    

    If you want to use prepare and whatnot you can access the PDO object directly.

    $PDO = $Db->Connection();
    

    Finally, if you want to be able to put your database connection information in your config. You can add the following to your config.

    $Configuration['ExDatabase']['Host'] = 'localhost';
    $Configuration['ExDatabase']['Dbname'] = 'external';
    $Configuration['ExDatabase']['User'] = 'external';
    $Configuration['ExDatabase']['Password'] = 'mypass';
    
    $Db = new Gdn_Database('ExDatabase');
    

    All of the relavent db config is in Gdn_Database->Init() in \library\database\class.database.php.

  • hbfhbf wiki guy? MVP
    Answer ✓

    I'm not opposed to having peoples names in the wiki, feel free to edit the page.

    I havent gotten around to testing the new version yet.

Answers

  • ToddTodd Chief Product Officer Vanilla Staff
    Answer ✓

    I'm a little perplexed by this problem. If you can find PDO outside of Vanilla then you should be able to find it inside of it.

    If you want to use Vanilla, but query an external database in a free form way you can also try the following.

    // I'm going to assume you are on the same server as Vanilla.
    $Db = new Gdn_Database(array('Dbname' => 'Database Name'));
    $Data = $Db->Query('select * from something')->ResultArray();
    var_dump($Data);
    
  • peregrineperegrine MVP
    edited March 2012

    Thanks @Todd
    I think its my limited knowledge of the vanilla strategies. If I am asking the wrong questions it is because I am not a real expert at this.

    an explanation

    a standalone program would use these example statements
    $DB =new PDO('mysql:host=localhost;dbname=external', 'external', 'mypass');
    $LookupStmt=$DB->prepare("SELECT * FROM table WHERE example=:example");  
    $LookupStmt->execute(array(':example' => $example));
    $results = $LookupStmt->fetch(PDO::FETCH_ASSOC);
    

    (how would I modify these to use in a plugin and would it fit in the class.example.plugin)

    1) what I am a bit confused about is how to add password, host, database,etc for the connect portion, since at least the password is different than the vanilla database.

    2) once I do connect , can I use the prepare, fetch and execute or would that be carried out in the query statement. I was under the impression prepared statement and execute provide are fast and do some checking on the data.

    Thanks for any elucidation on this.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • ToddTodd Chief Product Officer Vanilla Staff
    Answer ✓

    Okay here's the code in Vanilla:

    $Db = new Gdn_Database(array('Host' => 'localhost', 'Dbname' => 'external', 'User' => 'external', 'Password' => 'mypass'));
    $Results = $Db->Query("SELECT * FROM table WHERE example=:example", array(':example' => $example))->Result();
    

    If you want to use prepare and whatnot you can access the PDO object directly.

    $PDO = $Db->Connection();
    

    Finally, if you want to be able to put your database connection information in your config. You can add the following to your config.

    $Configuration['ExDatabase']['Host'] = 'localhost';
    $Configuration['ExDatabase']['Dbname'] = 'external';
    $Configuration['ExDatabase']['User'] = 'external';
    $Configuration['ExDatabase']['Password'] = 'mypass';
    
    $Db = new Gdn_Database('ExDatabase');
    

    All of the relavent db config is in Gdn_Database->Init() in \library\database\class.database.php.

  • peregrineperegrine MVP
    edited March 2012

    Excellent. Just what I was looking for. Thanks for the great explanation.

    moderator, if you will place [Documentation] preceding title and two tags database and documentation tag,

    @hbf - here is nice piece of documentation for your wiki.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • peregrineperegrine MVP
    edited March 2012

    @hbf A thought - maybe silly, but if you put the questioners name and the answerers name in the write ups that are suggested for your wiki - maybe that would generate more documentation for your wiki and give people an ego boost. And obviously your name is in lights because it as your brainstorm in the first place, any thoughts?

    BTW - did the v2 of the plugin work for you.

    I may not provide the completed solution you might desire, but I do try to provide honest suggestions to help you solve your issue.

  • hbfhbf wiki guy? MVP
    Answer ✓

    I'm not opposed to having peoples names in the wiki, feel free to edit the page.

    I havent gotten around to testing the new version yet.

Sign In or Register to comment.