Drupal 7 db_query using CAST

Question

Does Drupal 7's DB API support SQL's CAST function? I have a varchar with numbers in it that I'm trying to convert to integers for proper sorting. Here's my statement:

$images = db_query('SELECT filename, caption FROM {iha_captions}
WHERE uid = :uid ORDER BY CAST(weight AS INT) ASC', array(':uid' => $userid));

And here is the error I get:

PDOException: 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 'INT) ASC' at line 1: SELECT filename, caption FROM {iha_captions} WHERE uid = :uid ORDER BY CAST(weight AS INT) ASC; Array ( [:uid] => 2814 ) in iha_search_profile()

It then causes a cascade of SQL failures in Drupal's session and cache queries. Is CAST supported, or am I doing it wrong?

Answer 1

It's not a Drupal error; it comes from the MySQL engine about CAST using:

The type can be one of the following values:
BINARY[(N)]
CHAR[(N)]
DATE
DATETIME
DECIMAL[(M[,D])]
SIGNED [INTEGER]
TIME
UNSIGNED [INTEGER]

You cannot use INT; it should be SIGNED, UNSIGNED or DECIMAL.

Answer 2

It's not a Drupal error it's a MySQL error. CAST() won't accept INT as a valid type but does give you a choice between SIGNED and UNSIGNED to cast to an integer type.

The following query runs fine for me in Drupal:

SELECT * FROM node ORDER BY CAST(nid AS UNSIGNED)