1

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?

  • 5
    Would you mind posting your actual query? – Chris Pliakas Jul 29 '11 at 20:44
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();
  • This does not explain why the OP's code doesn't work, nor why it does work in path.test. I added another answer explaining why & why not. – rudolfbyker Mar 4 at 9:19
  • How can I use these functions in my testing class? In the same way you do in normal code. Since the OP doesn't show any code, I cannot say your code is wrong in this point and this other point. – kiamlaluno Mar 4 at 9:45
0

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.

  • It's well-documented, and the documentation page is even linked in api.drupal.org/api/drupal/7.x: Database abstraction layer, right after example code, says Curly braces are used around "node" to provide table prefixing via DatabaseConnection::prefixTables(). – kiamlaluno Mar 4 at 9:53
  • Wow, how did I miss that... I was googling around for about an hour without finding that. Then I started reading the source code and found the answer there. Hopefully this answer, and your comment, will lead more people to the right docs. – rudolfbyker Mar 5 at 7:51

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.