update_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()

▸ 62 functions call update_sql()

▾ 62 functions call 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.mysql-common.inc: Add a new field to a table.
db_add_field in includes/database.pgsql.inc: Add a new field to a table.
db_add_index in includes/database.mysql-common.inc: Add an index.
db_add_index in includes/database.pgsql.inc: Add an index.
db_add_primary_key in includes/database.mysql-common.inc: Add a primary key.
db_add_primary_key in includes/database.pgsql.inc: Add a primary key.
db_add_unique_key in includes/database.mysql-common.inc: Add a unique key.
db_add_unique_key in includes/database.pgsql.inc: Add a unique key.
db_change_column in ./update.php: Change a column definition using syntax appropriate for PostgreSQL. Save result of SQL commands in $ret array.
db_change_field in includes/database.mysql-common.inc
db_change_field in includes/database.pgsql.inc: Change a field definition.
db_create_table in includes/database.inc: Create a new table from a Drupal table definition.
db_drop_field in includes/database.mysql-common.inc: Drop a field.
db_drop_field in includes/database.pgsql.inc: Drop a field.
db_drop_index in includes/database.mysql-common.inc: Drop an index.
db_drop_index in includes/database.pgsql.inc: Drop an index.
db_drop_primary_key in includes/database.mysql-common.inc: Drop the primary key.
db_drop_primary_key in includes/database.pgsql.inc: Drop the primary key.
db_drop_table in includes/database.mysql-common.inc: Drop a table.
db_drop_table in includes/database.pgsql.inc: Drop a table.
db_drop_unique_key in includes/database.mysql-common.inc: Drop a unique key.
db_drop_unique_key in includes/database.pgsql.inc: Drop a unique key.
db_field_set_default in includes/database.mysql-common.inc: Set the default value for a field.
db_field_set_default in includes/database.pgsql.inc: Set the default value for a field.
db_field_set_no_default in includes/database.mysql-common.inc: Set a field to have no default value.
db_field_set_no_default in includes/database.pgsql.inc: Set a field to have no default value.
db_rename_table in includes/database.mysql-common.inc: Rename a table.
db_rename_table in includes/database.pgsql.inc: Rename a table.
hook_update_N in developer/hooks/install.php: Perform a single update.
locale_update_6000 in modules/locale/locale.install: {locales_meta} table became {languages}.
locale_update_6001 in modules/locale/locale.install: Change locale column to language. The language column is added by update_fix_d6_requirements() in update.php to avoid a large number of error messages from update.php. All we need to do here is copy locale to language and then drop locale.
locale_update_6002 in modules/locale/locale.install: Remove empty translations, we don't need these anymore.
locale_update_6003 in modules/locale/locale.install: Prune strings with no translations (will be automatically re-registered if still in use)
locale_update_6006 in modules/locale/locale.install: Neutralize unsafe language names in the database.
statistics_update_1000 in modules/statistics/statistics.install: Changes session ID field to VARCHAR(64) to add support for SHA-1 hashes.
system_update_6000 in modules/system/system.install: Remove auto_increment from {boxes} to allow adding custom blocks with visibility settings.
system_update_6005 in modules/system/system.install: Add language to url_alias table and modify indexes.
system_update_6006 in modules/system/system.install: Drop useless indices on node_counter table.
system_update_6007 in modules/system/system.install: Change the severity column in the watchdog table to the new values.
system_update_6008 in modules/system/system.install: Add info files to themes. The info and owner columns are added by update_fix_d6_requirements() in update.php to avoid a large number of error messages from update.php. All we need to do here is copy description to owner and then drop description.
system_update_6009 in modules/system/system.install: The PHP filter is now a separate module.
system_update_6011 in modules/system/system.install: Add language support to nodes
system_update_6015 in modules/system/system.install: Add the form cache table.
system_update_6016 in modules/system/system.install: Make {node}'s primary key be nid, change nid,vid to a unique key. Add primary keys to block, filters, flood, permission, and term_relation.
system_update_6018 in modules/system/system.install: Add HTML corrector to HTML formats or replace the old module if it was in use.
system_update_6019 in modules/system/system.install: Reconcile small differences in the previous, manually created mysql and pgsql schemas so they are the same and can be represented by a single schema structure.
system_update_6021 in modules/system/system.install: Migrate the menu items from the old menu system to the new menu_links table.
system_update_6022 in modules/system/system.install: Update files tables to associate files to a uid by default instead of a nid. Rename file_revisions to upload since it should only be used by the upload module used by upload to link files to nodes.
system_update_6034 in modules/system/system.install: Rename permission "administer access control" to "administer permissions".
system_update_6036 in modules/system/system.install: Change the search schema and indexing.
system_update_6037 in modules/system/system.install: Create consistent empty region for disabled blocks.
system_update_6038 in modules/system/system.install: Ensure that "Account" is not used as a Profile category.
system_update_6039 in modules/system/system.install: Rename permissions "edit foo content" to "edit any foo content". Also update poll module permission "create polls" to "create poll content".
system_update_6041 in modules/system/system.install: Change forum vocabulary not to be required by default and set the weight of the forum.module 1 higher than the taxonomy.module.
system_update_6044 in modules/system/system.install: RC1 to RC2 index cleanup.
system_update_6045 in modules/system/system.install: Update blog, book and locale module permissions.
system_update_6047 in modules/system/system.install: Fix cache mode for blocks inserted in system_install() in fresh installs of previous RC.
system_update_6051 in modules/system/system.install: Create a signature_format column.
update_fix_compatibility in ./update.php: Disable anything in the {system} table that is not compatible with the current version of Drupal core.

File

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

Code

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

Comments

Beware of serialized data

Posted by webchick on December 25, 2009 at 7:58am

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. :)

create your own update_sql function

Posted by mikeytown2 on January 7, 2010 at 6:42pm

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;
}
?>

Thanks very much for this code

Posted by fndtn357 on February 21, 2010 at 10:12am

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.

Drush description

Posted by emackn on November 12, 2010 at 9:44pm

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.

Brackets

Posted by brazorf on January 11, 2012 at 4:18pm

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