Error when creating MySQL stored procedure

Question

I have a problem when trying to create a stored procedure using Mysql server.

This is my mysql query

DELIMITER |
CREATE PROCEDURE `EditProduk`(IN `XML` LONGTEXT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN
BEGIN
    declare v_row_index int unsigned default 0;
    declare v_row_count int unsigned;
    declare v_xpath_row varchar(255);
    declare RESULT int unsigned default 0;
    declare USER int unsigned default 0;
    declare PRDKID int unsigned default 0;

    IF(XML <> '' AND  XML <> '0' AND XML IS NOT NULL)
    THEN

    -- calculate the number of row elements.
    set v_row_count := extractValue(XML,'count(/xml/ProdukData)');
    -- loop through all the row elements
    while v_row_index < v_row_count do        
        set v_row_index := v_row_index + 1;
        set v_xpath_row := concat(
            '/xml/ProdukData['
        ,   v_row_index
        ,   ']'
        );

    SET USER = extractValue(XML,concat(v_xpath_row,'/USER/text()'));
    SET PRDKID = extractValue(XML,concat(v_xpath_row,'/id/text()'));

    UPDATE Produk SET 
    ProdukName = extractValue(XML,concat(v_xpath_row,'/name/text()')), 
    ProdukCode = extractValue(XML,concat(v_xpath_row,'/code/text()')), 
    ProdukMerek = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/merek/text()')) = "",0, ProdukMerekId)  FROM ProdukMerek WHERE ProdukMerekName = extractValue(XML,concat(v_xpath_row,'/merek/text()'))),
    ProdukCategory = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/category/text()')) = "",0, ProdukCategoryId) FROM ProdukCategory WHERE ProdukCategoryName = extractValue(XML,concat(v_xpath_row,'/category/text()'))), 
    ProdukQuality = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/quality/text()')) = "",0, ProdukQualityId) FROM ProdukQuality WHERE ProdukQualityName = extractValue(XML,concat(v_xpath_row,'/quality/text()'))), 
    ProdukGroup = (SELECT IF(extractValue(XML,concat(v_xpath_row,'/group/text()')) = "",0, ProdukGroupId) FROM ProdukGroup WHERE ProdukGroupName = extractValue(XML,concat(v_xpath_row,'/group/text()'))), 
    ProdukBuyPrice = extractValue(XML,concat(v_xpath_row,'/buyprice/text()')),
        ProdukSellPrice = extractValue(XML,concat(v_xpath_row,'/sellprice/text()')), 
    ProdukDiscount = extractValue(XML,concat(v_xpath_row,'/discount/text()')), 
    ProdukStatus = extractValue(XML,concat(v_xpath_row,'/status/text()')),
    ProdukImagePath = extractValue(XML,concat(v_xpath_row,'/defaultimage/text()')),
        ModifiedDate = NOW(), 
    ModifiedBy = USER
    WHERE ProdukId = PRDKID;


    end while;

    -- calculate the number of row elements.
        set v_row_count := extractValue(XML,'count(/xml/ProdukDetail)');
    SET v_row_index = 0;
    SET PRDKID = LAST_INSERT_ID();
    -- loop through all the row elements
        while v_row_index < v_row_count do        
            set v_row_index := v_row_index + 1;
            set v_xpath_row := concat(
                '/xml/ProdukDetail['
            ,   v_row_index
            ,   ']'
        );

    if extractValue(XML,concat(v_xpath_row,'/id/text()')) = '0'
            then
            insert into ProdukDetail (ProdukId, ProdukDetailWarna, 
               ProdukDetailUkuran, ProdukDetailImagePage, 
               ModifiedDate, ModifiedBy, 
               CreatedDate, CreatedBy) values (    
               PRDKID
            ,   extractValue(XML,concat(v_xpath_row,'/color/text()'))
            ,   extractValue(XML,concat(v_xpath_row,'/size/text()'))
            ,   extractValue(XML,concat(v_xpath_row,'/photo/text()'))
            ,   NOW(), USER, NOW(), USER
            );

        else
            UPDATE ProdukDetail SET 
            ProdukDetailWarna =extractValue(XML,concat(v_xpath_row,'/color/text()')), 
            ProdukDetailUkuran =extractValue(XML,concat(v_xpath_row,'/size/text()')), 
            ProdukDetailImagePage=extractValue(XML,concat(v_xpath_row,'/photo/text()')), 
            ModifiedDate=NOW(), 
            ModifiedBy =USER
            WHERE ProdukDetailId = extractValue(XML,concat(v_xpath_row,'/id/text()'));
        end if;
    end while;
    SET RESULT = 1;
    else
        SET RESULT = 0;
    END IF;
    SELECT RESULT;
END|
DELIMITER ;

This is the error I got :

Galat

Pencarian SQL:

DELIMITER | CREATE PROCEDURE EditProduk(IN XML LONGTEXT) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN BEGIN declare v_row_index int unsigned default 0; declare v_row_count int unsigned; declare v_xpath_row varchar(255); declare RESULT int unsigned default 0; declare USER int unsigned default 0; declare PRDKID int unsigned default 0; IF(XML <> '' AND XML <> '0' AND XML IS NOT NULL) THEN -- calculate the number of row elements. set v_row_count := extractValue(XML,'count(/xml/ProdukData)'); -- loop through all the row elements while v_row_index < v_row_count do set v_row_index := v_row_index + 1; set v_xpath_row := concat( '/xml/ProdukData[' , v_row_index , ']' ); SET USER = extractValue(XML,concat(v_xpath_row,'/USER/text()')); SET PRDKID = extractValue(XML,concat(v_xpath_row,'/id/text()')); UPDATE Produk SET ProdukName = extractValue(XML[...]

MySQL menyatakan: Dokumentasi 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 '' at line 87

Can someone help me?

Because I try to explore to found the solution but not found..

Thanks

zedfoxus · Accepted Answer · 2015-03-08 03:19:33Z

1

You have 2 BEGIN

CREATE PROCEDURE `EditProduk`(IN `XML` LONGTEXT) NOT DETERMINISTIC 
NO SQL SQL SECURITY DEFINER BEGIN
BEGIN
...
...

Remove BEGIN after DEFINER and you should be good to go.

answered Mar 8, 2015 at 3:19

zedfoxus

37.4k5 gold badges68 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Error when creating MySQL stored procedure

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related