Can we convert bulk insert query to parameterized query

[muralishetty2558]

const valuesStr = tPayload.map((values) => {
   sql += `("${values.col1}","${values.col2}","${values.col3}","${values.col4}"`;
 }).join(',');
 
 let sql = `INSERT INTO TABLE_NAME_1
  (col1, col2, col3, col4) VALUES ${valuesStr}`;

 const [row] = await this.pool.execute(sql);

Can you help me convert this to Parameterized query ?

[sidorares]

not sure what you mean, .execute() automatically prepares a statement for you and then executes it.
Do you want to prepare it manually and execute some time later?

[muralishetty2558]

Actually every time the code is ran, because of varying tPayload array size, new query is cached in mysql. Can we avoid multiple queries being cached using (prepared statements) Parameterized query?

[muralishetty2558]

I have updated my question. Hopefully it makes sense now..

[sidorares]

I'd rather use plain text query ( e.i non prepared statement version ). New sql === new statement

The only solution I can think of is to have stored procedure that accepts JSON array as a parameter. In that case SP call is a one fixed query ( once prepared and cached ) and JSON array string is your parameter

[muralishetty2558]

To be just clear, "use plain text query" means use query helper instead of execute. const [row] = await this.pool.query(sql); like this?

And thanks for the stored procedure solution, will give try it.

[sidorares]

Yes, .query()