Send a Procedure in a query from Node to MySQL

Question

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?

Thats a **terrible** terrible idea. Create a seperate table for this information and have a id column to link it to the `food` table. Search for [Relational Database Design for Beginners](https://duckduckgo.com/?t=ffab&q=relational+database+design+for+beginners&ia=web) — RiggsFolly, Jul 13 '22 at 18:05
isn't it exactly what I'm trying to do? I have separate tables for nutrients and food, one link between them being the id. In id column (food table) there is just 1 value — jaclaw, Jul 13 '22 at 18:09
You are in a loop of `food.nutrients.length` and for each iteration you are attempting to add a column to the `food` table? Or am I getting that wrong — RiggsFolly, Jul 13 '22 at 18:14
PS I dont see why you are trying to create a proceedure to do this in either? — RiggsFolly, Jul 13 '22 at 18:14
So the `food` table will end up with many many column, one for each nutrient. Thats what is wrong — RiggsFolly, Jul 13 '22 at 18:16
I would like to answer your question, but procedure is the only 1 that works at the moment — jaclaw, Jul 13 '22 at 18:16

score 0 · Accepted Answer · answered Jul 13 '22 at 18:24

0

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.

answered Jul 13 '22 at 18:24

RiggsFolly

93,638
21
103
149

So you are telling me it's better to have a table with (foods * nutrients) rows and 3 columns than a table with foods rows and nutrients columns? I'm new to SQL so it seems kinda weird to me – jaclaw Jul 13 '22 at 18:39
Yes, randomly adding columns to accomodate new data is a bad idea – RiggsFolly Jul 13 '22 at 18:43
I went for a walk and I think i understood it :D It's gonna be better since not every cell in the food row will have data. And I can probably do it without procedures. Will update tommorow. – jaclaw Jul 13 '22 at 19:23

Send a Procedure in a query from Node to MySQL

1 Answers1