CODEWITHSUNDEEP
mysqlnode.jssql-insertmysql-error-1064
TheNoobDeveloper0299
TheNoobDeveloper0299

Reputation: 767

Insert multiple records in mysql using node.js fails

I am trying to INSERT multiple records in a MySQL Table, but whatever I do, it inserts only one row.

Here is the node.js code:

let orderID = 15; //It is a foreign key. Noted it from the table. 
itemParams = [
        [ orderID, 'd', 34.6, '12', '123.jpeg' ],
        [ orderID, 'd', 30.6, '2', '456.jpeg' ]
];

let addOrderItems = await pool.query(`INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES (?)`, itemParams);

When I console log addOrderItems the affectedRows count is 1, so I also checked my table in Workbench to be sure and only one row was added. (oid is a foreign key). Table does have a primary key which auto increments.

The query I found online didn't have paranthesis around the question mark ? but when I remove the paranthesis, I get the following error:

Error: ER_PARSE_ERROR: 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 '19, 'd', 34.6, '12', '123.jpeg'' at line 1
    at Query.Sequence._packetToError (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\sequences\Sequence.js:47:14)
    at Query.ErrorPacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\sequences\Query.js:79:18)
    at Protocol._parsePacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Protocol.js:291:23)
    at Parser._parsePacket (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Parser.js:433:10)
    at Parser.write (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Parser.js:43:10)
    at Protocol.write (Z:\Jura_SaaS\node_modules\mysql\lib\protocol\Protocol.js:38:16)
    at Socket.<anonymous> (Z:\Jura_SaaS\node_modules\mysql\lib\Connection.js:88:28)
    at Socket.<anonymous> (Z:\Jura_SaaS\node_modules\mysql\lib\Connection.js:526:10)
    at Socket.emit (events.js:315:20)
    at addChunk (_stream_readable.js:309:12)
    --------------------
    at Pool.query (Z:\Jura_SaaS\node_modules\mysql\lib\Pool.js:199:23)
    at internal/util.js:297:30
    at new Promise (<anonymous>)
    at Pool.query (internal/util.js:296:12)
    at Z:\Jura_SaaS\routes\order.js:190:44
    at processTicksAndRejections (internal/process/task_queues.js:93:5) {
  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 '19, 'd', 34.6, '12', '123.jpeg'' at line 1",
  sqlState: '42000',
  index: 0,
  sql: "INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES 19, 'd', 34.6, '12', '123.jpeg';"
}

NOTE: I have promisified pool.query that is the reason you don't see the callback format of it.

Upvotes: 0

Views: 3125

Answers (2)

Simon
Simon

Reputation: 66

I had the same issue. Here is an article detailing your case. For your case I would do the following :

let orderID = 15; //It is a foreign key. Noted it from the table. 
itemParams = [
        [ orderID, 'd', 34.6, '12', '123.jpeg' ],
        [ orderID, 'd', 30.6, '2', '456.jpeg' ]
];

let addOrderItems = await pool.query(`INSERT INTO orderitems (oid, description, weight, quantity, image_url) VALUES ?`, [itemParams]);

Upvotes: 0

jsdev2023
jsdev2023

Reputation: 568

Well, I have been using a similar method. Except the fact that I don't insert all the rows at once but execute insertion of each row by executing query in each iteration of a loop.

Note: I'm using MVC server architecture

Here's the code.

  1. Controller
module.export.multiInsert = async (req) => {
    const transferData = req.body.rows;
    let conn = await con.getConnection();

    for await (data of transferData) {
    try {
            await conn.beginTransaction();
           // code for insertion
            const data = await something.addData(conn, a, b, c);
    } catch (error) {
            conn.rollback();

            response.status = false;
            response.result = 'Error occured!'

            console.log(error)

    } finally {
            conn.release();
    }
}
  1. Model
Class something{
static async addData(conn, a, b, c) {

        let params = [a,b,c];

        const [resData] = await conn.execute('INSERT INTO `table` (`a`,`b`,`c`) 
                          VALUES(?,?,?);', params);
        return { "status": resData };
    }
}
  1. Route
router.post('/insert-a-lot-of-data', controller.multiInsert)

Upvotes: 1

Related Questions