Query other external databases [closed]

Question

I'm trying to query an other mysql database that has an completely other table-structure than drupal (no session, semantics table and so on). When trying to add the database to my config file I get these strange errors that I'm missing the semantics table.

I get the feeling that drupal is expecting that the other database is an also drupal database, which it is not..

My question is: how do I query and complete other other database (other user and table) the drupal way?

I already tried this but it gives the same problems: http://drupal.org/node/18429

Answer 1

It sounds like one of two things might be happening:

1. You're overwriting the default database (which needs to be the Drupal database) with your custom database.

2. You're calling API functions that interact with the Drupal database before switching back from your external db.

In either case these are abridged versions of the code from the docs page you linked to with a few comments that will hopefully help it to make more sense:

Drupal 6:

// Define the default database, this MUST be the database containing the Drupal installation.
$db_url['default'] = 'mysql://drupal:drupal@localhost/drupal';

// Define your external database separately.
$db_url['mydb'] = 'mysql://user:pwd@localhost/anotherdb';

module file:

// Switch to the external DB
db_set_active('mydb');

// Perform a query. Note that you should not call any Drupal API functions that will
// interact with the database until you've switched back. If you need your data to
// interact with the API, save it in a variable, switch back to the Drupal DB, and 
// THEN make the API call
db_query('SELECT * FROM table_in_anotherdb');

// Switch back
db_set_active('default');

Drupal 7:

settings.php:

$databases = array (
  'default' => array (
    'default' => array ( // Again this must be your Drupal database.
      'database' => 'drupal',
      'username' => 'username',
      'password' => 'password',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
  'mydb' => array (
    'default' => array (
      'database' => 'db1',
      'username' => 'username2',
      'password' => 'password2',
      'host' => 'db.example.com',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);

module file:

// Switch to the external DB
db_set_active('mydb');

// Get some data. The same rules about accessing the API functions apply here.
$results = db_query($sql);

// Switch back to the default db.
db_set_active('default');