Xato kodi: mysql-da 1064

Men operatsiyani bajarishim kerak bo'lgan operatsiyani yaratdim, lekin har safar uni chaqirsam, MySQL xatolikni keltirib chiqadi va men buni nima demoqchi ekanligini bilmayman. Men buni behuda tushunishga harakat qildim, bu erda jurnali tuzilishi, u erda saqlanadigan protsedura bu erda bajariladigan operatsiya:

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

Bu erda saqlanadigan amaliyot:

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 [email protected]_ID');
prepare s2 from @dyn_update;
execute s2;
end

Jarayoni quyidagi hollarda chaqirganimda:

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')

Men juda katta xatoga yo'l qo'yaman:

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

Biroq, yangilash amaliyotini o'zida saqlab qolgan amaliyotni qo'llashga harakat qilsam, u juda yaxshi ishlaydi.

yangi Xabar:  shukur ilohi, nihoyat muammoni bartaraf qildim, muammoni tartibga solib qo'ydi, men faqat parametrlarni tartibga solib qo'ydim, yangilanish bayonoti buyurtma haqida qayg'urmaydi, men bilganimdek, ishonchim komil emas , Lekin aminmanki, o'zida saqlanadigan protsedura yaxshi yaratilgan, yangi kod:

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 $$

ko'p rahmat

4

3 javoblar

Qatorli qiymatlarni qayd etishni unutgansiz.

misol uchun:

concat('update yourTable
set address ="',  @address, '" 
where id = 1');

or use function quote

4
qo'shib qo'ydi
Yaxshi tejash, men shu masala bo'yicha bir muncha vaqt qarayapman. Rahmat.
qo'shib qo'ydi muallif usumoio, manba
huh ... bu erda dinamik so'rovlarni disk raskadrovka qilishning juda yaxshi yo'li. 1) sharhni bajarish/tayyorlash 2) "Select @your_query" ni tanlang 3) o'zida saqlab turishni bajaring va natijada so'rovni oling 4) So'rovni bajaring va so'rovingizni ko'rib chiqing. Bunday holatda siz ba'zi xatoliklarni tanladingiz, chunki ba'zi parametrlari bo'sh edi. Iltimos, mening qadamlarimga amal qiling.
qo'shib qo'ydi muallif ravnur, manba
Yopiq kursdosh ... Har qanday "field = NULL" dan qochish kerak. kabi concat funktsiya shartlari, jumladan, NULL qiymatlarini atlaymalısınız, agar @Variable null bo'lmasa, @stmt = concat (@stmt, AND va yourfield = "', @variable,'" ") . Har bir o'zgaruvchiga dinamik so'rovingizni alohida-alohida qurishga harakat qiling va ularni qo'shing.
qo'shib qo'ydi muallif ravnur, manba
lekin men bir necha saqlanadigan protsedurani shu tarzda yaratdim, chunki siz menga aytganidek, uni o'zgartirdim, yangi xatolik yuzaga keldi: Xato kodi: 1064 SQL sintaksisida xatolik bor; 1-satrda "NULL" yaqinida ishlatish uchun to'g'ri sintaksis uchun MySQL-server versiyasiga mos keladigan qo'llanmani tekshiring.
qo'shib qo'ydi muallif Rehme, manba
Men sizning barcha qadamlaringizni kuzatardim, xato hali ham mavjud, baribir rahmat.
qo'shib qo'ydi muallif Rehme, manba
Mening savolimga vaqt ajratganingiz uchun tashakkur.
qo'shib qo'ydi muallif Rehme, manba

Men uchun "(GRAVE ACCENT) belgisi qo'shildi. Bu bitta taklif emas (')

2
qo'shib qo'ydi
CREATE TABLE Order
( 
    Order_Id             integer  NOT NULL ,
    Order_Time           datetime  NULL ,
    Order_Status         char(50)  NULL ,
    Customer_Id          integer  NOT NULL 
)
2
qo'shib qo'ydi