Send a Procedure in a query from Node to MySQL-CodePudding

I'm building a backend for my food application, and I need to create columns in food table and INSERT rows in nutrients table. I'm constructing a query, there are ~60 nutrients in every food, and there are hundreds of different nutrient types.

I used one of answers from MySQL: ALTER TABLE if column not exists as my template


            for (let i = 0; i < food.nutrients.length; i  ) {
              createColumnsString  = `
DROP PROCEDURE IF EXISTS \`create_column\`; 
DELIMITER //
CREATE PROCEDURE \`create_column\`()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE \`food\` ADD COLUMN \`${food.nutrients[i].nutrientNumber}\` VARCHAR(45); 
INSERT INTO \`nutrients\` (nutrientid, nutrientname, unit) VALUES ("${food.nutrients[i].nutrientNumber}", "${food.nutrients[i].nutrientName}", "${food.nutrients[i].unitName}"); 
END // 
DELIMITER ; 
CALL \`create_column\`(); 
DROP PROCEDURE \`create_column\`; `;
            }
            console.log(createColumnsString);
            db.query(createColumnsString);

the console.log(createColumnsString) for each nutrient prints this in Node console:

DROP PROCEDURE IF EXISTS `create_column`;
DELIMITER //
CREATE PROCEDURE `create_column`()
BEGIN
DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;
ALTER TABLE `food` ADD COLUMN `269.3` VARCHAR(45);
INSERT INTO `nutrients` (nutrientid, nutrientname, unit) VALUES ("269.3", "Sugars, Total NLEA", "G");
END //
DELIMITER ;
CALL `create_column`();
DROP PROCEDURE `create_column`;

And it works when i paste it to MySQL Workbench. I can put all ~60 queries one after another and it does what it's supposed to do.

On the other hand, db.query(createColumnsString) gives me this:

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlMessage: "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 'DELIMITER //\n"  
    'CREATE PROCEDURE `create_column`()\n'  
    'BEGIN\n'  
    "DECLARE CONTINUE HANDLER F' at line 1",
  sqlState: '42000',
  index: 1,
  sql: '\n'  
    'DROP PROCEDURE IF EXISTS `create_column`; \n'  
    'DELIMITER //\n'  
    'CREATE PROCEDURE `create_column`()\n'  
    'BEGIN\n'  
    'DECLARE CONTINUE HANDLER FOR SQLEXCEPTION BEGIN END;\n'  
    'ALTER TABLE `food` ADD COLUMN `303` VARCHAR(45); \n'  
    'INSERT INTO `nutrients` (nutrientid, nutrientname, unit) VALUES ("303", "Iron, Fe", "MG"); \n'  
    'END // \n'  
    'DELIMITER ; \n'  
    'CALL `create_column`(); \n'  
    'DROP PROCEDURE `create_column`; \n'  
    'DROP PROCEDURE IF EXISTS `create_column`; \n'

I'm using mysql library for connection. Does it even permit the use of DELIMITER? What am I doing wrong?

CodePudding user response：

Create a Food table to contain the food info.

id int
name varchar(30)
... etc etc

Create a Nutriets table to hold nutrient info.

id int
nutrientname    varchar(30)
unit            _not sure of type_
. . . etc etc

Then as many foods will have the same nutrients in them you need a xref table, or link table to connect them

That table is simply something like this

food_nutrients table

id          int
food_id     int
nutrient_id int

Nowyou can link any food to any nutrient, all you need is either the id of the food or the id of the nutrient to be able to list all a foods nutrients, or all the food that contain any nutrient.