3

I got working what i wanted by just doing the insert inside the 2 for-loops, but i felt like it should be better to do one insert after the loop is done.

What i tried:

1: Insert array of objects -> only inserts 1st object in array

  let itemsToDb = [];
  for (let i = 0; i < stashes.length; i++) {
    for (let j = 0; j < stashes[i].items.length; j++) {
      let item = stashes[i].items[j];
      let itemToDb = {
        item_id: item.id,
        verified: item.verified,
        icon: item.icon,
        name: item.name,
        typeline: item.typeLine,
        identified: item.identified,
        ilvl: item.ilvl,
        note: item.note,
        frametype: item.frameType,
        inventory_id: item.inventoryId,
        corrupted: item.corrupted,
        date: new Date().toJSON().slice(0, 19).replace('T', ' ')
      };
      itemsToDb.push(itemToDb);
    }
  }
  let sql = 'INSERT INTO items SET ?';
  db.query(sql, itemsToDb, (error) => {
    if (error) console.log(error);
    console.log(itemsToDb); //item added!
  });

2: Insert array of arrays -> Error (see below)

  let itemToDb = [
    item.id,
    item.verified,
    item.icon,
    item.name,
    item.typeLine,
    item.identified,
    item.ilvl,
    item.note,
    item.frameType,
    item.inventoryId,
    item.corrupted,
    new Date().toJSON().slice(0, 19).replace('T', ' ')
  ];

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 ''45580327d5e9aa29bac9f4461029acd59c379006cdd622a6290ce28bff3496ee', false, 'http' at line 1

Database structure looks like this: enter image description here

3
  • What's value item.id which you use in INSERT?. id is PRIMARY_KEY, if you've set autoincrement, there is possible problem with duplicities. If ypu have real id you must use REPLACE or with INSERT set value item.id to NULL. Commented Apr 27, 2020 at 9:56
  • item_id (filled with item.id) is some unique hash i get from the API data. I don't need to add the id (a.i. primary key) to the array/object right? It will just count automaticly i thought? Commented Apr 27, 2020 at 10:06
  • Yes, it's right - I see it now. I mistake with names... Commented Apr 27, 2020 at 10:17

1 Answer 1

2

In parse_error message you may see that values are without column names. I don't excactly know about object params in node's mysql module. From reading sql error message you try INSERT with VALUES not SET:

INSERT INTO items (`item_id`, `verified`, `icon`, `name`, `typeline`, `identified`, `ilvl`, `note`, `frametype`, `inventory_id`, `corrupted`, `date`) VALUES ?
Sign up to request clarification or add additional context in comments.

4 Comments

You got me on the right track. So indeed use VALUES and then [itemsToDb] instead of just itemsToDb. Tyty!
@Awesom-o: When you define sql db columns, make column's names more specific and don't use sql keywords - date. This column has some reason: date_created_at or created_at or somenthing else...
Makes sense yeah, i will work on that! Ty for the tip!
can I do this with mysql2 node package?

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.