I'm running into a SqlError when requesting data from my database. Specifically, I believe the problem lies within my fetchData function; likely, I'm not passing the two parameters (startDate and endDate) correctly.

As stated in the title of the post the SqlError I am experiencing is as follows:

SqlError: Parameter at position 1 is undefined

Also, please be aware these parameters do have values, per my console:

start date:  2018-01-01 00:00:00
end date:  2022-08-15 00:00:00

Here is my fetchData function as well as additional helper functions that define the state of startDate and endDate:

   const [startDate, setStartDate]: any = useState('');
   const [endDate, setEndDate]: any = useState('');

    //Get data from database
    useEffect(() => {
        fetchData();
    }, [startDate, endDate]);

    const fetchData: any = async () => {
        const response: any = await axios.get(`/api/get-canceled/${[startDate, endDate]}`);
        let dataRows: any[] = response.data;
        console.log('response', response.data);
        setRows(dataRows);
    };

  const handleStart = (e: any) => {
        const inputStart = e.target.value;
        setStartInput(inputStart);
        const start = `${inputStart} 00:00:00`;
        setStartDate(start);
    };
    console.log(startDate);

    const handleEnd = (e: any) => {
        const inputEnd = e.target.value;
        setEndInput(inputEnd);
        const end = `${inputEnd} 00:00:00`;
        setEndDate(end);
    };
    console.log(endDate);

Here is my 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 { startDate, endDate }: any = req.query;
    let conn;
    try {
        conn = await pool.getConnection();
        const rows = await conn.query(
            `SELECT oi.po, oi.id, p.product_id, p.vendor_id, oi.quantity, oi.scanned_qty, oi.order_id, oi.qty_canceled, oi.channel_order_item_id, o.order_datetime, 
            o.ship_lastname, o.ship_firstname, o.ship_email, o.channel_order_id, o.channel_order_id_alt, o.channel_id, p.description, p.stock_status 
            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 o.order_datetime > ? AND o.order_datetime < ? AND oi.qty_canceled > 0;`,
            [startDate, endDate]
        );

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

Any help is greatly appreciated. Thank you!

CodePudding user response：

You should use the params object when calling the axios get method.

axios.get('/api/get-canceled', { params : { startDate, endDate }});

CodePudding user response：

Try replacing your fetchData function in front end.

const fetchData: any = async () => {
       const response: any = await axios.get(`/api/get-canceled?startDate=${startDate}&endDate=${endDate}`);
       let dataRows: any[] = response.data;
       console.log('response', response.data);
       setRows(dataRows);
};