can u please telme how can we call a procedure on login time…that procedure should verify the user in yii
can u please telme how can we call a procedure on login time…that procedure should verify the user in yii
Sorry for late reply.
its very simple. just do it:
mysql_query("call my_procedure()");
Hi,
In an action I need to call a stored procedure to duplicate rows in a DB table,
I have a query to get the values to pass to my stored procedure.
In my controller:-
public function actionAdmin(){
$date= "SELECT DATE(now())";
$mydate = Yii::app()->db->createCommand($date)->queryScalar();
$count="select classId,admissionId from studentattendances where Date=$mydate";
$resultset=array();
$resultset=Yii::app()->db->createCommand($count)->query();
foreach($resultset as $result){
$sql=“CALL multiple_records(’’,’$result->classId’,’$result->admissionId’,’’,’$mydate’,‘1’)”;
Yii::app()->db->createCommand($sql)->execute();
}
But when my action is executed i am unable to get the duplicated rows… Please help me
In your producer, after insert, there should be a select query
[color="#556B2F"]/* The thread has been split from a thread in "Job Opportunities" */[/color]
sample code to execute procedure
//change the status of observation automatically using the stored preocedure
$checkopen="EXEC Incident_Status_Change ".$inc_id.",".$emp_id;
$resultoaction = $connection->createCommand($checkopen)->execute();
actual procedure
-- =============================================
-- Author: Kumar Kulandai
-- Create date: 07 Nov 2012
-- Description: Update the Incident status based on the Recommendation Status and Published
-- =============================================
ALTER PROCEDURE [dbo].[Incident_Status_Change] (
@Incident_Id float,
@Empl_Id int
)
AS
BEGIN
-- Declare variables
DECLARE @recompublished as INT
DECLARE @incidentstatus as VARCHAR(1)
DECLARE @totalrecom as INT
DECLARE @totalclosedrecom as INT
DECLARE @isinclocked as INT
-- To Change the status of the Incident based on the Recommendation Published or not
-- Get the Total Published
SET @recompublished = (SELECT
COUNT(Published) AS Published
FROM
Incident_Recommendation
WHERE
Inc_Id=@Incident_Id AND
Emp_LM_Id=@Empl_Id AND
Record_Status=1 AND
Published=1)
-- Check the status to update
-- if more than one recommendation published then change the status to Progress
-- if no recommendation was published then change the status to Draft
SELECT @recompublished;
IF @recompublished>=1
BEGIN
SET @incidentstatus = 'P'
END
IF @recompublished=0
BEGIN
SET @incidentstatus = 'D'
END
SELECT @incidentstatus
-- Change the observation status based on the Total Published
UPDATE
Incident_Details
SET
Incident_Status=@incidentstatus
FROM Incident_Details a,
Incident_Recommendation b
WHERE
a.Incident_Id=b.Inc_Id AND
a.Empl_Id= b.Emp_LM_Id AND
a.Incident_Id=@Incident_Id AND
a.Empl_Id=@Empl_Id AND
Incident_Status<>'@incidentstatus'
-- To Change the status of the Incident Status to Closed based on the All Recommendation Status
-- this functionality need to confirm with client
SET @totalrecom = (SELECT
COUNT(Rec_Id) AS Rec_Id
FROM
Incident_Recommendation
WHERE
Inc_Id=@Incident_Id AND
Emp_LM_Id=@Empl_Id AND
Record_Status=1)
SET @totalclosedrecom = (SELECT
COUNT(Rec_Id) AS Rec_Id
FROM
Incident_Recommendation
WHERE
Inc_Id=@Incident_Id AND
Emp_LM_Id=@Empl_Id AND
Record_Status=1 AND
Rec_Status_Id='C')
SET @isinclocked = (SELECT
Locked_By
FROM
Incident_Details
WHERE
Incident_Id=@Incident_Id AND
Empl_Id=@Empl_Id AND
Record_Status=1)
select @isinclocked
IF @totalclosedrecom=@totalrecom AND @totalrecom>=1 AND @isinclocked=0
BEGIN
UPDATE
Incident_Details
SET
Incident_Status='C'
WHERE
Incident_Id=@Incident_Id AND
Empl_Id=@Empl_Id
END
END