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

问题描述

更新

列出VALUES时，我犯了一个小错误.我应该输入:username"而不是:alias".我想这个问题的答案是谁想要的自由统治权?还是删除问题?

I was making a petty mistake when listing the VALUES. I should have put ":username" and not ":alias". I suppose the answer credit to this question is free reign for anyone who wants it? Or do I delete the question?

原始

一段时间以来，我一直在使用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]:无效的参数号:未定义参数

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);

FYI $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屋！