API解决了，而不发送/api/bulk-email/OT_3_5_TEST的响应，这可能会导致请求停滞。

Question

我的API没有发送响应，我得到了一个sqlerror，但是所有正确的输入都是根据我的理解传递的。

下面是我对数据库的调用：

 const fetchData: any = async (po: any) => {
        const response: any = await axios.get(`/api/bulk-email/${po}`);
        //Data fetched...
    };

这是对我根据API使用的数据库的查询：

 `SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,
            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc
            FROM shopper s INNER JOIN orders o on s.id = o.shopper_id INNER JOIN orderitems oi ON o.id = oi.order_id
            INNER JOIN products p ON oi.item_id = p.id
            WHERE oi.po = ? AND (oi.quantity > oi.scanned_qty or oi.scanned_qty is null);`,

这就是我定义PO的地方：

const pullPOs = async () => {
        const response: any = await axios.get(`/api/get-pos`);
        setPOs(response.data);
    };

const handleSelectPO = (e: any) => {
        setPo(e.target.innerText);
        fetchData(e.target.innerText);
    };

当我console.log(po);时，我得到了我想要的答案。当我console.log(fetchData(e.target.innerText));的时候，我得到了一个悬而未决的承诺。我认为是由于未解决的SQL错误。

这是一个错误：

SqlError: Parameter at position 1 is undefined
SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,
            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc
            FROM shopper s INNER JOIN orders o on s.id = o.shopper_i...
sql: SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,
            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc
            FROM shopper s INNER JOIN orders o on s.id = o.shopper_i...
    at Object.module.exports.createError (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/misc/errors.js:56:10)
    at Query.throwUnexpectedError (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/cmd/command.js:38:24)
    at Query.throwNewError (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/cmd/command.js:59:22)
    at Query.validateParameters (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/cmd/query.js:176:14)
    at Query.start (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/cmd/query.js:54:17)
    at Connection.addCommandEnablePipeline (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/connection.js:1077:11)
    at Connection.query (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/connection.js:1312:10)
    at new Promise (<anonymous>)
    at ConnectionPromise._QUERY_CMD (/Users/blakelucey/Desktop/fsd-oms/node_modules/mariadb/lib/connection-promise.js:96:12)
    at __WEBPACK_DEFAULT_EXPORT__ (webpack-internal:///(api)/./pages/api/bulk-email/[po].tsx:15:33) {
  text: 'Parameter at position 1 is undefined\n' +
    'SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,\n' +
    '            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc\n' +
    '            FROM shopper s INNER JOIN orders o on s.id = o.shopper_i...',
  sql: 'SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,\n' +
    '            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc\n' +
    '            FROM shopper s INNER JOIN orders o on s.id = o.shopper_i...',
  fatal: false,
  errno: 45017,
  sqlState: 'HY000',
  code: 'ER_PARAMETER_UNDEFINED'
}
API resolved without sending a response for /api/bulk-email/OT_3_5_TEST, this may result in stalled requests.

这是API的代码：

import type { NextApiRequest, NextApiResponse } from 'next';
import * as pool from '../../../src/utils/dbConnection';
import console from 'console';

export default async (req: NextApiRequest, res: NextApiResponse) => {
    const {
        channel_order_id,
        po,
        id,
        product_id,
        quantity,
        scanned_qty,
        qty_canceled,
        channel_order_item_id,
        description,
        manufacturers_id,
        gtin,
        upc,
        order_id,
        stocked_item,
        ship_firstname,
        ship_lastname,
        ship_email
    } = req.query;
    let conn;
    try {
        conn = await pool.getConnection();
        const rows = await conn.query(
            `SELECT o.channel_order_id, oi.po, oi.id, p.product_id, oi.quantity, oi.scanned_qty,
            oi.qty_canceled, oi.channel_order_item_id, p.description, p.manufacturers_id, p.gtin, p.upc
            FROM shopper s INNER JOIN orders o on s.id = o.shopper_id INNER JOIN orderitems oi ON o.id = oi.order_id
            INNER JOIN products p ON oi.item_id = p.id
            WHERE oi.po = ? AND (oi.quantity > oi.scanned_qty or oi.scanned_qty is null);`,
            [
                channel_order_id,
                po,
                id,
                product_id,
                quantity,
                scanned_qty,
                qty_canceled,
                channel_order_item_id,
                description,
                manufacturers_id,
                gtin,
                upc,
                order_id,
                stocked_item,
                ship_firstname,
                ship_lastname,
                ship_email
            ]
        );

        res.status(200).json(JSON.stringify(rows));
    } catch (err) {
        console.log(err);
    } finally {
        if (conn) conn.end(); // close connection
    }
};

提前感谢您的帮助！

Answer 1

po没有被列在正确的位置。我将po移到上面数组中的第一个位置(channel_order_id上方)，这就解决了我的SQL错误。