Retrieve Data from Just-Inserted Row (MySql and Node.js)

Question

if I have a query like the following:

var queryString = "INSERT INTO pid SET title = '" + randomTitle + "', poc = '" + random + "';"
connection.query(queryString, function(err, rows, fields) {

...(do something here)
});

, is there a way I can retrieve information for the just-inserted row without performing a new query (in my particular case, I want the auto-generated primary key value).

For instance, can I use the construct with the "query" object (below) and then perhaps use one of the query.on callback to retrieve the information about the just-inserted row?:

var query = connection.query(queryString, function(err, rows, fields) {

    query.on('fields', function(fields) {
    ... get the field information?
    });

    query.on('result', function(row) {
    .. get the field information?
    });


});

If not via the query callbacks, is there another way? Thanks for any response!

score 11 · Answer 1 · answered Jan 07 '16 at 21:22

According to docs it is possilbe. Notice that callback function of insert query does not have rows and fields but only result parameter:

connection.query(queryString, function(err, result) {
    if (err) {
        // handle error
    }
    console.log(result.insertId); // prints inserted id
}

Your query is also vulnerable to sql injection. It should look like this:

var queryString = "INSERT INTO pid SET title = ?, poc = ?";
connection.query(queryString, [randomTitle, random], function(err, result) {
    // ...
}

Retrieve Data from Just-Inserted Row (MySql and Node.js)

1 Answers1