TechNote

How to fix “the SQL constructed is invalid” error when using cfproperty with ORM enabled CFC’s?

Issue

Coldfusion throws an exception "SQL constructed is invalid" if the table name has spaces in the sql assigned to formula attribute of cfproperty.

Reason

cfproperty has an attribute formula which is assigned a SQL statement that will compute a value of the column in a table. If the SQL for the property has a table name with a space escaped by square brackets, the following exception is thrown "the SQL constructed is invalid".

Example

<cfproperty name="formulacol" formula="select count(*) from [table with space]">

Solution

To resolve this issue, ensure that you do not use square brackets for table names when there are spaces in the table name. Instead, use aliases for table names.