Mysql stored procedure insert query inside a loop only inserting last value

Question

I wrote a stored procedure to insert multiple values to same table using an array and a loop. But the table only have the last value of the array. and also it is throwing an error saying

Column can't be null.

This is my stored procedure:

CREATE DEFINER=`root`@`localhost` PROCEDURE `insert_images`(
packageName VARCHAR(300), 
packageUniqueCode VARCHAR(100), 
packageAddedDate DATE, 
packagePrice DOUBLE, 
packageDescription VARCHAR(3000), 
packageOwnerID INT, 
images VARCHAR(500)
)
BEGIN
    declare i INT default 0;
START TRANSACTION;
   INSERT INTO packages(packageName, packageUniqueCode, packageAddedDate, packagePrice, packageDescription, packageOwnerID) 
     VALUES(packageName, packageUniqueCode, packageAddedDate, packagePrice, packageDescription, packageOwnerID);
  SET i = 1;
   WHILE i <= JSON_LENGTH(images) DO
    INSERT INTO packageImages(imageName, location, packageOwnerID, packageID) 
    VALUES(JSON_EXTRACT(images,CONCAT( '$[', `i`, '].imageName')), JSON_EXTRACT(images,CONCAT( '$[', `i`, '].location')), packageOwnerID,LAST_INSERT_ID());
    SET i = i + 1;
   END WHILE;
COMMIT;
END

And I'm calling it using this code:

CALL insert_images("PJKG", "codea", "2022-11-11", 12, "description", 22, '[{"imageName": "pasinduImage", "location": "uploads/image"},{"imageName": "pasinduImage2", "location": "uploads/image"}]');

Please help me to figure things out. Thanks

slaakso · Accepted Answer · 2020-03-04 10:48:35Z

1

The JSON index starts from 0. So set:

SET i = 0;
WHILE (i < JSON_LENGTH(images)) DO

answered Mar 4, 2020 at 10:48

slaakso

9,1452 gold badges19 silver badges29 bronze badges

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

1 Comment

Pasindu Dineth Peiris Over a year ago

Thanks mate. Worked really well.

Collectives™ on Stack Overflow

Mysql stored procedure insert query inside a loop only inserting last value

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related