My sql Input output stored procedure throwing error while calling from php code

Question

Hi I have stored procedure written in MySql 5.6 which have 2 input parameter and 2 output parameter ,while i am calling this stored procedure from my php code it throw below error :

Error Code: 1414. OUT or INOUT argument 2 for routine sp name is not a variable or NEW pseudo-variable in BEFORE trigger .

I am using below code:

public function get_brand_code_nd_model($phonemake,$phonemodule)
{
    $db1=$this->load->database('fonesherpa', TRUE);
    $query = $db1->query("Call GetModelnBrandCodeFromName('$phonemake','$phonemodule',@BrandCode,@ModelCode)");

}

I am using below SP

CREATE DEFINER="root"@"localhost" PROCEDURE "GetModelnBrandCodeFromName"(

_BrandName nvarchar(100),
out _BrandCode int ,
_ModelName nvarchar(100),
out _ModelCode int

)
BEGIN


declare temp_IntBrandC int;
declare temp_IntModelC varchar(100);



Set temp_IntBrandC= (Select CompanyCode From MasterMobileBrand where CompanyName =_BrandName);


IF (temp_IntBrandC is NULL) then
Begin

 -- select IntBrandC;
    Set temp_IntBrandC = (Select CompanyCode From MasterMobileBrand where UserAgentName =_BrandName);

    -- print @Intbrandc
End;
end if;
IF (temp_IntBrandC is not NULL) then
Begin
-- select @IntBrandC;
    SET _BrandCode = temp_IntBrandC ;
    -- select _brandcode;
    -- Print @brandcode
    SET temp_IntModelC = (select  ModelCode from MobileModel where BrandCode = _BrandCode and ModelName like CONCAT('%',_ModelName,'%') limit 1);
      -- select @IntModelc;
    -- Insert if Model Code does not exist in database
    If(temp_IntModelC is NULL) then
    Begin
-- select "hello";
 set @maxmobcode=(Select (MAX(ModelCode) + 1) From MobileModel);
        Insert Into MobileModel(companyname, ModelCode, BrandCode, ModelName, ImageName, IsJavaPhone)
        Values(_BrandName,@maxmobcode, _BrandCode, _ModelName, NULL, False);
        -- get newly inserted ModelCode
        SET temp_IntModelC = (select  ModelCode from MobileModel where BrandCode = _BrandCode and ModelName like   CONCAT('%',_ModelName,'%') limit 1);

        --
-- Print @IntModelc
    End;
    end if;
    If(temp_IntModelC is not NULL) then
    Begin
        SET _ModelCode = temp_IntModelC;
        -- select _brandcode;
    -- Print @Modelcode
    End;
    Else
    Begin
        SET _ModelCode = -1;
    End;
end if;
End;
Else
Begin
    SET _BrandCode = -1;
    SET _ModelCode = -1;

end;
end if;

End

Please help me how to solve this issue.

Can you include the code for the stored procedure in your question please? — madebydavid, Jan 27 '14 at 12:05
maybe this can help you: http://stackoverflow.com/questions/13382922/calling-stored-procedure-with-out-parameter-using-pdo also here is a complete example of how to call an SP with php: http://www.joeyrivera.com/2009/using-mysql-stored-procedures-with-php-mysqlmysqlipdo/ — Ruben Verschueren, Jan 27 '14 at 13:11

score 0 · Accepted Answer · answered Jan 28 '14 at 05:57

I have made the changes on SP and remove output parameter and then call it from PHP code now its working fine.Below changes I made in SP:

DELIMITER $$

    CREATE DEFINER="root"@"localhost" PROCEDURE "GetModelnBrandCodeFromName"(

    _BrandName nvarchar(100),
    -- out _BrandCode int ,
    _ModelName nvarchar(100)
    -- out _ModelCode int

    )
    BEGIN




    -- Declare variables to store result set during query execution :

    declare temp_IntBrandC int;
    declare temp_IntModelC varchar(100);



    Set temp_IntBrandC= (Select CompanyCode From MasterMobileBrand where CompanyName =_BrandName);

      -- select @IntBrandc;

    -- If not, then check if it matches the user agent name
    -- that is usually passed in HTTP headers 
    -- For e.g. when Blackberry is the company name the User Agent name
    -- is RIM 
    IF (temp_IntBrandC is NULL) then
    Begin

     -- select IntBrandC;
        Set temp_IntBrandC = (Select CompanyCode From MasterMobileBrand where UserAgentName =_BrandName);

        -- print @Intbrandc
    End;
    end if;
    IF (temp_IntBrandC is not NULL) then
    Begin
    -- select @IntBrandC;
        -- SET _BrandCode = temp_IntBrandC ;
        -- select _brandcode;

    -- select Brandcode from mobilemodel where brandcode=temp_IntBrandC limit 1;
        -- Print @brandcode
        SET temp_IntModelC = (select  ModelCode from MobileModel where BrandCode = temp_IntBrandC and ModelName like CONCAT('%',_ModelName,'%') limit 1);
          -- select @IntModelc;
        -- Insert if Model Code does not exist in database
        If(temp_IntModelC is NULL) then
        Begin
    -- select "hello";
     set @maxmobcode=(Select (MAX(ModelCode) + 1) From MobileModel);
            Insert Into MobileModel(companyname, ModelCode, BrandCode, ModelName, ImageName, IsJavaPhone)
            Values(_BrandName,@maxmobcode, temp_IntBrandC, _ModelName, NULL, False);
            -- get newly inserted ModelCode
            SET temp_IntModelC = (select  ModelCode from MobileModel where BrandCode = temp_IntBrandC and ModelName like   CONCAT('%',_ModelName,'%') limit 1);

            --
    -- Print @IntModelc
        End;
        end if;
        If(temp_IntModelC is not NULL) then
        Begin
            -- SET _ModelCode = temp_IntModelC;

    select Brandcode,Modelcode from mobilemodel where modelcode=temp_IntModelC limit 1;
            -- select _brandcode;
        -- Print @Modelcode
        End;
        Else
        Begin
            -- SET _ModelCode = -1;
    set temp_IntModelC=-1;
    select temp_IntModelC as ModelCode;
        End;
    end if;
    End;
    Else
    Begin
        -- SET _BrandCode = -1;
        -- SET _ModelCode = -1;
        -- select _brandcode,_ModelCode;
    set temp_IntBrandC=-1;
    set temp_IntModelC=-1;
    select temp_IntBrandC as Brandcode,temp_IntModelC as Modelcode;
         -- print @brandcode
        -- print @modelcode
    end;
    end if;

    End

and the php code for it :

public function get_brand_code_nd_model($phonemake,$Format,$phonemodule)
{

    $query = $db1->query("Call GetModelnBrandCodeFromName('$phonemake','$phonemodule')");


            if($query->num_rows() == 0)
                {
                    header('HTTP/1.1 404 Content not found');
                    //echo "Hi";

                    //log_message('error','Content not found',TRUE);
                }




                if($Format=='json' || $Format=='JSON' || empty($Format))

                    {
                        //echo "hi";
                        return json_encode($query->result_array());
                    }

                    if($Format=='xml' || $Format=='XML')
                {

                        $this->load->library('xml_writer');

                        foreach ($query->result_array() as $value)

                            {
                                $xml = Xml_writer::createXML('activation', $value);
                                echo $xml->saveXML();
                            }
                }

My sql Input output stored procedure throwing error while calling from php code

1 Answers1