Upgrading From 1.1.13 To 1.1.14 Invalid Bound Parameter Number Error

ldkafka · January 29, 2014, 3:33am

Hi,

I have a relation which worked perfectly in 1.1.13 and is now broken in 1.1.14 with CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens.

It seems to me that the query builder has issues with bound parameters within an ‘order’ statement - ie. it only counts the number of bound variables within the SQL statement (ignoring the ones in the ORDER BY clause), and then the passed number of parameters does not match the ones in the query (but it would match the overall number of bound variables in the query + order clause).

Can someone confirm if there were changelogs between the 2 versions which would explain this behavior?

Thank you.

Luci

qianggan · January 29, 2014, 9:54pm

It should not about new changes in Yii 1.1.14. The message you have shows the error in the SQL statement. check the count of your Bound variables in the query, and see if it’s match the count of given parameters.

ldkafka · January 29, 2014, 11:40pm

I understand what the error is - I have experienced it many times with faulty queries in the past. My point is that the query is not faulty (ie. I have checked the arguments) and it works perfectly if I point the framework base to an 1.1.13 install. It only breaks if I run it on 1.1.14.

I will try to post an example, which I avoided doing because the relation is reasonably complex and it involves other ‘through’ relations.

I just wanted to see if anyone on the development team may be able to point me to specific changes in the framework which would cause the break of previously working relation code.

qianggan · January 30, 2014, 9:07pm

I will compare below source code, so find out what’s different between 1.1.13 and 1.1.14.

github.com

yiisoft/yii/blob/1.1.14/framework/db/CDbCommand.php

<?php
/**
 * This file contains the CDbCommand class.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.yiiframework.com/
 * @copyright 2008-2013 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

/**
 * CDbCommand represents an SQL statement to execute against a database.
 *
 * It is usually created by calling {@link CDbConnection::createCommand}.
 * The SQL statement to be executed may be set via {@link setText Text}.
 *
 * To execute a non-query SQL (such as insert, delete, update), call
 * {@link execute}. To execute an SQL statement that returns result data set
 * (such as SELECT), use {@link query} or its convenient versions {@link queryRow},
 * {@link queryColumn}, or {@link queryScalar}.

This file has been truncated. show original

github.com

yiisoft/yii/blob/1.1.13/framework/db/CDbCommand.php

<?php
/**
 * This file contains the CDbCommand class.
 *
 * @author Qiang Xue <qiang.xue@gmail.com>
 * @link http://www.yiiframework.com/
 * @copyright Copyright &copy; 2008-2011 Yii Software LLC
 * @license http://www.yiiframework.com/license/
 */

/**
 * CDbCommand represents an SQL statement to execute against a database.
 *
 * It is usually created by calling {@link CDbConnection::createCommand}.
 * The SQL statement to be executed may be set via {@link setText Text}.
 *
 * To execute a non-query SQL (such as insert, delete, update), call
 * {@link execute}. To execute an SQL statement that returns result data set
 * (such as SELECT), use {@link query} or its convenient versions {@link queryRow},
 * {@link queryColumn}, or {@link queryScalar}.

This file has been truncated. show original

qianggan · January 30, 2014, 9:08pm

Would you provide the your PHP code which will help to locate the issue.

ldkafka · February 5, 2014, 12:02am

OK, this is how you replicate what I consider a bug:

You have 2 relations in a model (MODEL1):

‘test’ => array(self::HAS_MANY, ‘MODEL2’, ‘index_id’, ‘order’ => ‘some_attribute = :arg_1, some_attribute = :arg_2’, ‘params’ => array(’:arg_1’ => 1, ‘:arg_2’ => 2)),

‘test2’ => array(self::HAS_MANY, ‘MODEL3’, 'index_id2, ‘through’ => ‘test’)

When you call the test2 relation on a MODEL1 object, you get the error: Invalid parameter number: number of bound variables does not match number of tokens.

This has to do with the variables in the order statemet not being counted, and then ‘params’ have more varables than needed.

This worked OK prior to Yii 1.1.14.

mdomba · February 5, 2014, 8:48am

You are using conditions in the order by… how does this work?

ldkafka · February 6, 2014, 4:15am

IT works very well.

For example, if you have an ENUM column containing some ticket status and you want to SELECT tickets ordered firstly by ‘OPEN’, then ‘IN PROGRESS’, then ‘CLOSED’, etc. you use conditions. This is not a new or illegal practice by any means.

And interestingly, this only stopped working in 1.1.14.

Thank you for replying.

Luci

ldkafka · February 6, 2014, 4:20am

PS. This works OK if I only have 1 relation with the order => ‘:xxx’. It only breaks if I use a second relation which uses the first one through ‘through’.