MySQL Syntax Error 1064 On Set

Question

Let's see if I can edit this and put the whole procedure in.

I am trying to convert an Oracle database to MySQL. I have all the tables, keys, indexes, and views converted. I now need to convert a stored procedure to MySQL.

I have most of it done, and there is only one hang up on my code:

set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;

This gives me an error of 1064 Syntax Error: Missing semicolon

I have tested the rest of my procedure, and it works fine. It creates it, runs it, and retrieves data from it, but only if I remove those two lines.

Any ideas on what I can do?

Whole stored procedure:

DELIMITER //
USE `TEST`//
DROP PROCEDURE IF EXISTS `proc_IN`//
CREATE DEFINER=`root`@`localhost` PROCEDURE `proc_IN`
(IN DNIS VARCHAR(20),
IN MSISDN VARCHAR(20),
IN AVPAIR1 VARCHAR(20),
IN AVPAIR2 VARCHAR(20),
IN GROUPID VARCHAR(20),
OUT DNS1 VARCHAR(15),
OUT DNS2 VARCHAR(15),
OUT AUTHSTAT VARCHAR(100))
BEGIN
declare dns1_tmp varchar(15);
declare dns2_tmp varchar(15);
set dns1_tmp = X.X.X.X;
SET dns2_tmp = X.X.X.X;
DECLARE avpair1_tmp varchar(15);
DECLARE avpair2_tmp varchar(15);
DECLARE grpid_tmp varchar(15);
DECLARE C_USER CURSOR FOR SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP, ALLMEMBER WHERE ALLMEMBER.GROUPID=GRP.GROUPID
 UNION
 SELECT AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID FROM GRP; 
OPEN C_USER;
FETCH C_USER INTO AVPAIR1, AVPAIR2, DNS1, DNS2, GROUPID;
LOOP
 FETCH C_USER INTO avpair1_tmp, avpair2_tmp, dns1_tmp, dns2_tmp, grpid_tmp;
 INSERT INTO duplog VALUES(DNIS, MSISDN, avpair1_tmp, avpair2_tmp, dns1_tmp,dns2_tmp, grpid_tmp, SYSDATE);
END LOOP;
IF C_USER%ROWCOUNT > 1 THEN
 INSERT INTO duplog VALUES(DNIS, MSISDN, AVPAIR1, AVPAIR2, DNS1,DNS2, GROUPID, SYSDATE);
 SET AUTHSTAT := 'ok';
 elseif C_USER%ROWCOUNT = 1 THEN
 SET AUTHSTAT := 'ok';
ELSE
 SET AUTHSTAT := NULL;
END IF;
CLOSE C_USER;
COMMIT;
END //
DELIMITER ;

UPDATE:

I have tried single quotes, double quotes, and back ticks. When I do the code in this fashion

set dns1_tmp = 'X.X.X.X';
SET dns2_tmp = 'X.X.X.X';

I get a different error of Syntax Error: Missing end

• IGGt: have you tried set dns1_tmp = 'X.X.X.X'; ?
• skinfrakki: Yes I've tried single quotes, doubles quotes, and back ticks
• IGGt: On my machine I found two errors (may be different for you). I moved the SET dns1_tmp = '1.2.3.4'; and tmp2 below all the DECLARE statements, and put a BEGIN above DECLARE C_USER and an END; before END//. It then created without error. I don't know if that helps?
• skinfrakki: Should put that as the answer if possible
• Rick James: Often, it is practical to use @variables in Stored Routines. CURSORs are inefficient; it looks like that could be turned into two (because of UNION) INSERT INTO ... SELECT ... statements. I doubt if C_USER%ROWCOUNT will do what you expect.

Answer 1

The IP Addresses in your DNSx_tmp variables need to be enclosed in single quotes in order to make them character strings (as they have been Declared).

The SET DNSx_tmp; variables need to be moved below all the DECLARE statements.

You also need to add a BEGIN clause above DECLARE c_user and the matching END clause before your final ENDclause.