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

Yes I've tried single quotes, doubles quotes, and back ticks — skinfrakki
– skinfrakki, Commented Feb 20, 2015 at 16:15
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? — IGGt
– IGGt, Commented Feb 20, 2015 at 16:30
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. — Rick James
– Rick James, Commented Feb 20, 2015 at 18:44

IGGt · Accepted Answer · 2015-02-20 22:08:31Z

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.

answered Feb 20, 2015 at 22:08

IGGt

2,2966 gold badges35 silver badges51 bronze badges

Add a comment |

Stack Exchange Network

MySQL Syntax Error 1064 On Set

1 Answer 1

Your Answer

Hot Network Questions

MySQL Syntax Error 1064 On Set

1 Answer 1

Your Answer

Sign up or log in

Post as a guest

Related

Hot Network Questions