CDbExpression: problem with quotes

Archives Bug Discussions
1

On PostgreSQL I do following:


$lastDateProcessed = Yii::app()->db->createCommand()

	->select(new CDbExpression("to_char(MAX(when_processed), :df)", array(':df'=>'YYYY-MM-DD')))

	->from('service.import_gld360_filenames')

	->queryScalar();

And get an SQL syntax error which shows me the query as


The SQL statement executed was:

SELECT to_char(MAX(when_processed), ":df)" FROM "service"."import_gld360_filenames"

The same result I get without parametrization.

What do I do wrong? Is that a bug?

Thanks.

2

select() does not take params as second parameter:

http://www.yiiframework.com/doc/api/1.1/CDbCommand#select()-detail

and as your param is constant it sould just work like this:


$lastDateProcessed = Yii::app()->db->createCommand()

	->select("to_char(MAX(when_processed), 'YYYY-MM-DD')")

	->from('service.import_gld360_filenames')

	->queryScalar();
3

Thank you! It works.

But you didn’t realize me correctly. I didn’t pass params to select I did it to CDbExpression() (constructor).

So we can use constant expressions in select() but loss advantages of DAO (which are very useful for working with quotes first of all).

4

IMO, your first example shows the bug OR such a behavior isn’t documented properly - it is said CDbExpression is mainly used in CActiveRecord as attribute values, but your example works ONLY for setting attribute value (at least what I’ve got from my tests) properly.

In your example expression is partially escaped, and parameter is lost


Invalid parameter number: noparameters were bound.

 The SQL statement executed was:

 SELECT to_char(MAX(when_processed), `:df)`FROM `service`.`import_gld360_filenames`

When using with CDbCriteria, parameter is lost, but :df isn’t escaped. Example:


$criteria = new CDbCriteria(array( 

  'select'=>new CDbExpression('to_char(MAX(when_processed), :df)', array(':df'=>'YYYY-MM-DD')), 

));

$user = Users::model()->find($criteria);

 Invalid parameter number: noparameters were bound.

 The SQL statement executed was:

 SELECT to_char(MAX(when_processed), :df) FROM `users` `t` LIMIT 1.

Using your expression to set attribute value works properly:


INSERT INTO `users` (`name`) VALUES (to_char(MAX(when_processed), :df)). Bound with :df='YYYY-MM-DD'