CODEWITHSUNDEEP
mysqlstored-procedures
vipin p
vipin p

Reputation: 111

inserting values from arrays in mysql

I have multiple user_roles. Each user_role has multiple privileges and each privileges has multiple values. I need to create a procedure with user_role_name,description,priviliges_fk(array),values(arrayofstring) as inputs. This is the procedure I have written.

DELIMITER $$
DROP PROCEDURE IF EXISTS `save_role`$$
CREATE DEFINER=`event_admin`@`%` PROCEDURE `save_role`(IN p_role_name INT, 
                           IN p_description INT, 
                           IN p_privilege_fk INT(), 
                           IN p_values VARCHAR(1000)
                          )
BEGIN
      DECLARE i int default 0;
      DECLARE V_ROLE_FK int;
      DECLARE counter INT DEFAULT 0;

      INSERT INTO ROLE (ROLE_NAME,DESCRIPTION) VALUES(p_role_name,p_description);
      SELECT ROLE_PK INTO V_ROLE_FK FROM ROLE WHERE ROLE_NAME=p_role_name AND DESCRIPTION=p_description;

simple_loop:LOOP 
SET counter = counter + 1;
     INSERT INTO ROLE_PRIVILEGE_BRIDGE (ROLE_FK,PRIVILEGE_FK,VALUE) VALUES(V_ROLE_FK,p_privilege_fk(i),p_values);
END LOOP simple_loop;
END;

Upvotes: 1

Views: 1509

Answers (2)

Devart
Devart

Reputation: 121902

I'd suggest you to use AUTO_INCREMENT option for primary keys, it will help to work with them. Then use auto-incremented primary key values to insert new rows into a child table - one by one, not using array as a string parameter.

For example (data is simplified):

CREATE TABLE ROLE(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  ROLE_NAME INT,
  DESCRIPTION INT,
  PRIMARY KEY (ID)
)
ENGINE = INNODB;

CREATE TABLE ROLE_PRIVILEGE_BRIDGE(
  ID INT(11) NOT NULL AUTO_INCREMENT,
  PRIVILEGE_FK INT(11) DEFAULT NULL,
  VALUE INT(11) DEFAULT NULL,
  PRIMARY KEY (ID),
  CONSTRAINT FK FOREIGN KEY (PRIVILEGE_FK) REFERENCES ROLE (ID)
)
ENGINE = INNODB;


INSERT INTO ROLE(ROLE_NAME, DESCRIPTION) VALUES(1, 1);
SET @new_id = LAST_INSERT_ID();

INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 1);
INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 2);
INSERT INTO ROLE_PRIVILEGE_BRIDGE(PRIVILEGE_FK, VALUE) VALUES (@new_id, 3);

Upvotes: 1

Andreas Wederbrand
Andreas Wederbrand

Reputation: 39951

You can't. There are two workarounds that would work

  1. Call the procedure one time per element in the array
  2. Concatenate the array elements into one string separated by something (ie |, ;, :) and then split that string internally in the procedure.

I would go with the first alternative. It's cleaner, easier to understand and easier to test.

Upvotes: 2

Related Questions