Trouble Connecting to External Database

Question

Context: Joomla 3.8.5 MySQL Joomla database = berrien_dev External database = BMP PHP 5.6.33

I am attempting to access in a component I am writing an external database that sits in the same MySQL server as my Joomla site. After installing and running the component on the site side, I receive this message twice "Table 'berrien_dev.Estim' doesn't exist". As you can see, it is trying to find my table in the wrong database and I am stumped as to why.

Here is my model:

    protected function getListQuery()
{
    // Initialize variables and connect to remote MS SQL server
    $option = array();                          //prevent problems

    $option['driver']   = 'mysqli';            // Database driver name
    $option['host']     = 'localhost';       // Database host name
    $option['user']     = 'mysqluser';       // User for database authentication
    $option['password'] = 'somepassword';                 // Password for database authentication
    $option['database'] = 'BMP';          // Database name
    $option['prefix']   = '';               // Database prefix (may be empty)


    // Connect to external database
    $E2db = JDatabaseDriver::getInstance( $option );

    $query = $E2db->getQuery(true);

    // Create the base select statement.
    $query->select('a.PartNo as PartNo, a.Descrip as Descrip, a.CustCode as CustCode,
    a.EntDate as EntDate, a.EntBy as EntBy, a.ModDate as ModDate, a.ModBy as Modby')
    ->from($E2db->quoteName('Estim', 'a'));

    return $query;
}

Thanks for your help!

Answer 1

You have to let the Model know which database you want to use, in your code the query is correct but will be executed on the default database driver.

Override the __costruct of the Model in this way:

public function __construct($config = array()){
    parent::__construct($config);

    $option = array(); //prevent problems

    $option['driver']   = 'mysql';            // Database driver name
    $option['host']     = 'localhost';    // Database host name
    $option['user']     = 'mysqluser';       // User for database authentication
    $option['password'] = 'somepassword';   // Password for database authentication
    $option['database'] = 'BMP';      // Database name
    $option['prefix']   = '';             // Database prefix (may be empty)

    $db = JDatabaseDriver::getInstance( $option );
    parent::setDbo($db);
}

Source: Connecting to an external database

Answer 2

Where did you get this code? I'm not sure you can do this.

Try logging $E2db and JFactory::getDbo() to see if they're the same object, and whether the login data was saved.

If not, maybe you could create a new instance of the dbo. Something like

$bmpDbo=JDatabaseDriverPdomysql($options);

but I never tried, I'm not sure it will work.

But why not make your life easier, if they're on the same server, add the privileges to the second databases's table to your user, and simply

$db = JFactory::getDbo();
$db->select('BMP');

... then just run your query.