“无效的参数编号:未定义参数"插入数据 [英] "Invalid parameter number: parameter was not defined" Inserting data

问题描述

我使用 Yii 的主动记录模式已经有一段时间了.现在，我的项目需要为一笔小交易访问不同的数据库.我认为 Yii 的 DAO 会对此有好处.但是，我遇到了一个神秘的错误.

I've been using Yii's active record pattern for a while. Now, my project needs to access a different database for one small transaction. I thought the Yii's DAO would be good for this. However, I'm getting a cryptic error.

CDbCommand 执行 SQL 语句失败:SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

这是我的代码:

public function actionConfirmation
{
    $model_person = new TempPerson();

    $model = $model_person->find('alias=:alias',array(':alias'=>$_GET['alias']));
    $connection=Yii::app()->db2;
            $sql = "INSERT INTO users (username, password, ssn, surname
                    , firstname, email, city, country) 
                    VALUES(:alias, :password, :ssn, :surname
                    , :firstname, :email, :city, :country)";
            $command=$connection->createCommand($sql);
            $command->bindValue(":username", $model->alias);
            $command->bindValue(":password", substr($model->ssn, -4,4));
            $command->bindValue(":ssn", $model->ssn);
            $command->bindValue(":surname", $model->lastName);
            $command->bindValue(":firstname", $model->firstName);
            $command->bindValue(":email", $model->email);
            $command->bindValue(":city", $model->placeOfBirth);
            $command->bindValue(":country", $model->placeOfBirth);
            $command->execute();
            $this->render('confirmation',array('model'=>$model));
}

这会构建以下查询(如应用程序日志所示):

This constructs the following query (as seen on the application log):

INSERT INTO users (username, password, ssn, surname, firstname, email
                   , city, country) 
VALUES(:alias, :password, :ssn, :surname, :firstname, :email, :city, :country);

仅供参考 $model->placeOfBirth 应该是城市和县的值.这不是错字(只是我必须做的一件愚蠢的事情).

FYI $model->placeOfBirth is supposed to be in both city and county values. That's not a typo (just a silly thing I have to do).

推荐答案

只是为了提供一个答案 - 因为这个错误很常见 - 这里有几个原因:

Just to provide an answer - because this error is pretty common - here are a few causes:

1) :parameter 名称与绑定错误不匹配(错字?).这就是这里发生的事情.他在SQL语句中有:alias，但是绑定了:username.所以当尝试绑定参数时，Yii/PDO 在 sql 语句中找不到 :username ，这意味着它是一个参数短"并抛出错误.

1) The :parameter name does not match the bind by mistake (typo?). This is what happened here. He has :alias in the SQL statement, but bound :username. So when the param binding was attempted, Yii/PDO could not find :username in the sql statement, meaning it was "one parameter short" and threw an error.

2) 完全忘记为参数添加 bindValue().这在 Yii 其他结构中更容易做到，比如 $critera，你有一个数组或参数 ($criteria->params = array(':bind1'=>'test', ':bind2'=>'test)).

2) Completely forgetting to add the bindValue() for a parameter. This is easier to do in Yii other constructs like $critera, where you have an array or params ($criteria->params = array(':bind1'=>'test', ':bind2'=>'test)).

3) 使用 together 和 joins 时与 CDataProvider 分页和/或排序发生奇怪的冲突.没有具体、简单的方法来表征这一点，但是在 CDataProviders 中使用复杂查询时，我遇到了一些奇怪的问题，即参数被丢弃并发生此错误.

3) Weird conflicts with CDataProvider Pagination and/or Sorting when using together and joins. There is no specific, easy way to characterize this, but when using complex queries in CDataProviders I have had weird issues with parameters getting dropped and this error occurring.

在 Yii 中解决这些问题的一个非常有用的方法是 启用参数在您的配置文件中记录.将此添加到配置文件中的 db 数组中:

One very helpful way to troubleshoot these issues in Yii is to enable parameter logging in your config file. Add this to your db array in your config file:

'enableParamLogging'=>true,

并确保在您的 log 部分中设置了 CWebLogRoute 路由.这将打印出给出和错误的查询，以及它试图绑定的所有参数.超级有用！

And make sure the CWebLogRoute route is set up in your log section. This will print out the query that gave and error, and all of the parameters it was attempting to bind. Super helpful!

这篇关于“无效的参数编号:未定义参数"插入数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！