Yii Framework Forum: Solved - Bindparam Not Working Properly - Yii Framework Forum

Jump to content

Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

Solved - Bindparam Not Working Properly Rate Topic: -----

#1 User is offline   rpaulpen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 136
  • Joined: 01-December 08

Posted 04 March 2013 - 01:09 PM

I've spent countless hours trying to figure out why my sql statement won't work when I use bindParam. I've searched out here and found some threads, but none seem to resolve my problem.

I'm not getting any errors, but it just seems not to be binding to the value therefore nothing is being returned.

Here is my code:

$connection=Yii::app()->db;
$sql="UPDATE narratives SET description = '$this->description', content = '$this->content' WHERE id = ':id'"; 			
$command=$connection->createCommand($sql);
$command->bindParam(':id',$this->id,PDO::PARAM_STR);
$result = $command->execute(explode(" ",$sql)); 
return $result;


Here is a dump from the logs:
SELECT * FROM narratives WHERE id = 'remotest'
2013/03/04 12:47:47 [trace] [system.db.CDbCommand] Executing SQL: UPDATE narratives SET description = 
'For testing', content = '<p>This is test data</p>' WHERE id = ':id'. Bound with :id='remotest', 
0='UPDATE', 1='narratives', 2='SET', 3='description', 4='=', 5='\'For', 6='testing\',', 
7='content', 8='=', 9='\'<p>This', 10='is', 11='test', 12='data</p>\'', 13='WHERE', 14='id', 15='=', 16='\':id\''



Here is a vardump of $command which seems to be substituting :id with the actual value "remotest":
[font=Times][size=2]CDbCommand#1 ( [params] => array() [CDbCommand:_connection] => CDbConnection#2 ( [connectionString] => 'mysql:host=localhost;dbname=ng' [username] => 'ng' [password] => 'mypassword' [schemaCachingDuration] => 0 [schemaCachingExclude] => array() [schemaCacheID] => 'cache' [queryCachingDuration] => 0 [queryCachingDependency] => null [queryCachingCount] => 0 [queryCacheID] => 'cache' [autoConnect] => true [charset] => null [emulatePrepare] => null [enableParamLogging] => 'true' [enableProfiling] => false [tablePrefix] => null [initSQLs] => null [driverMap] => array ( 'pgsql' => 'CPgsqlSchema' 'mysqli' => 'CMysqlSchema' 'mysql' => 'CMysqlSchema' 'sqlite' => 'CSqliteSchema' 'sqlite2' => 'CSqliteSchema' 'mssql' => 'CMssqlSchema' 'dblib' => 'CMssqlSchema' 'sqlsrv' => 'CMssqlSchema' 'oci' => 'COciSchema' ) [pdoClass] => 'PDO' [CDbConnection:_attributes] => array() [CDbConnection:_active] => true [CDbConnection:_pdo] => PDO#3 ( ) [CDbConnection:_transaction] => null [CDbConnection:_schema] => null [behaviors] => array() [CApplicationComponent:_initialized] => true [CComponent:_e] => null [CComponent:_m] => null ) [CDbCommand:_text] => 'UPDATE narratives SET description = \'For testing\', content = \'[/size][/font][font=Times][size=2]This is test data[/size][/font]

[font=Times][size=2]\' WHERE id = \'remotest\'' [CDbCommand:_statement] => PDOStatement#4 ( [queryString] => 'UPDATE narratives SET description = \'For testing\', content = \'[/size][/font][font=Times][size=2]This is test data[/size][/font]

[font=Times][size=2]\' WHERE id = \'remotest\'' ) [CDbCommand:_paramLog] => array() [CDbCommand:_query] => null [CDbCommand:_fetchMode] => array ( '0' => 2 ) [CComponent:_e] => null [CComponent:_m] => null )Did it work?1[/size][/font]


Lastly, if I use the same code as above without any bindParam, it works fine. here is the sample code of that:

$connection=Yii::app()->db;
$sql="UPDATE narratives SET description = '$this->description', content = '$this->content' WHERE id = '$this->id'"; 			
$command=$connection->createCommand($sql);
$result = $command->execute(explode(" ",$sql)); 
return $result;


I would really appreciate some direction.


thanks,
0

#2 User is offline   clapas 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 39
  • Joined: 21-February 11

