function update_sql

You are here

4.6 updates.inc update_sql($sql)
4.7 update.php update_sql($sql)
5 update.php update_sql($sql)
6 database.inc update_sql($sql)

Perform an SQL query and return success or failure.

Parameters

$sql: A string containing a complete SQL query. %-substitution parameters are not supported.

Return value

An array containing the keys: success: a boolean indicating whether the query succeeded query: the SQL query executed, passed through check_plain()

Related topics

62 calls to update_sql()
book_update_6000 in modules/book/book.install
Drupal 5.x to 6.x update.
comment_update_6001 in modules/comment/comment.install
db_add_column in ./update.php
Add a column to a database using syntax appropriate for PostgreSQL. Save result of SQL commands in $ret array.
db_add_field in includes/database.pgsql.inc
Add a new field to a table.
db_add_field in includes/database.mysql-common.inc
Add a new field to a table.

... See full list

1 string reference to 'update_sql'
drupal_error_handler in includes/common.inc
Log errors as defined by administrator.

File

includes/database.inc, line 63
Wrapper for database interface code.

Code

function update_sql($sql) {
  $result = db_query($sql, true);
  return array(
    'success' => $result !== FALSE,
    'query' => check_plain($sql),
  );
}

Comments

At least as of current writing (Drupal 6.15 and 5.21), update_sql() will break serialized data that's passed to it, since it gets confused about { }s and strips them out, thinking they're db prefix indicators.

In other words, this:

a:1:{s:6:"groups";a:1:{i:2237;s:4:"2237";}}

gets mutilated into this:

a:1:s:6:"groups";a:1:i:2237;s:4:"2237";

The workaround is to use db_query() with proper %s placeholders instead.

There's a bug report about this at http://drupal.org/node/667714, but since this function was thankfully removed in Drupal 7, I'm not sure if it's a huge priority. Wanted to document this fact, in any case, so hopefully the next person doesn't pull out their hair. :)

I find this quite useful.

<?php
/**
* Perform an SQL query and return success or failure.
*
* @see http://api.drupal.org/api/function/update_sql/6
*
* @param $sql
*   A string containing a complete SQL query.
* @return
*   An array containing the keys:
*      success: a boolean indicating whether the query succeeded
*      query: the SQL query executed, passed through check_plain()
*      rows: number of rows effected
*/
function my_update_sql($sql) {
 
$args = func_get_args();
 
array_shift($args);
 
$result = db_query($sql, $args);
 
$sql = my_return_query_string($sql, $args);
  return array(
'success' => $result !== FALSE, 'query' => check_plain($sql), 'rows' => db_affected_rows());
}

/**
* Builds a basic query, returns string.
*
* @see http://api.drupal.org/api/function/db_query/6
*
* @param $query
*   A string containing an SQL query.
* @param ...
*   A variable number of arguments which are substituted into the query
*   using printf() syntax. Instead of a variable number of query arguments,
*   you may also pass a single array containing the query arguments.
*
*   Valid %-modifiers are: %s, %d, %f, %b (binary data, do not enclose
*   in '') and %%.
*
*   NOTE: using this syntax will cast NULL and FALSE values to decimal 0,
*   and TRUE values to decimal 1.
*
* @return
*   String of query that would have been run
*/
function my_return_query_string($query) {
 
$args = func_get_args();
 
array_shift($args);
 
$query = db_prefix_tables($query);
  if (isset(
$args[0]) and is_array($args[0])) { // 'All arguments in one array' syntax
   
$args = $args[0];
  }
 
_db_query_callback($args, TRUE);
 
$query = preg_replace_callback(DB_QUERY_REGEXP, '_db_query_callback', $query);
  return
$query;
}
?>

I ended up using your code in a file as

mysqlupdate.php

without the ?> at the end. I put this file into the default folder and included

include('mysqlupdate.php');

in the settings.php file at the very end.

Works beautifully! I have Code Review accepting this as well.

To have drush print out a useful description of your update hook, comment your code. Drush looks for the comments and uses it if they are there.

This is just a silly thing, but here you go: do not include brackets ({,}) in the sql code or the query will fail.