CODEWITHSUNDEEP
javascriptmysqlnode.jsexpress
user11729531
user11729531

Reputation:

Error when attempting to insert data into MySql database using Express

I am attempting to run an insert query to a database, however it gives me the following error:

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 \'INSERT INTO house_totals (`currentDate`, `receipts`, `qty`, `pounds`, `ppu`, `gr\' at line 1',
  sqlState: '42000',
  index: 0,
  sql:
   'DELETE FROM house_totals WHERE currentDate = \'077,2020\' AND house_number = \'4\'; INSERT INTO house_totals (`currentDate`, `receipts`, `qty`, `pounds`, `ppu`, `gross_receipts`, `average`, `house_number`) Values (\'077,2020\', \'1 lb Button Box - Organic\', 8765, 0, 1.35, 0, 0, \'4\'), (\'077,2020\', \'1 lb Button in Plastic\', 0, 0, 1.15, 0, 0, \'4\'), (\'077,2020\', \'1 lb Button in tray\', 0, 0, 1.25, 0, 0, \'4\'), (\'077,2020\', \'1 lb Button Plastic - Organic\', 0, 0, 1.35, 0, 0, \'4\'), (\'077,2020\', \'1 lb Button Tray - Organic\', 0, 0, 1.43, 0, 0, \'4\'), (\'077,2020\', \'1 lb Crimini Bulk\', 0, 0, 1.18, 0, 0, \'4\'), (\'077,2020\', \'1 lb Large\', 0, 0, 1.15, 0, 0, \'4\'), (\'077,2020\', \'1 lb Large - Organic\', 0, 0, 1.4, 0, 0, \'4\'), (\'077,2020\', \'1 lb Medium in Box\', 0, 0, 1.22, 0, 0, \'4\'), (\'077,2020\', \'1 lb Medium Plastic\', 0, 0, 1.1, 0, 0, \'4\'), (\'077,2020\', \'1 lb Medium Plastic - Organic\', 0, 0, 1.34, 0, 0, \'4\'), (\'077,2020\', \'10 oz Medium Tray\', 0, 0, 1.04, 0, 0, \'4\'), (\'077,2020\', \'12 oz Medium Black Trays\', 0, 0, 1.1, 0, 0, \'4\'), (\'077,2020\', \'12 oz Medium Blue Trays - Organic\', 0, 0, 1.22, 0, 0, \'4\'), (\'077,2020\', \'16 oz Medium in Trays\', 0, 0, 1.14, 0, 0, \'4\'), (\'077,2020\', \'16oz Medium In Trays - Organic\', 0, 0, 1.39, 0, 0, \'4\'), (\'077,2020\', \'24 oz Large in Blue Trays\', 0, 0, 1.14, 0, 0, \'4\'), (\'077,2020\', \'24oz Baby Bella Trays\', 0, 0, 1.2, 0, 0, \'4\'), (\'077,2020\', \'24oz Large in Black Trays - Organic\', 0, 0, 1.45, 0, 0, \'4\'), (\'077,2020\', \'3 lb Fancy in Box\', 0, 0, 3.3, 0, 0, \'4\'), (\'077,2020\', \'8oz Mediums in Trays - Organic\', 0, 0, 1.39, 0, 0, \'4\'), (\'077,2020\', \'36 Count\', 0, 0, 6, 0, 0, \'4\'), (\'077,2020\', \'4 oz Medium Tray\', 0, 0, 1.27, 0, 0, \'4\'), (\'077,2020\', \'3.5\\" Port\', 0, 0, 1.35, 0, 0, \'4\'), (\'077,2020\', \'4\\" Port\', 0, 0, 1.35, 0, 0, \'4\'), (\'077,2020\', \'42 Count\', 0, 0, 4.85, 0, 0, \'4\'), (\'077,2020\', \'5 lb Crimini\', 0, 0, 6.5, 0, 0, \'4\'), (\'077,2020\', \'5 lb Medium\', 0, 0, 5.5, 0, 0, \'4\'), (\'077,2020\', \'8 oz Baby Bella Trays\', 0, 0, 1.2, 0, 0, \'4\'), (\'077,2020\', \'8 oz Blue Special\', 0, 0, 1.14, 0, 0, \'4\'), (\'077,2020\', \'8 oz Mediums in Trays\', 0, 0, 1.14, 0, 0, \'4\'), (\'077,2020\', \'8oz Mediums in Trays - Organic\', 0, 0, 1.39, 0, 0, \'4\'), (\'077,2020\', \'Opens\', 0, 0, 0.58, 0, 0, \'4\'), (\'077,2020\', \'Opens - Organic\', 0, 0, 1.35, 0, 0, \'4\'), (\'077,2020\', \'Soup\', 0, 0, 0.65, 0, 0, \'4\');'

My code is: 

...
let sqlData = JSON.parse(req.body.jsonPost);
  //console.log(sqlData)
  var result = [];
  var date = "";
  for (let i = 0; i < sqlData.length; i++) {
    let toPush = [];
    toPush.push(sqlData[i]["currentDate"]);
    date = sqlData[i]["currentDate"];
    toPush.push(sqlData[i]["receipts"]);
    toPush.push(sqlData[i]["qty"]);
    toPush.push(sqlData[i]["pounds"]);
    toPush.push(sqlData[i]["ppu"]);
    toPush.push(sqlData[i]["gross_receipts"]);
    toPush.push(sqlData[i]["average"]);
    //toPush.push(sqlData[i]["houseNumber"])
    toPush.push(req.params.id.toString())

    result.push(toPush);
  }
  queryString = 'DELETE FROM house_totals WHERE currentDate = ? AND house_number = ?; INSERT INTO house_totals (`currentDate`, `receipts`, `qty`, `pounds`, `ppu`, `gross_receipts`, `average`, `house_number`) Values ?;'
  getConnection().query(queryString, [date.toString(), req.params.id.toString(), result], function (err, results, fields) {
...

I've run the command in MySql workbench, and it executes fine, if I remove the slashes, but the error continues in my Express app

I've tried multiple ways of removing the forward slashes, but the error persists even without the slashes, so I am running out of ideas at this point. Any help would be appreciated.

Upvotes: 0

Views: 40

Answers (1)

user11729531
user11729531

Reputation:

run the delete and insert as separate queries. Put them in the same transaction if they need to be atomic. – danblack

Upvotes: 1

Related Questions