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

peregrine

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.

Todd

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

Todd

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.

hbf

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.

Todd

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

peregrine

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.

Todd

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.

peregrine

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.

hbf

here is my first cut at taking this great info and getting it into the wiki. feel free to edit.

http://vanillawiki.homebrewforums.net/index.php/Tips_for_Working_with_External_Databases

peregrine

@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.

hbf

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.