CODEWITHSUNDEEP
mysqlnode.jsexpressnode-mysql
ceno980
ceno980

Reputation: 2011

node-mysql: Multiple statement queries, ER_PARSE_ERROR

I am trying to run a multiple statement query to a MySQL database. I am using node-mysql and ExpressJS. I have enabled multiple statement queries through setting multipleStatements to true:

const connection = mysql.createPool(config.databaseOptions, {multipleStatements: true});

I have the following multiple statement query:

app.get('/information', urlencodedParser, function (req, res) {
        var mysql = require('./resources/mysqlConnection');

        var qry = "SELECT e.emp_id, company_id, age, country FROM employee as e INNER JOIN company as c ON e.emp_id = c.emp_id WHERE e.emp_id = ?;SELECT * from players as p INNER JOIN teams as t ON p.team_id = t.team_id WHERE country = ?";

        mysql.query(qry, ["E00909", "Japan"], function(err, rows) {
            if (err) throw err;
            console.log(rows);
            var emp_data = rows[0];
            var team_data = rows[1];
            res.render('info.ejs', {data : emp_data});
        });

});

However, when I run the server, I get the following error:

Error: ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT * from players as p INNE'

I have read other posts regarding multiple statement queries but the solution to all those cases was enabling multiple statement queries by setting multipleStatements to true. I have already done this so I'm not sure why am I getting this error. I think that the ["E00909", "Japan"] array in the code is the source of this issue but I am not sure how to resolve it. Further writing the array as ['E00909', 'Japan'] did not solve the problem. Any insights are appreciated.

Upvotes: 4

Views: 1417

Answers (1)

Terry Lennox
Terry Lennox

Reputation: 30685

I would try setting the options in your config.databaseOptions object, e.g.

const config = {
    databaseOptions: {
        host: "some_host",
        user: "some_user",
        password: "some_pw",
        database: "some_db",
        multipleStatements: true
    }
}

Or just try:

config.databaseOptions.multipleStatements = true;

before creating the pool.

This resolves the error for me!

If I omit this line I get the same error as you:

ER_PARSE_ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT..

Upvotes: 3

Related Questions