Array Parameter ToString Problem (err)

[dbwodlf3]

console.log([1,2,3,4,5, [1,2,3]].toString());
1,2,3,4,5,1,2,3

/** ... */

const query = "SELECT * FROM refId=? AND fk IN (?)";
conn.query(query, [1, [1,2,3]] ]);

/** Raise a Error, Becuase it is paased [1, 1, 2, 3] instead of [1, [1,2,3]] */
/** Some case it looks work well but it happens in some complex query including subquery. I don't know what is problem in exactly. */

/** ... */
const pool = mariadb.createPool({
     host: 'mydb.com', 
     user:'myUser', 
     password: 'myPassword',
     connectionLimit: 5,
     arrayParenthesis: true
});

const conn = pool.getConnection();

const query = "SELECT * FROM refId=? AND fk IN ?";
conn.query(query, [1, [1,2,3]] ]);

/** It works. But there aren't about arrayParenthesis in mariadb documents. and, it is ambiguous. 
  ```fk IN ?``` it works, but ```fk IN (?)``` doesn't works.
  So, if turn on arrayParenthesis option then have to change every expression for "IN"
*/

References : https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/master/lib/cmd/query.js#L240 https://github.com/mariadb-corporation/mariadb-connector-nodejs/blob/master/lib/cmd/encoder/text-encoder.js#L43

[rusher]

arrayParenthesis option is only there for compatibility with previous version that was incompatible with mysql/mysql2

Recommendation is to use parenthesis, like :

    await conn.query('CREATE TABLE testArrayParameter (val1 int, val2 int)');
    await conn.query('INSERT INTO testArrayParameter VALUES (1,1), (1,2), (1,3), (2,2)');

    const query = "SELECT * FROM testArrayParameter WHERE val1 = ? AND val2 IN (?)";
    const res = await conn.query(query, [1, [1,3]]);
// result will be: [ { val1: 1, val2: 1 }, { val1: 1, val2: 3} ] 

There is no known issue with that. If you can reproduce some, I would gladly check that.

Connector doesn't use Object.prototype.toString(), because connector escape parameters to prevent injection

[dbwodlf3]

/** it works. */
await conn.query("INSERT INTO test_user (id, username) VALUES (?)", [[1, "user1"]]);

/** it only first item works. */
await conn.query("INSERT INTO test_user (id, username) VALUES (?)", [[2, "user2"], [3, "user3"]]);

/** it doesn't work. */
await conn.query("INSERT INTO test_user (id, username) VALUES ?", [[4, "user4"]]);

/** it doesn't work. */
await conn.query("INSERT INTO test_user (id, username) VALUES ?", [[5, "user5"], [6, "user6"]]);

/** it doesn't work. */
await conn.query("INSERT INTO test_user (id, username) VALUES ?", [[[7, "user7"], [8, "user8"]]);

aboves are excuted just npm install mariadb and with mariadb connection.

https://stackoverflow.com/questions/8899802/how-do-i-do-a-bulk-insert-in-mysql-using-node-js

this is just anti-pattern in mariadb connector(so doesn't support)?

https://stackoverflow.com/questions/37719975/how-to-do-bulk-insert-in-mariadb-using-nodejs https://mariadb.com/kb/en/library/connectornodejs-pipelining/