Unable to execute bulk insert query

[soysandeep]

I was trying to bulk insert rows into a table and I'm getting an error. Isn't this how we are supposed to insert multiple rows with one query?

sql.execute(
  `INSERT INTO ${MENU_TABLE} VALUES ?`,
  values,
  () => { ... }
);

values here is an array of array, like - [ ['1', 'foo' ,'bar' ], [ .. ], [ .. ] ]

Result:

{
    "message": "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 '?' at line 1"
}

Additional info:

• mysql2 version 2.3.3

[sidorares]

Not with execute - it does not work like "query builder" and requires exactly same number of ? placeholders as the number of parameters you pass. Either use .query or expand placeholder

[SilasGrygier]

Hi @sidorares, is the query() command still a prepared statement because the docs for mysql2 only mention the execute() command explicitly for prepared statements, thanks in advance!

[sidorares]

query() is not using prepared statements.

query() steps:

• perform client side parameters interpolation ( also available via format() and escape() functions, also available as a standalone package https://www.npmjs.com/package/sqlstring )
• send result of the above step as COM_QUERY mysql command
• serialise the response rows

execute() steps:

• check if there is a previously prepared statement for the parametrized query you have
• if there is, perform COM_STMT_EXECUTE command with statement id and parameters
• if not, prepare first with COM_STMT_PREPARE command, save result, then execute
• no client side parameter interpolation step

[SilasGrygier]

Great thanks so much for the answer! Based on your response I've put together an example insert-bulk query that uses a prepared statement:

const product_array = [ [ '1', 'dry-rain', 0], ['2', 'velvet-desk', 0 ], ['3', 'metal-chair', 0  ] ] 
let sql = 'insert into product (id, title, counter) values ?';
sql = mysql.format(sql, [product_array]);
let result = await conn.query(sql).catch(error => {
    conn.release();
    throw new Error('Error during SQL insertion', { cause: error });
});

[sidorares]

@SilasGrygier since you use .query() there is no prepared statement in your example. Also .query would call format automatically.

const product_array = [ [ '1', 'dry-rain', 0], ['2', 'velvet-desk', 0 ], ['3', 'metal-chair', 0  ] ] 
try {
  let result = await conn.query('insert into product (id, title, counter) values ?', [product_array]);
  // ...
} catch(error) {
  // ...
}

Example which uses prepared statement is exactly as above but with .query replaced with .execute

const product_array = [ [ '1', 'dry-rain', 0], ['2', 'velvet-desk', 0 ], ['3', 'metal-chair', 0  ] ] 
try {
  let result = await conn.execute('insert into product (id, title, counter) values ?', [product_array]);
  // ...
} catch(error) {
  // ...
}