A substitute for db_query() when querying from Chado.

This function is needed to avoid switching databases when making query to the chado database.

Will use a chado persistent connection if it already exists.

$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_chado/api/tripal_chado.query.api.inc, line 1654

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

Code

function chado_query($sql, $args = array()) {
  $results = NULL;

  $is_local = isset($GLOBALS["chado_is_local"]) && $GLOBALS["chado_is_local"];

  // Args should be an array
  if (!is_array($args)) {
    tripal_report_error('tripal_chado', 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);

    // 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 = chado_get_schema_name('chado');
    $drupal_schema_name = chado_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');
      try {
        $results = db_query($sql, $args);
        chado_set_active($previous_db);
      }
      catch (Exception $e) {
        chado_set_active($previous_db);
        throw $e;
      }
    }
    // 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.
  else if (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;
}