gpt4 book ai didi

php - 从 php 代码调用时,我的 sql 输入输出存储过程抛出错误

转载 作者:行者123 更新时间:2023-11-30 00:45:39 26 4
gpt4 key购买 nike

嗨,我有用 MySql 5.6 编写的存储过程,它有 2 个输入参数和 2 个输出参数,当我从我的 php 代码调用这个存储过程时,它抛出以下错误:

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

我正在使用以下代码:

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

}

我正在使用以下 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

请帮我解决这个问题。

最佳答案

我已经对 SP 进行了更改并删除了输出参数,然后从 PHP 代码中调用它,现在它工作正常。以下是我在 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

及其 php 代码:

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

关于php - 从 php 代码调用时,我的 sql 输入输出存储过程抛出错误,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/21380159/

26 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com