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

Question

在 Express/Node 中工作，当尝试执行存储过程时收到错误:

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 '?,?,?,?,?)

这没有什么意义，因为我在项目中有 4 个左右的其他函数使用 1-3 个参数执行相同的操作，并且它们都按预期运行并且不会抛出任何错误。

尝试运行存储过程时。

函数调用:

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))

})

实际功能(在单独的文件中，导入):

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);
      })
  })
}

该过程在工作台中运行时可以正常运行:

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

我最初的想法是它可能与 async/await 有关，但无论如何它都会失败。另外，如果我删除“？”，该函数会抛出预期的“预期 x 参数”，因此它正在识别过程本身。一些任意参数限制？？

Answer 1

服务层定义需要7个参数。

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

在实现过程中，您将其称为:

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

由于 [qbid, date, amount, invId, acc] 是数组，因此您传递了 3 个参数。