Connect and Query external database in Drupal 8 and shortcut_set_users doesn’t exist error

To connect with external db add the following code in the sites/default/settings.php replacing username/password and database name with your credentials.

$databases['external']['default'] = array(
'database' => 'external',
'username' => 'root',
'password' => '',
'prefix' => '',
'host' => 'localhost',
'port' => '3306',
'namespace' => 'Drupal\\Core\\Database\\Driver\\mysql',
'driver' => 'mysql',
);

In the controller or module where you want your custom query, add the following line.


use \Drupal\Core\Database\Database;
Database::setActiveConnection('external');
$db = Database::getConnection();
$data = $db->select('comprofiler', 'table_alias')->fields('table_alias', ['fieldname'])->execute();
foreach ($data as $profile) {
$test[] = $profile->fieldname;
}
Database::setActiveConnection();

Always change the database connection back to default.
I had the following error when i forgot to add the last line in the above code.

Uncaught PHP Exception Drupal\\Core\\Database\\DatabaseExceptionWrapper: "SQLSTATE[42S02]: Base table or view not found: 1146 Table 'external.shortcut_set_users' doesn't exist

I thought the error was in querying to my external database and wasted a lot of time trying to find the cause. Absence of that line will cause Drupal to use the external database instead of Drupal database for all the remaining queries and give a table not found fatal error, in this case shortcut_set_users.

Always remember to set the connection back to default.

Advertisements

2 thoughts on “Connect and Query external database in Drupal 8 and shortcut_set_users doesn’t exist error

  1. Thanks for the tutorial finally I was able to see my data from external db, but how to show data from external database to view(node, block) of D8?. The Data that I showed always show out of D8 view like top of header.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s