0

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.

O. Jones
  • 103,626
  • 17
  • 118
  • 172
TheNoobDeveloper0299
  • 667
  • 1
  • 6
  • 14
  • Please note that ```mysql``` is an older npm package. Use ```mysql2```. You can refer this link, I had solved before. https://stackoverflow.com/questions/64464555/unnesting-node-database-calls/65138795#65138795 – Srinath Kamath Dec 16 '20 at 11:28
  • 1
    First off, you might've to look into changing ```values(?)``` to ```values(?,?....)``` – Srinath Kamath Dec 16 '20 at 11:36
  • Okay, I'll try using mysql2 first, but you put only one question mark when you're inserting an array of arrays – TheNoobDeveloper0299 Dec 16 '20 at 15:37
  • Why is it so? I had tried with one question mark and I had similar problems – Srinath Kamath Dec 17 '20 at 05:03
  • 1
    You add multiple question marks (as many as the fields) when you are inserting one tuple/row. If you want to INSERT multiple tuples, you will provide a list of list, so the lists will be related to the question marks and not the items in the list, this is my conclusion after some trial and error – TheNoobDeveloper0299 Dec 19 '20 at 06:08

2 Answers2

1

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)
Srinath Kamath
  • 542
  • 1
  • 6
  • 17
0

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]);
Simon
  • 56
  • 6