I'm encountering an issue with a SqlError while trying to retrieve data from my database. It seems like the problem is within my fetchData function where I might not be passing the two parameters (startDate and endDate) correctly.
The specific SqlError message that I am facing is:
SqlError: Parameter at position 1 is undefined
It's important to note that these parameters do contain values, as verified in my console:
start date: 2018-01-01 00:00:00
end date: 2022-08-15 00:00:00
Here is the implementation of my fetchData function along with some helper functions to track the state of startDate and endDate:
const [startDate, setStartDate]: any = useState('');
const [endDate, setEndDate]: any = useState('');
//Retrieve data from the 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);
This is how my API is configured:
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
}
};
Your assistance is highly valued. Thank you!