I created a procedure that is supposed to do some operation but every time I call it, mysql comes out with an error, which I have not clue what it means. I have tried to understand it in vain, here 's the table structure which the stored procedure where made to do an operation on it:
CREATE TABLE `recruitment`.`job_seeker` (
`user_id` INT Null ,
`fname` VARCHAR(45) Null ,
`lname` VARCHAR(45) Null ,
`mname` VARCHAR(45) Null ,
`gender` VARCHAR(10) Null ,
`dob` DATE Null ,
`marital_status` VARCHAR(45) Null ,
`address` VARCHAR(45) Null ,
`city` VARCHAR(45) Null ,
`nationality` VARCHAR(45) Null ,
`phone` VARCHAR(45) Null ,
`mobile` VARCHAR(45) Null ,
`degree_id` INT Null ,
`education` VARCHAR(100) Null ,
`experience` VARCHAR(250) Null ,
`other` VARCHAR(250) Null ,
`job_target` VARCHAR(250) Null ,
PRIMARY KEY (`user_id`) ,
INDEX `user_id` (`user_id` ASC) ,
INDEX `degree_id` (`degree_id` ASC) ,
CONSTRAINT `user_id`
FOREIGN KEY (`user_id` )
REFERENCES `recruitment`.`user_authentication` (`user_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION,
CONSTRAINT `degree_id`
FOREIGN KEY (`degree_id` )
REFERENCES `recruitment`.`degree` (`degree_id` )
ON DELETE NO ACTION
ON UPDATE NO ACTION
);
Here's the stored procedure:
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createSeekerProfile`(in userName varchar(45),
in fn varchar(45),in mn varchar(45), in ln varchar(45),
in gender varchar(6),in nationality varchar(45),
in ad varchar(45),in city varchar(45),in phone varchar(15),in mob varchar(15),
in maritalStatus varchar(45), in degId int, in educ varchar(100),
in exper varchar(250), in other varchar(250),
in dob date,in jtarg varchar(250))
begin
declare returned_ID int;
set @dyn_que = CONCAT('select user_id into @returned_ID
from user_authentication where user_name = ? ');
prepare s1 from @dyn_que ;
set @usn = userName;
execute s1 using @usn ;
set @dyn_update =CONCAT('update job_seeker set fname =',fn,', lname = ',ln,' ,mname = ',mn,' ,
nationality =',nationality,',address =',ad,',city =',city,',phone=',phone,',
mobile =',mob,', gender = ',gender,',other =',other,',
degree_id =',degId,', job_target=',jtarg,', dob =',dob,', education =',educ,',
experience=',exper,', marital_status=',maritalStatus,' where user_id =@returned_ID');
prepare s2 from @dyn_update;
execute s2;
end
Whenever I call the procedure through:
call createSeekerProfile('realsilhouette','robert','marie','david','male'
,'earthal','an address here','capital of earth','012178152',
'1111111111','single',2,'engineering','looking forward','determined',
'2008-7-04','Oracle CEO')
I get an awful error which is:
Error Code: 1064
You have an error in your SQL syntax; check the manual that corresponds
to your MySQL server version for the right syntax to use near 'address here,
city =capital of earth,phone=012178152,
mobile =1111111111, gender' at line 2
However when I try execute the update statement manually which is inside stored procedure, it works out so well.
new Post : thanks god,finally I fixed the problem up, The problem came down to the order, all I did was just make the parameters in order, though update statement does not care about the order, as far as I know, I'm not sure, But what i am sure of is the stored procedure got created beautifully, here's the new code :
DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `createSeekerProfile`(
in un varchar(45),
in fn varchar(45),
in ln varchar(45),
in mn varchar(45),
in g varchar(10),
in dateOfBirth date,
in ms varchar(45),
in ad varchar(45),
in city varchar(45),
in nat varchar(45),
in ph varchar(45),
in mob varchar(45),
in degid int,
in educ varchar(100),
in exp varchar(250),
in other varchar(250),
in jtarg varchar(250))
begin
declare returned_ID int(11);
set @dyn_que = CONCAT('select user_id into @returned_ID from user_authentication
where user_name = ? ');
prepare s1 from @dyn_que ;
set @usn = un;
execute s1 using @usn ;
set @dyn_update =CONCAT('update job_seeker set fname
="',fn,'",lname="',ln,'",mname="',mn,'",lname ="',ln,'",
gender ="',g,'",dob="',dateOfBirth,'",marital_status="',ms,'",
address="',ad,'",city="',city,'",
nationality="',nat,'",phone="',ph,'",mobile="',mob,'",degree_id="',
degid,'",education="',educ,'",
experience="',exp,'",other="',other,'",job_target="',jtarg,'"
where user_id = @returned_ID');
prepare stm from @dyn_update;
execute stm;
end $$
many thanks
Your forgot about quoting string values.
for example:
concat('update yourTable
set address ="', @address, '"
where id = 1');
or use function quote
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With