For an hour or so, I have been debugging an SQL error thrown by the PostgresSQL in my CakePHP application. The error message was also not really helpful. Below is the SQL generated by the CakePHP update function
UPDATE "public"."posts" SET "value" = asap WHERE id = (1)
SQLSTATE[42703]:
Undefined column: 7 ERROR: column "asap" does not exist LINE 1:
The culprit turns out to be that how quote is used to identify a string in SQL.
I have to explicitly put the single quote in the string value ‘asap’ in the CakePHP function below when updating a record of the Model Post.
The below is the signature of the updateAll function from CakePHP library.
public function updateAll($fields, $conditions);
The tricky part is you can’t quote strings in the conditions, but for the updating value of the fields, the string has to be single quoted.
$this->Post->updateAll(
array('Post.value' => "'asap'" ),
array('Post.key = ' => "when"));
A further discussion can be seen at the stackoverflow page
http://stackoverflow.com/questions/1992314/what-is-the-difference-between-single-and-double-quotes-in-sql