CODEWITHSUNDEEP
node.jsmariadb
Mahir Altınkaya
Mahir Altınkaya

Reputation: 439

Node js JSON string insert to DB but cant update same JSON string

I encountered a strange mistake. I add a data that I send with Vue axios post to the database, a column of this data that is converted to JSON object with JSON.stringfy. When I try to update this line later, can I share the idea that I am having the following error? I shared SQL string below.

{
  id: null,
  table_id: 1425,
  user_id: 15,
  order_time: 1586975000253,
  order_status: 1,
  order: [
    {
      product: 8,
      amount: 1,
      portion: [Object],
      order_time: 1586974979254,
      status: 0,
      desc: ''
    },
    {
      product: 4,
      amount: 1,
      portion: [Object],
      order_time: 1586974979707,
      status: 0,
      desc: ''
    },
    {
      product: 8,
      amount: 1,
      portion: [Object],
      order_time: 1586974980271,
      status: 0,
      desc: ''
    }
  ],
  corp: 'sssxx'
}

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'order = '[{\"product\":8,\"amount\":1,\"portion\":{\"id\":1,\"title\":\"Tam Pors' at line 1

UPDATE orders SET order = '[{\\"product\\":8,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974979254,\\"status\\":0,\\"desc\\":\\"\\"},{\\"product\\":4,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974979707,\\"status\\":0,\\"desc\\":\\"\\"},{\\"product\\":8,\\"amount\\":1,\\"portion\\":{\\"id\\":1,\\"title\\":\\"Tam Porsiyon\\",\\"price\\":\\"25\\"},\\"order_time\\":1586974980271,\\"status\\":0,\\"desc\\":\\"\\"}]', order_status = 1 WHERE id = NULL AND table_id = 1425 and corp = 'sssxx'

Upvotes: 0

Views: 81

Answers (1)

Bill Karwin
Bill Karwin

Reputation: 562368

The problem with your code has nothing to do with using JSON.

The word order is a reserved keyword. See https://mariadb.com/kb/en/reserved-words/ You can't use it as a column name unless you delimit it with back-ticks:

UPDATE orders SET `order` = ...whatever...

The clue in the error message is that it complained about the word order, not about anything in your JSON.

...for the right syntax to use near 'order = ...

Syntax errors show you exactly the point in your SQL syntax where the parser got confused.

Upvotes: 1

Related Questions