I'm playing with MySQL stored procedure from this answer to insert many rows with random data.
When I'm executing query that inserts rows containing value only from my own variable:
DROP PROCEDURE IF EXISTS InsertRand;
DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT)
BEGIN
DECLARE i INT;
SET i = 1;
START TRANSACTION;
WHILE i <= NumRows DO
SET @devname = CONV(FLOOR(RAND() * 99999999999999), 20, 36);
INSERT INTO `devices` VALUES (NULL, FLOOR(1 + (RAND() * 3)), NULL, CONCAT('Device ', @devname), CONCAT('Description for device ', @devname, '.'), ROUND(RAND()));
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
CALL InsertRand(77);
then everything is fine. But, when I'm trying to extend inserted data with current iterator value:
DROP PROCEDURE IF EXISTS InsertRand;
DELIMITER $$
CREATE PROCEDURE InsertRand(IN NumRows INT)
BEGIN
DECLARE i INT;
SET i = 1;
START TRANSACTION;
WHILE i <= NumRows DO
SET @devname = CONV(FLOOR(RAND() * 99999999999999), 20, 36);
INSERT INTO `devices` VALUES (NULL, FLOOR(1 + (RAND() * 3)), NULL, CONCAT('Device no ', @i), CONCAT('Description for device no ', @i, ' and device ID = ', @devname, '.'), ROUND(RAND()));
SET i = i + 1;
END WHILE;
COMMIT;
END$$
DELIMITER ;
CALL InsertRand(77);
then I'm getting #1048 - Column 'name' cannot be null.
Column name is third in my INSERT query. When I'm pushing CONCAT('Device ', @devname) into it, all is fine. Changing this to CONCAT('Device no ', @i) fails ugly.
This must be something awfully obvious, but I'm struggling with this for past two hours (MySQL newbie I am) and I don't see the light in the tunnel.
