CREATE PROCEDURE StudentMessage_Insert(
_regno VARCHAR(8000),
_regnoCount INT,
_isEmail int,
_isSMS int,
_isParent int,
_isStudent int,
_message text,
_msgfrom varchar(100),
_filePath1 varchar(200),
_filePath2 VARCHAR(200),
_filePath3 VARCHAR(200),
_messageTitle VARCHAR(200)
)
BEGIN
DECLARE xx INT;
DECLARE regnoTemp nvarchar(100);
DECLARE _tmpMsg TEXT;
DECLARE _PrtUC varchar(100);
if (_isStudent = 1) then
SET xx = 0;
WHILE xx < _regnoCount DO
SET xx = xx + 1;
-- SELECT SPLIT_STRING(_regno, ',', xx);
SELECT SPLIT_STRING(_regno, ',', xx) into regnoTemp;
set _tmpMsg = _message;
-- Error is in All Replace Keywords - Setting up the Message Tokens
SET _tmpMsg = replace(_tmpMsg, '{regno}', regnoTemp );
SET _tmpMsg = replace (_tmpMsg, '{StudentName}', fn_getStudentName(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{ParentName}', fn_getFatherNameByStudentUserCode(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{StaffName}', fn_getNameByUserCode(_msgfrom) );
SET _tmpMsg = replace (_tmpMsg, '{ClassName}', fn_getClassNameByUserCode(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{SectionName}', fn_getSectionNameByUserCode(regnoTemp) );
CALL Student_InsertTimeLine(regnoTemp, _msgfrom, regnoTemp, 'Message',_messageTitle, _tmpMsg, NOW());
insert into message(msgFrom,msgTo,regno,messageBody,filename1,filename2,filename3,isSMS,isEmail,messageDate)
values ( _msgfrom,regnoTemp,regnoTemp,_tmpMsg,_filePath1,_filePath2,_filePath3,_isSMS,_isEmail, NOW());
END WHILE;
end if;
IF (_isParent = 1) THEN
SET xx = 0;
WHILE xx < _regnoCount DO
SET xx = xx + 1;
-- SELECT SPLIT_STRING(_regno, ',', xx);
SELECT SPLIT_STRING(_regno, ',', xx) INTO regnoTemp;
set _tmpMsg = _message;
-- Error is in All Replace Keywords - Setting up the Message Tokens
SET _tmpMsg = replace(_tmpMsg, '{regno}', regnoTemp );
SET _tmpMsg = replace (_tmpMsg, '{StudentName}', fn_getStudentName(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{ParentName}', fn_getFatherNameByStudentUserCode(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{StaffName}', fn_getNameByUserCode(_msgfrom) );
SET _tmpMsg = replace (_tmpMsg, '{ClassName}', fn_getClassNameByUserCode(regnoTemp) );
SET _tmpMsg = replace (_tmpMsg, '{SectionName}', fn_getSectionNameByUserCode(regnoTemp) );
SET _PrtUC = fn_getParentUCByStudent(regnoTemp);
CALL Student_InsertTimeLine(regnoTemp, _msgfrom, _PrtUC, 'Message', _messageTitle,_tmpMsg, NOW());
INSERT INTO message(msgFrom,msgTo,regno,messageBody,filename1,filename2,filename3,isSMS,isEmail,messageDate)
VALUES ( _msgfrom,_PrtUC,regnoTemp,_tmpMsg,_filePath1,_filePath2,_filePath3,_isSMS,_isEmail, NOW());
END WHILE;
END IF;
END;
CALL StudentMessage_Insert ('UA12','1','1','0','1','1','Today will be holiday due to dynamic.','AD11','','','','Important Notice')
I am getting this ERROR:
SQL execution error #1270. Response from the database:
Illegal mix of collations (latin1_swedish_ci,IMPLICIT),
(utf8_general_ci,COERCIBLE), (utf8_general_ci,COERCIBLE) for operation
'replace'