PDO Exception when creating a node with a workflow attached when using Oracle database

Issue on the Oracle project

#1443170: PDO Exception when adding a workflow to a node using the Workflow module

The offending code is the following:

  // Yes. This is a monster. This should, in all absolute seriousness, be cut down to size.
  // I also REALLY do not like the insecure field pieces in here.
  $results = db_query(''
      . '(SELECT t.tid, t.' . $field . ' as state_id, s.state AS state_name, s.weight AS state_weight '
        . 'FROM {workflow_transitions} t '
        . 'INNER JOIN {workflow_states} s ON s.sid = t.' . $field . ' '
        . 'WHERE t.' . $field_where . ' = :sid AND s.status = 1 '
        . 'ORDER BY state_weight) '
    . 'UNION '
      . '(SELECT s.sid as tid, s.sid as state_id, s.state as state_name, s.weight as state_weight '
        . 'FROM {workflow_states} s '
        . 'WHERE s.sid = :sid AND s.status = 1) '
    . 'ORDER BY state_weight, state_id',
    array(':sid' => $sid));
  foreach ($results as $transition) {

What if we converted this to use db_select? Let Drupal generate the SQL code as required instead of using db_query directly?

For example

  // Top Query
  $query = db_select('workflow_transitions', 't');
  $query->innerJoin('workflow_states', 's', 's.sid = t.target_sid');
  $query->addField('t', 'tid');

  // Check the direction of the query
  if ($dir == 'to') {
    $query->addField('t', 'target_sid', 'state_id');
    $query->condition('t.sid', $sid);
  }
  else {
    $query->addField('t', 'sid', 'state_id');
    $query->condition('t.target_sid', $sid);
  }

  $query->addField('s', 'state', 'state_name');
  $query->addField('s', 'weight', 'state_weight');

  $query->condition('s.status', 1);
  //$query->orderBy('state_weight');

  // Bottom Query
  $query2 = db_select('workflow_states', 's');

  $query2->addField('s', 'sid', 'tid');
  $query2->addField('s', 'sid', 'state_id');
  $query2->addField('s', 'state', 'state_name');
  $query2->addField('s', 'weight', 'state_weight');

  $query2->condition('s.sid', $sid);
  $query2->condition('s.status', 1);

  $query2->orderBy('state_weight');
  $query2->orderBy('state_id');

  // Add the union of the two queries
  $query->union($query2, 'UNION');

  $results = $query->execute();

By using "addField" you can build in the conditions for the direction of the query without any inline string stuff as the original has.

Not tested at all, but short of any typos it should work.

EDIT: Tested and it works with MySQL. The location of the orderBy methods is important (seem to only be available on the second query)

Alright, so that fixed the syntax error, but now I get another issue

PDOException: SELECT t.tid AS tid, t.target_sid AS state_id, s.state AS state_name, s.weight AS state_weight FROM {workflow_transitions} t INNER JOIN {workflow_states} s ON s.sid = t.target_sid WHERE (t.sid = :db_condition_placeholder_0) AND (s.status = :db_condition_placeholder_1) ORDER BY state_weight ASC UNION SELECT s.sid AS tid, s.sid AS state_id, s.state AS state_name, s.weight AS state_weight FROM {workflow_state} s WHERE (s.sid = :db_condition_placeholder_2) AND (s.status = :db_condition_placeholder_3) ORDER BY state_weight ASC, state_id ASC (prepared: SELECT t.tid AS tid, t.target_sid AS state_id, s.state AS state_name, s.weight AS state_weight FROM "WORKFLOW_TRANSITIONS" t INNER JOIN "WORKFLOW_STATES" s ON s.sid = t.target_sid WHERE (t.sid = :db_condition_placeholder_0) AND (s.status = :db_condition_placeholder_1) ORDER BY state_weight ASC UNION SELECT s.sid AS tid, s.sid AS state_id, s.state AS state_name, s.weight AS state_weight FROM "WORKFLOW_STATE" s WHERE (s.sid = :db_condition_placeholder_2) AND (s.status = :db_condition_placeholder_3) ORDER BY state_weight ASC, state_id ASC ) e: SQLSTATE[HY000]: General error: 933 OCIStmtExecute: ORA-00933: SQL command not properly ended (ext\pdo_oci\oci_statement.c:148) args: Array ( [:db_condition_placeholder_0] => 1 [:db_condition_placeholder_1] => 1 [:db_condition_placeholder_2] => 1 [:db_condition_placeholder_3] => 1 ) in workflow_allowable_transitions() (line 1378 of D:\wwwroot\mytc\sites\all\modules\contrib\workflow\workflow.module).

Here's the generated SQL

SELECT t.tid AS tid, t.target_sid AS state_id, s.state AS state_name, s.weight AS state_weight
FROM 
{workflow_transitions} t
INNER JOIN {workflow_states} s ON s.sid = t.target_sid
WHERE  (t.sid = :db_condition_placeholder_0) AND (s.status = :db_condition_placeholder_1) 
ORDER BY state_weight ASC, state_id ASC UNION ALL SELECT s.sid AS tid, s.sid AS state_id, s.state AS state_name, s.weight AS state_weight
FROM 
{workflow_state} s
WHERE  (s.sid = :db_condition_placeholder_0) AND (s.status = :db_condition_placeholder_1) 

Can anyone see something wrong with the generated UNION query?

I've tested the above rewrite of the query in comment #3 and it seems to work in both Oracle and MySQL.

Do you require a patch file or is this code block acceptable?