Yii Framework Forum: Error Call Store Procedure Ms Sql Server - Yii Framework Forum

Jump to content

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

Error Call Store Procedure Ms Sql Server Rate Topic: -----

#1 User is offline   martinus 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 20
  • Joined: 23-June 14

Posted 02 July 2014 - 05:06 AM

hello,

I have code like below :
public function actionLaporanKSPA(){
		$NBA = '01-15-26400';
		$UserID = 'uli';

		  $command = Yii::app()->db->createCommand('call rKSPA (:NBA,:UserID )');  
		  $command->bindParam('NBA', $NBA);   
		  $command->bindParam('UserID', $UserID);  
		  $result = $command ->execute();  
		
	}


but I get error like below after executed the action :
 CDbCommand failed to execute the SQL statement: SQLSTATE[HY000]: General error: 102 General SQL Server error: Check messages from the SQL Server [102] (severity 15) [(null)]. The SQL statement executed was: call rKSPA (:NBA,:UserID )

/var/www/html/yii/framework/db/CDbCommand.php(357)

345         {
346             if($this->_connection->enableProfiling)
347                 Yii::endProfile('system.db.CDbCommand.execute('.$this->getText().$par.')','system.db.CDbCommand.execute');
348 
349             $errorInfo=$e instanceof PDOException ? $e->errorInfo : null;
350             $message=$e->getMessage();
351             Yii::log(Yii::t('yii','CDbCommand::execute() failed: {error}. The SQL statement executed was: {sql}.',
352                 array('{error}'=>$message, '{sql}'=>$this->getText().$par)),CLogger::LEVEL_ERROR,'system.db.CDbCommand');
353 
354             if(YII_DEBUG)
355                 $message.='. The SQL statement executed was: '.$this->getText().$par;
356 
357             throw new CDbException(Yii::t('yii','CDbCommand failed to execute the SQL statement: {error}',
358                 array('{error}'=>$message)),(int)$e->getCode(),$errorInfo);
359         }
360     }
361 
362     /**
363      * Executes the SQL statement and returns query result.
364      * This method is for executing an SQL query that returns result set.
365      * @param array $params input parameters (name=>value) for the SQL execution. This is an alternative
366      * to {@link bindParam} and {@link bindValue}. If you have multiple input parameters, passing
367      * them in this way can improve the performance. Note that if you pass parameters in this way,
368      * you cannot bind parameters or values using {@link bindParam} or {@link bindValue}, and vice versa.
369      * Please also note that all values are treated as strings in this case, if you need them to be handled as




please, help me
0

#2 User is offline   Joblo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 668
  • Joined: 12-September 10
  • Location:Austria

Posted 03 July 2014 - 11:04 AM

Maybe with exec?
$command = $db->createCommand('exec rKSPA :NBA,:UserID');
...

0

#3 User is offline   Joblo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 668
  • Joined: 12-September 10
  • Location:Austria

Posted 04 July 2014 - 03:04 AM

Some examples for MSSql stored procedures working for me:

 $sql = 'EXEC myStoredProc @Param1=:Param1,@Param2=:Param2';
 $command = $db->createCommand($sql);
 $command->bindParam(':Param1', $Param1);
 $command->bindParam(':Param2', $Param2);

  $command->queryAll(); //queryRow()



 $sql = 'DECLARE @errorCode INTEGER; EXEC @errorCode = myStoredProc @Param1=:Param1,@Param2=:Param2';
 $command = $db->createCommand($sql);
 $command->bindParam(':Param1', $Param1);
 $command->bindParam(':Param2', $Param2);

  $command->queryScalar(); //the errorcode

0

#4 User is offline   martinus 

  • Junior Member
  • Pip
  • Yii
  • Group: Members
  • Posts: 20
  • Joined: 23-June 14

Posted 04 July 2014 - 10:56 AM

View PostJoblo, on 04 July 2014 - 03:04 AM, said:

Some examples for MSSql stored procedures working for me:

 $sql = 'EXEC myStoredProc @Param1=:Param1,@Param2=:Param2';
 $command = $db->createCommand($sql);
 $command->bindParam(':Param1', $Param1);
 $command->bindParam(':Param2', $Param2);

  $command->queryAll(); //queryRow()



 $sql = 'DECLARE @errorCode INTEGER; EXEC @errorCode = myStoredProc @Param1=:Param1,@Param2=:Param2';
 $command = $db->createCommand($sql);
 $command->bindParam(':Param1', $Param1);
 $command->bindParam(':Param2', $Param2);

  $command->queryScalar(); //the errorcode



thanks joblo for your reply,
I will try it soon,,,
0

#5 User is offline   Joblo 

  • Master Member
  • PipPipPipPip
  • Yii
  • Group: Members
  • Posts: 668
  • Joined: 12-September 10
  • Location:Austria

Posted 04 July 2014 - 01:54 PM

Just detected in your code a typo?
Missing ":" in your bindParam;


//$command->bindParam('NBA', $NBA); wrong  

//must be
$command->bindParam(':NBA', $NBA);   


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