Nodejs MySQL ER_PARSE_ERROR on VALID query

Question

I've tried using mysql lib with nodejs and a simple query like SELECT * FROM table; works, but now that I've tried to construct a real query to update my database it doesn't work.

I have used an online validating tool and it checks out.

    var mysql = require('mysql');
    var request = require('request');
    request.get('http://localhost:8080/dump/asda.dump', function (error, response, body) {
        if (!error && response.statusCode == 200) {
            var data =JSON.parse(body);
            var products = data['products'][0];
            var myquery = "INSERT INTO `products2` (";
            var midquery = ") VALUES (";
            
            for (var k in products) {
                if (typeof products[k] === 'number') var v = products[k];
                else if (typeof products[k] === 'string') var v = "\'" + products[k]+ "\'";
                else if (typeof products[k] === 'boolean') var v = products[k];
                else continue;
                myquery = myquery + "`" + k + "`,";
                midquery = midquery  + v + ",";
            }
            myquery = myquery.slice(0,-1);
            midquery = midquery.slice(0, -1);

            print(myquery + midquery + ")");

            connection.connect();
            connection.query(myquery, function (err, rows, fields) {
                if (!err) console.log(rows);
                else console.log(err);
            });
            connection.end();
        }
    });

I have tried both the version with the ticks and without the ticks an none of them work.

Possible reasons might be

Too long query. Maybe some internal char limit exeeded?
Unsupporded characters. I am pretty sure I have quite a few Croatian ćčćš in there. How to I support that?
Improper ' " escaping (although it seems ok to me).
I have 'code' as a table column. I've checked and it's not a reserved keyword in mysql but it's blued in MySQL Workbench so maybe it breaks things somehow.

What I get is:

{ [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 '' at line 1]
  code: 'ER_PARSE_ERROR',
  errno: 1064,
  sqlState: '42000',
  index: 0 }

I see where you print myquery + midquery + ")", which will print the entire concatenated query, but only myquery gets passed to the connection.query method, which is only part of the total query. — Paul Griffin
– Paul Griffin, Commented May 1, 2015 at 14:51
THANK YOU. Should I delete my post now since the problem is kinda unrelated to the title? — ditoslav
– ditoslav, Commented May 1, 2015 at 15:28

Abhishek Dhanraj Shahdeo · Accepted Answer · 2015-05-02 05:34:42Z

1

You should try with the ticks whenever you are using the database entities inside the query like the table names or database variables. As far as your query is concerned, only myquery is getting into the function as your database statement and it doesn't contain the whole query. As a result you are getting an error because of your incomplete complete query and improper syntax in it(as it is incomplete already). Your print statement will print it right because of the concatenation that you have used. If you are able to keep the concatenated query string in a variable such as:

    var new_query=myquery + midquery + ")";

And then using it as

    connection.query(new_query, function (err, rows, fields) {
            if (!err) console.log(rows);
            else console.log(err);
        });

I think your query should work. Thank you...!

answered May 2, 2015 at 5:34

Abhishek Dhanraj Shahdeo

1,3662 gold badges14 silver badges35 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Nodejs MySQL ER_PARSE_ERROR on VALID query

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related