[development] Best practice for update_sql?

[Kieran Parsons]

I am wondering how best to change update_sql commands for Drupal 6
modules. I know that update_sql does not to %-placeholder substitution
(although previous patches suggest that it was considered and rejected
-- eg http://drupal.org/node/36324), but now we also need to remove
literals from db_query
(http://drupal.org/node/114774#db-query-standard) and so in effect
from update_sql as well.

This seems to make update_sql much less useful. I looked through how
the core modules deal with this and they do not seem consistent to me,
and also do not seem to be following the dq_query change either. Some
examples from system.install:

$ret[] = update_sql("UPDATE {profile_fields} SET category = 'Account
settings' WHERE LOWER(category) = 'account'");
$ret[] = update_sql("UPDATE {permission} SET perm =
'$renamed_permission' WHERE rid = $role->rid");

In many other cases the update is done using db_query and then something like:
$ret[] = array('success' => TRUE, 'query' => "My query");
is used. But if the db_query failed this will still appear as a
success and the only error message will be in the normal error message
(which presumably may only be logged and not displayed in some cases).

What is the best way to deal with this?

Thanks,
Kieran