CODEWITHSUNDEEP
mysqlnode.jsexpress
reCursed
reCursed

Reputation: 165

Node JS, Express MySQL ES_PARSE_ERROR... "right syntax to use near '?,?,?,?,?"

Working in Express/Node, when attempting to execute stored procedure receiving the 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 '?,?,?,?,?)

Which makes little sense, as I have 4 or so other functions in the project doing the same thing with between 1-3 params and they all behave as expected and throw no errors.

When attempting to run a stored procedure.

function call:

paymentLines.forEach(async payment => {

var qbid = payment.Id
var date = payment.TxnDate
var amount = payment.TotalAmt
var invId = payment.Line[0].LinkedTxn[0].TxnId
var acc = payment.DepositToAccountRef.value

console.log(qbid, date, amount, invId)

await service.updatePaymentDb(db, 'call qbpayments(?,?,?,?,?)', [qbid, date, amount, invId, acc])
  .then(console.log("Payment updated"))
  .catch(() => res.sendStatus(500))

})

The actual function (in separate file, imported):

updatePaymentDb: function (db, proc, qid, date, amount, invId, acc) {
    return new Promise(function(resolve, reject) {
        db.query(proc, invId, date, amount, acc,  qid, (error, res) => {
        if (error) {
            reject(error);
        } else
        resolve(res);
      })
  })
}

The procedure, which functions correctly when run in workbench:

CREATE DEFINER=`rjeadmin`@`%` PROCEDURE `qbpayments`(in invId int, in paydate Date, in amount decimal, in acc int, in qbid int)
BEGIN

set @invoicenumber = (select `Invoice Number` from tbldebtorinvoices where qbid = invId);
set @currId = (select curr_id from tbldebtorinvoices where qbid = invId);
set @entityId = (select EntityID from tbldebtorinvoices where qbid = invId);

insert into tbldebtorinvoicepayments(InvoiceNumber, PaymentDate, PaymentAmount, curr_id, BankAccountID, EntityID, debtorPaymentExRate, qbid)
values(@invoicenumber, paydate, amount, @currId, @entityId, acc, 1, qbid);

select max(`Invoice Number`) from tbldebtorinvoices;

END

My initial thought was that it may have something to do with the async/await, however it fails regardless. Also, if I remove the '?'s, the function throws the expected "expected x arguments", so it is recognising the procedure itself. Some arbitrary parameter limit??

Upvotes: 0

Views: 514

Answers (1)

ssbarbee
ssbarbee

Reputation: 1712

The definition in the service layer needs 7 parameters.

  updatePaymentDb: function (db, proc, qid, date, amount, invId, acc)

During the implementation you call it as:

await service.updatePaymentDb(db, 'call qbpayments(?,?,?,?,?)', [qbid, date, amount, invId, acc])

You pass 3 parameters since [qbid, date, amount, invId, acc] is array.

Upvotes: 2

Related Questions