Error mysql executing multiple rows with data json

Question

I'm trying to make a multiple rows update. It works perfectly with 1 row, but with multiple having error sintax. The configuration hsa been set correctly { ..., multipleStatements: true }

But im doing this kind of query, generated from a mapping of list items.

const query = 'UPDATE `ROW_INFO` SET `JSON` = ? WHERE `ID` = ?;UPDATE `ROW_INFO` SET `JSON` = ? WHERE `ID` = ?;'
const values = [ [ {foo:'bar1'}, 1 ], [ {foo:'bar2'}, 2 ] ]
await connection.execute(query, values)

I tried all as 1 array, wrapping in array, in 2 arrays, but nothing, having same 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 'UPDATE `ROW_INFO` SET `JSON` = `[object Object]` WHERE `ID` = 1' at line 1

If i try to set values on string get worst errors, beacuse json can not be set on query for this case. Hope someone can help me with this kind of error, or how to set the right way the values.

put the json in quotes, so that iot will not be interpreted as part of the javascript — nbk
– nbk, Commented Oct 24, 2023 at 7:14
you pass an object to mysql, but you need to pass the json as string, something like andrew already said, mysql will convert the string to json automatcally — nbk
– nbk, Commented Oct 24, 2023 at 10:16
@nbk not only with that, looks like have to change from execute to query too, thanks! — Alberto Acuña
– Alberto Acuña, Commented Oct 24, 2023 at 15:46

nafrolov · Accepted Answer · 2023-10-24 07:51:19Z

0

What about running execute for each row separately? (although I'm not sure about the performance)

Like this:

const values = [ [ {foo:'bar1'}, 1 ], [ {foo:'bar2'}, 2 ] ]

for (const value of values){
    await connection.execute(query, value)
}

Refer to this thread for other examples.

answered Oct 24, 2023 at 7:51

nafrolov

915 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Alberto Acuña Over a year ago

thats the problem, the performance, im trying to minimize this, also a for with await is wrong option, should be like a promise all, but it creates n times connections for execute, so is like execevie if you have to update 100 rows for example

nafrolov Over a year ago

@AlbertoAcuña yeah, you are right. Here is another thread on the topic. A (straighforward) workaround would be to add parameters to a query for each element in values array inside for cycle and add this query to the final query on which to call connection.execute() (in the end, after the for cycle)

Collectives™ on Stack Overflow

Error mysql executing multiple rows with data json

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related