2

Currently working on getting some info from an external application's database onto my user profile page as a test.

I've run into a snag that I can't seem to get past. This is probably something so simple, but I'm pulling my hair out over it.

I have 2 databases - one is Drupal, and the other is from SocialEngine. These are both hosted on the same server(not localhost).

My error is this: Base table or view not found: 1146 Table 'uitv.engine4_users' doesn't exist: SELECT email FROM {engine4_users} WHERE enabled = :enabled; Array ( [:enabled] => 1 )

Awesome. Okay, well I think I can see the issue here... engine4_users is actually in the uitv_social database, while my Drupal installation is in the 'uitv' database. I've used db_set_active($social); which relates to my database settings in settings.php - and I've set it back to the default database after I query the social one.

What am I missing here? (Drupal 7 database documentation seems to be blank on this subject)

[EDIT: I did something silly - use db_set_active('yourdbkey'); - and make sure your query is right. Hope this helps a Googler. Curiously, I had done this several times before, but since the query was incorrect it wasn't giving me anything, obviously.]

  • Can you write your solution up as an answer and accept it, so that the question is marked as resolved. Alternatively, you could also close it. – Berdir Oct 4 '11 at 4:29
1

I know you answered your own question with a solution -- here is additional info.

The helper functions db_select(), db_update(), db_insert(), db_merge() all use the default database connection defined in settings.php. As you pointed out you can use db_set_active() to set an active database within a given scope of code. I personally dont like this approach, because X months from writing the code you will forget that, "oooohhh I used db_set_active X lines above" and run into annoying maintenance issues.

There is another solution, dont simply use the helper db functions. Use their parent Objects, which they are wrapping ... as in below:

    // 'remote_database' is a 2nd db connection defined in settings.php
    $query = Database::getConnection('default', 'remote_database')->select('survey_item', 'si');
$query->fields('si', array('item_id', 'presentation_order', 'question_type', 'stem', 'choice_1','choice_2', 'choice_3', 'choice_4', 'choice_5', 'choice_6', 'choice_7', 'choice_8'));
$query->condition('survey_id', $current_row->survey_id);
$query->orderBy('presentation_order', 'ASC');
$current_row->form_items = $query->execute()->fetchAll();

So you can create a Query as I have above by specifying the connection first. You may do this for all ->select() or ->update() or ->insert() functions which drupal supplies wrappers for.

Or, you can even pass as a 3rd argument to a SelectQuery() constructor the connection to use for the query, see the docs here:

http://api.drupal.org/api/drupal/includes--database--select.inc/class/SelectQuery/7

  // This is the constructor for SelectQuery() from select.inc
  public function __construct($table, $alias = NULL, DatabaseConnection $connection, $options = array()) {
$options['return'] = Database::RETURN_STATEMENT;
parent::__construct($connection, $options);
$this->where = new DatabaseCondition('AND');
$this->having = new DatabaseCondition('AND');
$this->addJoin(NULL, $table, $alias);

}

So you can also start a db_select() query as follows to tie it to a non default connection in another means:

$query = new SelectQuery('my_table', 'table_alias', Database::getConnection('default', 'remote_database'));
// other things you build onto your query
// $query->addJoins(...); $query->fields(...); ...
return $query->execute()->fetchAll();

So in a nutshell -- the db helper functions are convenience when talking to 1 database. Beyond that, you can specify per-query what connection to use ... if you like more fine control of your queries and not use db_set_active() all over the place.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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