Posted 04 March 2013 - 02:04 PM

Please try removing the quotes for the param. You have this

WHERE id = ':id'"


Try this:

WHERE id = :id"

0

#3 User is offline   rpaulpen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 136
  • Joined: 01-December 08

Posted 04 March 2013 - 02:06 PM

View Postclapas, on 04 March 2013 - 02:04 PM, said:

Please try removing the quotes for the param. You have this

WHERE id = ':id'"


Try this:

WHERE id = :id"



Thanks for your reply, but have already tried that and if gives the following error:
CDbCommand failed to execute the SQL statement: SQLSTATE[HY093]: 
Invalid parameter number: number of bound variables does not match 
number of tokens. The SQL statement executed was: UPDATE narratives 
SET description = 'For testing', content = '<p>This is test data</p>' 
WHERE id = :id. Bound with :id='remotest', 0='UPDATE', 1='narratives', 2='SET',
 3='description', 4='=', 5='\'For', 6='testing\',', 7='content', 8='=', 9='\'<p>This', 
10='is', 11='test', 12='data</p>\'', 13='WHERE', 14='id', 15='=', 16=':id'




0

#4 User is offline   seenivasan 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 620
  • Joined: 17-June 12
  • Location:Chennai,TamilNadu,India.

Posted 04 March 2013 - 02:26 PM

Dear Friend

Would you please check the following?

$connection=Yii::app()->db;
$sql='UPDATE narratives SET description = :description, content = :content WHERE id = :id'; //No sub quotes                 
$command=$connection->createCommand($sql);
$command->bindParam(':description',$this->description,PDO::PARAM_STR);
$command->bindParam(':content',$this->content,PDO::PARAM_STR);
$command->bindParam(':id',$this->id,PDO::PARAM_STR);
$command->execute(); //Here there is no need to pass parameters, as we have already called bindParam method.

0

#5 User is offline   clapas 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 39
  • Joined: 21-February 11

Posted 04 March 2013 - 02:39 PM

Yes, I think that's because your param is an integer, and you are using PDO::PARAM_STR which is for strings.
0

#6 User is offline   rpaulpen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 136
  • Joined: 01-December 08

Posted 04 March 2013 - 02:43 PM

View Postseenivasan, on 04 March 2013 - 02:26 PM, said:

Dear Friend

Would you please check the following?

$connection=Yii::app()->db;
$sql='UPDATE narratives SET description = :description, content = :content WHERE id = :id'; //No sub quotes                 
$command=$connection->createCommand($sql);
$command->bindParam(':description',$this->description,PDO::PARAM_STR);
$command->bindParam(':content',$this->content,PDO::PARAM_STR);
$command->bindParam(':id',$this->id,PDO::PARAM_STR);
$command->execute(); //Here there is no need to pass parameters, as we have already called bindParam method.



Yes, that worked! Now that I read your code, it makes perfect sense.

Thanks very much!






0

#7 User is offline   rpaulpen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 136
  • Joined: 01-December 08

Posted 04 March 2013 - 02:45 PM

View Postseenivasan, on 04 March 2013 - 02:26 PM, said:

Dear Friend

Would you please check the following?

$connection=Yii::app()->db;
$sql='UPDATE narratives SET description = :description, content = :content WHERE id = :id'; //No sub quotes             	
$command=$connection->createCommand($sql);
$command->bindParam(':description',$this->description,PDO::PARAM_STR);
$command->bindParam(':content',$this->content,PDO::PARAM_STR);
$command->bindParam(':id',$this->id,PDO::PARAM_STR);
$command->execute(); //Here there is no need to pass parameters, as we have already called bindParam method.




Just to clarify, this reply worked.
0

#8 User is offline   rpaulpen 

  • Standard Member
  • PipPip
  • Yii
  • Group: Members
  • Posts: 136
  • Joined: 01-December 08

Posted 04 March 2013 - 02:48 PM

View Postclapas, on 04 March 2013 - 02:39 PM, said:

Yes, I think that's because your param is an integer, and you are using PDO::PARAM_STR which is for strings.


No, the are strings. I did get Seenivasan's solution to work. Thanks for your help.



0

Share this topic:


Page 1 of 1
  • You cannot start a new topic
  • You cannot reply to this topic

1 User(s) are reading this topic
0 members, 1 guests, 0 anonymous users