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

The statement `res.render('test-view', { info: camInfo })` in your second version of `app.get(...)` refers to an undefined variable `camInfo`. You probably want `var camInfo = getInfo(req)`, but since `getInfo` is asynchronous by nature, you must first convert it into an asynchronous function, similar to [here](https://stackoverflow.com/a/72633606/16462950). — Heiko Theißen, Sep 16 '22 at 13:10
@HeikoTheißen I need to convert the `app.get(...)` into an asynchronous function? — Yenmangu, Sep 16 '22 at 13:32
@HeikoTheißen I am going to edit my question. I am now getting the error `TypeError: Cannot convert undefined or null to object` — Yenmangu, Sep 16 '22 at 14:16
`getInfo` is now technically an `async function`, but it returns nothing, and `mysqlConf.getConnection` with a callback function as argument also returns nothing. Same for `connection.query`. Consider using `util.promisify` as described [here](https://stackoverflow.com/a/72633606/16462950). A proper asynchronous function returns a `Promise` or an `await`ed value. — Heiko Theißen, Sep 16 '22 at 14:23
I have tried to follow your example in the other question, but Ill be honest, I feel more lost from that than when i began. — Yenmangu, Sep 16 '22 at 14:27

Heiko Theißen · Accepted Answer · 2022-09-16T15:06:27.897

1

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

edited Sep 16 '22 at 15:06

answered Sep 16 '22 at 14:36

Heiko Theißen

12,807
2
7
31

Would you be able to explain the syntax behind the `bind` and `util.promisify`? As you can tell I am relatively new to this, and doing my best to understand. – Yenmangu Sep 16 '22 at 14:42
See https://stackoverflow.com/questions/2236747/what-is-the-use-of-the-javascript-bind-method and https://nodejs.org/dist/latest-v16.x/docs/api/util.html#utilpromisifyoriginal. – Heiko Theißen Sep 16 '22 at 14:43
Secondly. is it possible to use both the `getInfo` and another function performing another query to a separate table `getFeatures` within the `app.get(...)`? – Yenmangu Sep 16 '22 at 14:47
1

Several database queries in one request are possible with `Promise.all`, as described [here](https://stackoverflow.com/a/73063354/16462950). – Heiko Theißen Sep 16 '22 at 14:53
unfortunately, implementing your solution has returned `TypeError [ERR_INVALID_ARG_TYPE]: The "original" argument must be of type function. Received an instance of Query` – Yenmangu Sep 16 '22 at 14:57
1

also caught the `result = results[key];` your implementation needs `result = rows[key]` I am not sure why the original worked with `results` when it wasnt defined before hand, unless its a feature? – Yenmangu Sep 16 '22 at 15:05
I have marked it as correct as it all now works, and I am able to send an object containing both the `camInfo` and the `camFeatures` to the view. – Yenmangu Sep 16 '22 at 15:08

How do I include the req parameters in the SQL query

1 Answers1