Table prefixes with db_select() in SimpleTest tests

Question

How can I do database queries using functions like db_select() and db_query() in my DrupalWebTestCase sub-class? When I try to run them, I get an error:

SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'simpletest421143 WHERE (nid = '1')' at line 2`

I think it's because the tables aren't properly prefixed, yet I see the prefix in the error string. So it seems that it's trying to prefix, but not doing it properly.

How can I use these functions in my testing class?

Answer 1

Executing queries inside SimpleTest tests is not different from executing them in a module. The path.test file contains the following code, and it is executed without errors.

$forum_vid = db_query("SELECT vid FROM {taxonomy_vocabulary} WHERE module = 'forum'")->fetchField();
$tid = db_insert('taxonomy_term_data')
  ->fields(array(
    'name' => $this->randomName(),
    'vid' => $forum_vid,
  ))
  ->execute();

Answer 2

tl;dr: Put curly braces around your table names.

---

A relatively well-known, but not well documented feature of db_query() is to automatically replace {table_name} (note the curly braces) with the_proper_prefix.table_name. Then it will work with Simpletest and on sites that use table prefixes.

The only documentation on this that I could find is in DatabaseConnection::prefixTables which is called from DatabaseConnection::prepareQuery which is called from DatabaseConnection::query which is called from db_query:

Queries sent to Drupal should wrap all table names in curly brackets. This function searches for this syntax and adds Drupal's table prefix to all tables, allowing Drupal to coexist with other systems in the same database and/or schema if necessary.