MYSQL Stored Procedure using cursor loop not working in IN Select function

Question

I got a SP function where it will pass the ID and the Comma Separated Values to be inserted into new rows
now i have a cursor that select multiple post where ID = (comma separated value) which is a dynamic variable

this is my first stored procedure to get the comma separated ID values

CREATE DEFINER=`root`@`%` PROCEDURE `getGalleryItems`()
BEGIN
DECLARE postID int default 0;
DECLARE uData varchar(1024);
   DECLARE done INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT post_id, unserializeData(meta_value)
      FROM wp_postmeta AS meta
      LEFT JOIN wp_posts AS post ON meta.post_id = post.ID
      WHERE meta.meta_key LIKE 'image_gallery' LIMIT 2);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;



   OPEN cur;
   
   REPEAT
       FETCH cur INTO postID, uData;
    if ! done then
    call insertGalleryImages(postID,uData);

       end if;

   UNTIL done  = 1 END REPEAT;
   CLOSE cur;
END

then the 2nd SP which the value pass in is something like this

CREATE DEFINER=`root`@`%` PROCEDURE `insertGalleryImages`(in parentID int, in objectID varchar(1024))
BEGIN
DECLARE postID bigint(20) default 0;
DECLARE postTitle text default null;
DECLARE postExcerpt text default null;
DECLARE postContent longtext default null;
   DECLARE done INT DEFAULT 0;
   DECLARE cntr INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT ID, post_title, post_excerpt, post_content 
FROM wp_posts WHERE ID IN (objectID));
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
   
   OPEN cur; 
   
   REPEAT
       FETCH cur INTO  postID, postTitle, postExcerpt, postContent;
       if ! done then
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, concat("gallery_items_", cntr, "_title"), postTitle),
(parentID, concat("_gallery_items_", cntr, "_title"), 'field_5cac53504a85f'),
(parentID, concat("gallery_items_", cntr, "_item_type"), 'image'),
(parentID, concat("_gallery_items_", cntr, "_item_type"), 'field_5d1d6e1497252'),
(parentID, concat("gallery_items_", cntr, "_image"), postID),
(parentID, concat("_gallery_items_", cntr, "_image"), 'field_5cac53434a85e'),
(parentID, concat("gallery_items_", cntr, "_credit"), postExcerpt),
(parentID, concat("_gallery_items_", cntr, "_credit"), 'field_5cac53884a861'),
(parentID, concat("gallery_items_", cntr, "_description"), postContent),
(parentID, concat("_gallery_items_", cntr, "_description"), 'field_5cac53594a860');

SET cntr = cntr + 1;
       end if;
   UNTIL done END REPEAT;
   CLOSE cur;
   
      INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, 'gallery_items', cntr),
(parentID, '_gallery_items', 'field_5cac4ee94a85d');
   
END

now i tried to debug this where i put on the 2nd procedure object ID to be static. the loop goes well but when i'm using the variable to be passed on to. it doesn't loop the way it should be hope someone helps me out on this thank you!

doing this in SP is just painful. Handle data breaking apart in application languages. — danblack, Jul 03 '20 at 05:40
yeah kind of but im the point already where the script actually works but it's just that it doesn't loop the way it should be dynamically. — Derek Anas, Jul 03 '20 at 06:28
MySQL doesn't allow you to do `WHERE ID IN (objectID)` where `objectID` is a list. It [only works](https://stackoverflow.com/q/13116042) (as you have figured out) when you put a constant list in there. You can instead use one of the methods mentioned there or in [SQL split values to multiple rows](https://stackoverflow.com/q/17942508). — Solarflare, Jul 03 '20 at 07:50

score 0 · Answer 1 · answered Jul 03 '20 at 07:47

By the way I fix this by manipulating the passed variable in to view table as prepared statement and select from there

Apparently cursor doesn't work dynamically as it should be

here is the code that i applied

CREATE DEFINER=`root`@`%` PROCEDURE `insertGalleryImages`(in parentID int, in objectID varchar(1024))
BEGIN
DECLARE postID bigint(20) default 0;
DECLARE postTitle text default null;
DECLARE postExcerpt text default null;
DECLARE postContent longtext default null;
   DECLARE done INT DEFAULT 0;
   DECLARE cntr INT DEFAULT 0;
   
   DECLARE cur CURSOR FOR
(SELECT ID, post_title, post_excerpt, post_content 
FROM vw_myproc);
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

SET @select = concat('CREATE VIEW vw_myproc as SELECT * FROM wp_posts WHERE ID IN(', objectID, ')');
    PREPARE stm FROM @select;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;

    SET @select = concat('SELECT * FROM wp_posts WHERE ID IN (', objectID, ')');
    PREPARE stm FROM @select;
    EXECUTE stm;
    DEALLOCATE PREPARE stm;
   
   OPEN cur; 
   
   REPEAT
       FETCH cur INTO  postID, postTitle, postExcerpt, postContent;
       if ! done then
INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, concat("gallery_items_", cntr, "_title"), postTitle),
(parentID, concat("_gallery_items_", cntr, "_title"), 'field_5cac53504a85f'),
(parentID, concat("gallery_items_", cntr, "_item_type"), 'image'),
(parentID, concat("_gallery_items_", cntr, "_item_type"), 'field_5d1d6e1497252'),
(parentID, concat("gallery_items_", cntr, "_image"), postID),
(parentID, concat("_gallery_items_", cntr, "_image"), 'field_5cac53434a85e'),
(parentID, concat("gallery_items_", cntr, "_credit"), postExcerpt),
(parentID, concat("_gallery_items_", cntr, "_credit"), 'field_5cac53884a861'),
(parentID, concat("gallery_items_", cntr, "_description"), postContent),
(parentID, concat("_gallery_items_", cntr, "_description"), 'field_5cac53594a860');

SET cntr = cntr + 1;
       end if;
   UNTIL done END REPEAT;
   CLOSE cur;
   
      INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`) VALUES
(parentID, 'gallery_items', cntr),
(parentID, '_gallery_items', 'field_5cac4ee94a85d');
   
END

reference from this question : MySQL stored procedure cursor for prepared statements

MYSQL Stored Procedure using cursor loop not working in IN Select function

1 Answers1