Assigning a Header Image Results in SQL Error With PostgreSQL [#481692]

The error:

pg_query() [<a href='function.pg-query'>function.pg-query</a>]: Query failed: ERROR: operator does not exist: character varying = integer LINE 1: ...LECT nid, conditions FROM headerimage WHERE block = 1 ORDER ... ^ HINT: No operator matches the given name and argument type(s). You might need to add explicit type casts.

The SQL that was generated:

select nid, conditions from headerimage where block = 1 order by weight, nid asc;

Since block is a varchar(32), PostgreSQL expects a string, not an integer as the parameter. Therefore, the parameter must be quoted.

The code that is causing the problem is on line 180 of headerimage.module.

$result = db_query("SELECT nid, conditions FROM {headerimage} WHERE block = %d ORDER BY weight, nid ASC", $block);

Changing line 180 of headerimage.module as below fixed the problem in PostgreSQL.

$result = db_query("SELECT nid, conditions FROM {headerimage} WHERE block = '%s' ORDER BY weight, nid ASC", $block);

Comments

Comment #1

cilkay commented 4 June 2009 at 08:06

Also had to change line 508 of headerimage.module to:

$result = db_query("SELECT title, weight, n.nid FROM {headerimage} hi JOIN {node} n ON n.nid = hi.nid AND hi.block = '%s' ORDER BY weight, nid ASC", $delta);

Comment #2

cilkay commented 5 June 2009 at 05:55

Also had to change line 598 headerimage.module to:

$block_has_nodes = db_result(db_query("SELECT nid FROM {headerimage} WHERE block = '%s'", $delta));

Changed line 603 to:

$block_in_region = db_result(db_query("SELECT region FROM {blocks} WHERE module = '%s' AND theme = '%s' AND delta = '%s'", 'headerimage', variable_get('theme_default', 'garland'), $delta));

Basically, anywhere you have %d in the query and you're passing a string to it, you need to change to '%s'. The quotes around the %s are significant.