How do I include the req parameters in the SQL query

Question

The context

I am writing a node app which injects the result of a SQL query into an EJS view.

I have written a single query into the app.get() function, and it works perfectly.

This is the working code for the single query, to get the result from table camera_info

app.get('/product-page/:product_code', (req, res) => {
    mysqlConf.getConnection((err, connection) => {
        if (err) throw new err();
        console.log(`connected as id ${connection.threadId}`);
        connection.query('SELECT * FROM camera_info WHERE product_code = ?', [req.params.product_code], (err, rows) => {
            connection.release();
            if (!err) {
                results = rows;
                var result;
                Object.keys(results).forEach(function (key) {
                    result = results[key];
                    console.log(result);
                });
                res.render('test-view', { data: result });
            } else {
                console.log(err);
            }
        });
    });
});

I need to perform two queries to different tables camera_info and cam_features and inject the response into the view engine, so I am trying to write a function for each query, and include the functions into the app.get function, so as not to have a HUGE app.get() function.

I need to out put the result from the cam_info table as a list with a forEach statement in the test-page view, and there are a different number of 'features' for each camera, so thats why I am querying two different tables.

The Road Block

I don't know how to include the req.params of the initial app.get() as parameters in the functions, and this is what I have tried so far:

function getInfo(req) {
    var camInfo;
    mysqlConf.getConnection((err, connection) => {
        if (err) throw new err();
        console.log(`connected as id ${connection.threadId}`);
        connection.query('SELECT * FROM camera_info WHERE product_code = ?', [req.params.product_code], (err, rows) => {
            connection.release();
            if (!err) {
                results = rows;
                var result;
                Object.keys(results).forEach(function (key) {
                    result = results[key];
                    console.log(result);
                    camInfo = result;
                });
            } else {
                console.log(err);
            }
            return camInfo;
        });
    });
}

and then call the function like such:

app.get('/product-page/:product_code', (req, res) => {
    // getFeatures();
    getInfo(req);
    res.render('test-view', { info: camInfo });
    console.log(features);
});

However the result in the terminal, as well as crashing the server, is TypeError: Cannot read properties of undefined (reading 'product_code')

I can provide the full stack trace if need be.

EDIT - removed unnecessary 'returns' from the first line of the question.

EDIT 2 -


async function getInfo(req) {
    var camInfo = await mysqlConf.getConnection((err, connection) => {
        console.log(`connected as ID ${connection.threadId}`);
        try {
            connection.query(
                'SELECT * FROM camera_info WHERE product_code =?',
                [req.params.product_code],
                (err, rows) => {
                    connection.release();
                    if (!err) {
                        result = rows;
                        var result;
                        Object.keys(results).forEach(function (key) {
                            result = results[key];
                            console.log(result);
                        });
                    }
                }
            );
        } catch (error) {
            console.log(error);
        }
    });
}

app.get('/product-page/:product_code', (req, res) => {
    // getFeatures();
    var camInfo = getInfo(req);
    res.render('test-view', { info: camInfo });
});

Heiko Theißen · Answer

Your problem is not how to access the req.params, but how to implement the asynchronous database operation and return the result from an asynchronous function. (Such questions are often classified a duplicate of How do I return the response from an asynchronous call?)

The following is an attempt to transfer this solution to your problem:

async function getInfo(req) {
  var connection = await util.promisify(mysqlConf.getConnection.bind(mysqlConf))();
  console.log(`connected as id ${connection.threadId}`);
  var rows = await util.promisify(connection.query.bind(connection))(
    'SELECT * FROM camera_info WHERE product_code = ?', [req.params.product_code]);
  connection.release();
  var result;
  Object.keys(rows).forEach(function (key) {
    result = rows[key];
    console.log(result);
  });
  return result;
}
app.get('/product-page/:product_code', async (req, res) => {
  var camInfo = await getInfo(req);
  res.render('test-view', { info: camInfo });
});

How do I include the req parameters in the SQL query

1 Answers