1

Can I set the target database on a db_select()?

Example:

$result = db_select('table', 't')->fields('t')->db_target('db_name')->execute();

One can use db_set_active() to change to which database a query should be sent, but that requires one to manually "reset" the active db, which is a potential cause of bugs.

4
  • 1
    I've never come across any bugs with db_set_active(), could you elaborate a wee bit?
    – Clive
    Oct 9, 2012 at 12:45
  • I haven't entirely figured out what happened, but it seemed like a query against db A triggered a hook, that made a module want to translate a string, which made locale try to lookup translations, which fails because locale_source is in db B. I had to hack a db_set_actice('default') into locale.module to work around it. Made me think it would be useful to set the target per query instead of per connection.
    – Letharion
    Oct 9, 2012 at 13:06
  • Wow that sounds like fun! I'm not sure but maybe the code in Kiamlaluno's answer to this will help? The questions don't match but there's a technique in there for selecting from arbitrary databases that I hadn't seen before
    – Clive
    Oct 9, 2012 at 13:13
  • Hmm, interesting, I will have to try it next time this problem bites me. Thanks @Clive.
    – Letharion
    Oct 9, 2012 at 13:45

1 Answer 1

3

From the comments above such as from @Clive, I do this alll the time:

$count_query = Database::getConnection('default', 'dew_remote_sources')
               ->select('survey', 's');
$count_query->addExpression('COUNT(DISTINCT(survey_number))', 'cnt');

For this to work you need to have various databases defined in your $databases value of settings.php (or you can add additional $databases[key] values to top of your module sometimes, or create such a connection on-the-fly using:

Database::addConnectionInfo('for_migration', 'default', array(
  'driver' => 'mysql',
  'database' => 'migration_database',
  'username' => 'username',
  'password' => 'password',
  'host' => 'localhost',
  'prefix' => '',
));

You see this pattern alot in the Migrate documentation pages, migrate examples and 3rd party modules that have implemented migrations. Because you are literally moving content from DatabaseX to DatabaseY alot and switching around with db_set_active can easily lead to confusion in such cases with the Object Oriented nature of the Migrate module.

1
  • This is probably one of the most useful things I have seen on this site. Cheers!
    – Chapabu
    Oct 9, 2012 at 15:28

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.