node.js SQL Insert Into with array

Question

I'm new to Node.js and I've hit a roadblock I can't figure out. I'm trying to add an array to a SQL table using an INSERT INTO query. Following the example [here][1] I see that I add a question mark as a placeholder for my values array, and the code takes care of the rest - but that doesn't seem to be working.

Here's the code:

var temp = [1,3,2];
conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES ? ';

let query = connection.query(conn2string, temp, function (err, result) { // this will insert in to data base  //[vals2[0]]

And the resulting SQL connection string is:

'INSERT INTO test(`0`, `1`, `2`) VALUES 1 `

What I'm expecting Is:

'INSERT INTO test(`0`, `1`, `2`) VALUES (1, 3, 2); `

Any suggestions what I'm doing wrong?

Answer 1

To do this with the mysql package, you need to wrap the temp variable inside an array. Remember to add the parentheses around the ? of the VALUES.

var temp = [1, 3, 2];
conn2string =
  "INSERT INTO " + process.env.DB_TABLE + "(`0`, `1`, `2`) VALUES (?) ";

let query = connection.query(conn2string, [temp], function (err, result) {
  
});

This is because the parameter after the SQL string expects an array whose elements match each ? or ?? in the SQL string. By passing the temp array directly, you're essentially telling it that the first ? is 1, the second ? is 3, the third ? is 2. However, when you wrap the temp array in another array, you're essentially telling it that the value of the first ? is the temp array, and it formats it accordingly.

Answer 2

you can make a string value from array:

let query = connection.query(conn2string, '(' + temp.join(',') + ')', function (err, result) {

or, better, use full template:

var temp = [1,3,2];
conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES (?, ?, ?) ';

and to pass temp[0], temp[1], etc

or not to use a template:

conn2string= "INSERT INTO " + process.env.DB_TABLE + '(`0`, `1`, `2`) VALUES (' + temp[0] + ', ' + temp[1] + ', ' + temp[2] + ')';

Answer 3

you can try embedded the data into sql string directly using this way:

conn2string= `INSERT INTO ${process.env.DB_TABLE} (`0`, `1`, `2`) VALUES (${temp.join(',')})`;

or I think you must insert n "?" like the numbert of element in array