Use this function instead of db_query() to avoid switching databases when making query to the chado database.

Will use a chado persistent connection if it already exists.

NOTE: When writting SQL queries it is important to enclose the chado table name in curley brackets (ie: {feature}) in order to enusure proper prefixing. Furthermore, if you need to join between tables in separate schemas (ie: chado and drupal) then you should enclose the drupal table in square brackets (ie: [chado_feature]). Keep in mind JOINING BETWEEN SCHEMA'S IS NOT RECOMMENDED since it will break functionality for sites with external chado databases.

$sql = "SELECT F.name, CVT.name as type_name, ORG.common_name
         FROM {feature} F
         LEFT JOIN {cvterm} CVT ON F.type_id = CVT.cvterm_id
         LEFT JOIN {organism} ORG ON F.organism_id = ORG.organism_id
         WHERE
           F.uniquename = :feature_uniquename";
$args = array( ':feature_uniquename' => $form_state['values']['uniquename'] );
$result = chado_query( $sql, $args );
foreach ($result as $r) { [Do something with the records here] }

Parameters

$sql: The sql statement to execute

$args: The array of arguments, with the same structure as passed to the db_query() function of Drupal.

Return value

DatabaseStatementInterface A prepared statement object, already executed.

Example usage:

Related topics

File

tripal_core/api/tripal_core.chado_query.api.inc, line 1489

Provides an API for querying of chado including inserting, updating, deleting and selecting from chado.

Code

function chado_query($sql, $args = array()) {
  $is_local = isset($GLOBALS["chado_is_local"]) && $GLOBALS["chado_is_local"];

  // Args should be an array
  if (!is_array($args)) {
    tripal_report_error('tripal_core', TRIPAL_ERROR, 
    'chado_query; Need to pass an array to chado_query, "%value" passed instead. Query: %query', 
    array('%value' => $args, '%query' => $sql)
    );
    return FALSE;
  }

  // if Chado is local to the database then prefix the Chado table
  // names with 'chado'.
  if ($is_local) {

    // Remove carriage returns from the SQL.
    $sql = preg_replace('/\n/', '', $sql); // remove carriage returns

    // Prefix the tables with their correct schema.
    // Chado tables should be enclosed in curly brackets (ie: {feature} )
    // and Drupal tables should be enclosed in square brackets (ie: [tripal_jobs] ).
    $chado_schema_name = tripal_get_schema_name('chado');
    $drupal_schema_name = tripal_get_schema_name('drupal');
    $sql = preg_replace('/\{(.*?)\}/', $chado_schema_name . '.$1', $sql);
    $sql = preg_replace('/\[(\w+)\]/', $drupal_schema_name . '.$1', $sql);

    // Add an alter hook to allow module developers to change the query right before it's
    // executed. Since all queriying of chado by Tripal eventually goes through this
    // function, we only need to provide an alter hook at this point in order to ensure
    // developers have complete control over the query being executed. For example,
    // a module developer might want to remove schema prefixing from queries and rely
    // on the search path. This alter hook would allow them to do that by implementing
    // mymodule_chado_query_alter($sql, $args) and using a regular expression to remove
    // table prefixing from the query.
    // @see hook_chado_query_alter().
    drupal_alter('chado_query', $sql, $args);

    // The featureloc table has some indexes that use function that call other
    // functions and those calls do not reference a schema, therefore, any
    // tables with featureloc must automaticaly have the chado schema set as
    // active to find.
    if (preg_match('/' . $chado_schema_name . '.featureloc/i', $sql) or preg_match('/' . $chado_schema_name . '.feature/i', $sql)) {
      $previous_db = chado_set_active('chado');
      $results = db_query($sql, $args);
      chado_set_active($previous_db);
    }
    // for all other tables we should have everything in scope so just run the query
    else {
      $results = db_query($sql, $args);
    }
  }
  // Check for any cross schema joins (ie: both drupal and chado tables represented
  // and if present don't execute the query but instead warn the administrator.
  elseif (preg_match('/\[(\w*?)\]/', $sql)) {
    tripal_report_error(
    'chado_query', 
    TRIPAL_ERROR, 
    'The following query does not support external chado databases. Please file an issue
      with the Drupal.org Tripal Project. Query: @query', 
    array('@query' => $sql)
    );
    return FALSE;
  }
  // if Chado is not local to the Drupal database then we have to
  // switch to another database
  else {
    $previous_db = chado_set_active('chado');
    $results = db_query($sql, $args);
    chado_set_active($previous_db);
  }

  return $results;
}