PDOException Error When Creating Content with Field Collection module [#1888662]

I have created a content type in which I want a field collection included. I even created a simple field collection only containing one single text field and still have the same error. If I leave the field blank and create the node, there is no issue. If I fill in the field in the field collection I receive the following error message.

Drupal 7.18
MSSQL DB
IIS 7.5
PHP 5.4.10
Occurs with both Entity API 7.x-1.x-dev and 7.x-1.0

PDOException: SQLSTATE[42000]: [Microsoft][SQL Server Native Client 11.0][SQL Server]Incorrect syntax near '='.: SELECT revision.[revision_id] AS [revision_id], base.[item_id] AS [item_id], base.[field_name] AS [field_name], base.[archived] AS [archived], base.revision_id = revision.revision_id AS default_revision FROM {field_collection_item} base INNER JOIN {field_collection_item_revision} revision ON revision.revision_id = base.revision_id WHERE ( ([base].[item_id] IN (:db_condition_placeholder_0)) ); Array ( [:db_condition_placeholder_0] => 5 ) in EntityAPIController->query() (line 187 of C:\inetpub\wwwroot\sites\all\modules\entity\includes\entity.controller.inc).

Comments

Comment #1

boulwarek commented 15 January 2013 at 14:57

Just to verify that there was not some other issue with my Drupal site, I installed a completely new drupal site and only installed the Entity API and Field Collection Modules. I created a Content Type with a field collection and received the exact same error message upon saving the node.

Also, as a side note, I had to apply the patch listed here to allow Drupal to install on PHP 5.4: http://drupal.org/node/1635002#comment-6697530

Comment #2

boulwarek commented 15 January 2013 at 15:19

I also confirmed that it is not related to PHP 5.4 by downgrading to PHP 5.3.2.

Comment #3

boulwarek commented 15 January 2013 at 18:09

I ran the SQL query on my SQL server and concluded that the issue is with the following section of the query:
"base.revision_id = revision.revision_id AS default_revision"

Here is the actual SQL Query that was ran against the SQL Server attained via SQL Server Profiler:
SELECT revision.[revision_id] AS [revision_id], base.[item_id] AS [item_id], base.[field_name] AS [field_name], base.[archived] AS [archived], base.revision_id = revision.revision_id AS default_revision
FROM
field_collection_item base
INNER JOIN field_collection_item_revision revision ON revision.revision_id = base.revision_id
WHERE ( ([base].[item_id] IN (@P1)) )',N'@P1 nvarchar(1)',N'8

Comment #4

vllad commented 16 February 2013 at 01:06

I have same issue here, tracking down the bug based on the SQL query, as boulwarek says: there is a "base.revision_id = revision.revision_id" in the select part of the query, that part is generated on the file
entity/includes/entity.controller.inc on the line 174

$query->addExpression('base.' . $this->revisionKey . ' = revision.' . $this->revisionKey, $this->defaultRevisionKey);

I couldn't find the solution.
I look forward for any ideas.

Comment #5

jayson commented 12 March 2013 at 18:35

Priority:

Normal

» Critical

Hi, I am experiencing the same issue. Has there been any progress on this issue? This is a critical problem, I have bumped up the Priority. After you save a node and get the error, you cannot view that node anymore as this error always appears. Any help or advice or patch would be greatly appreciated.

Comment #6

jayson commented 13 March 2013 at 21:09

Status:

Active

» Needs review

For those who are using SQL Server and would like this fixed, here's how we got it to work.

As vllad posted, we also noticed that the problem is with line 174 in file entity/includes/entity.controller.inc.
$query->addExpression('base.' . $this->revisionKey . ' = revision.' . $this->revisionKey, $this->defaultRevisionKey);

The problem seems to be that this syntax in a SELECT clause is not compatible with SQL Server; it must work in MySQL which is why most people don't have this issue:
base.revision_id = revision.revision_id as default_revision

It should be written this way for SQL Server:
CASE base.revision_id WHEN revision.revision_id THEN base.revision_id ELSE revision.revision_id END as default_revision

So, we solved it by replacing line 174 with this line:
$query->addExpression('CASE base.' . $this->revisionKey . ' WHEN revision.' . $this->revisionKey . ' THEN base.' . $this->revisionKey . ' ELSE revision.' . $this->revisionKey . ' END', $this->defaultRevisionKey);

It would be nice if the module developers could test this for MySQL and incorporate it into a new version.

Thanks, J

Comment #7

entropea commented 22 March 2013 at 14:21

Thanks Jayson.

Confirmed this worked on MSSQL (My problem appeared after using Field Collection for repeating field sets... had to use the dev version to avoid another bug, which meant going to the dev version of EntityAPI).

#6 above worked for me :)