The recent change to entity API/field collections that added in revision support broke entities (and field collections) for a site I had running on SQL Server 2008. Much debugging later, it turns out that SQL Server doesn't have support for the Boolean data type, which is used to check whether the current revision is the default one in EntityAPIController's buildQuery() function.

The included patch changes the
"SELECT base.{$this->revisionKey} = revision.{$this->revisionKey} AS {$this->defaultRevisionKey}"
query to
"SELECT CASE WHEN base.{$this->revisionKey} = revision.{$this->revisionKey} THEN 1 ELSE 0 END AS {$this->defaultRevisionKey}".

CommentFileSizeAuthor
entity.revisions_sql_server.patch744 bytestorpy

Comments

torpy’s picture

torpy commented
Status: Active » Needs review
askildolsen’s picture

askildolsen commented

Ran into the same problem, and the supplied patch works for me on SQL Server 2012.
How will the patch work on other DBMSes, when it returns 0 and 1 instead of true/false?

torpy’s picture

torpy commented

It should still work on MySQL/PostgreSQL :)
See - http://drupal.org/node/555540

rob_johnston’s picture

rob_johnston commented

This works for me with my bean creation problem #1917732: Incorrect syntax near '=' when creating a bean on MS SQL Server on Windows/IIS7/SQL Server 2008 and I also tested it with Linux/Apache2/MySQL without problems.

pabloroberto27’s picture

pabloroberto27 commented

The patch solves the problem when using SQL Server 2008. Thanks a lot!

azinck’s picture

azinck commented
Status: Needs review » Reviewed & tested by the community

Works for me on SQL Server 2012. Tentatively setting to RTBC due to the number of positive reviews.

fago’s picture

fago
Primary language German
Location Vienna
commented
Status: Reviewed & tested by the community » Postponed (maintainer needs more info)

Sounds resonable, but does this work with all support mysql, postgres versions also? Let's research/test that.

rymo’s picture

rymo
he/him
commented

Using this patch in production, thank you.

rob_johnston’s picture

rob_johnston commented
Status: Postponed (maintainer needs more info) » Reviewed & tested by the community

I've tested on PostGres 9.1.9 and, as expected, it works. Combined with comment #4 it has also been tested on mysql and sqlsrv, so RTBC now?

emudojo’s picture

emudojo commented

I can attest that this works as expected

luukg’s picture

luukg commented

Works with Oracle.

fago’s picture

fago
Primary language German
Location Vienna
commented

entity.revisions_sql_server.patch queued for re-testing.

fago’s picture

fago
Primary language German
Location Vienna
commented
Issue summary: View changes
Status: Reviewed & tested by the community » Fixed

according to the docs MySQL 5 shouldn't be problematic either, thus committed.

Status: Fixed » Closed (fixed)

Automatically closed - issue fixed for 2 weeks with no activity.