sqlState: '42000', The multiple inserting can be achieved in .query() not in .execute(). Why?

[stskapilan]

Hi,
I'm trying to insert multiple rows to db,

q = "INSERT INTO `user` (`User_Name`,`Password`,`Email`,`isAuthor`) VALUES ?;"
values = [User_Name,Password,Email,isAuthor]
connection.promise().query(q,[values],(err,results)=>{            
            if(err) console.log("error",err)
            console.log("results",results)
        })

In this above code I can achieve it. but in .execute() I'm not able to do it.
Can anyone explain it?

q = "INSERT INTO `user` (`User_Name`,`Password`,`Email`,`isAuthor`) VALUES ?;"
values = [User_Name,Password,Email,isAuthor]
connection.promise().execute(query,[values],(err,results)=>{            
            if(err) console.log("error",err)
            console.log("results",results)
        })

I'm getting error like this,

code: 'ER_PARSE_ERROR',
  errno: 1064,
  sql: 'INSERT INTO `user` (`User_Name`,`Password`,`Email`,`isAuthor`) VALUES ?;',
  sqlState: '42000',
  sqlMessage: "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"

Originally posted by @stskapilan in #935 (comment)

[sidorares]

Hi @stskapilan

While similar, client side parameters interpolation ( .query() ) and prepared statements ('.execute()` ) are not 100% the same.

Some restrictions for prepared statements:

• you have to have number of ? placeholders equal to number of parameters in the parameter array
• only simple mysql types are supported ( number, date, string, Buffer )
• arrays / objects are not expanded into lists or key=value pairs
• some syntax allowed for "immediate execution / text protocol" is not allowed for prepared statements - see https://dev.mysql.com/doc/refman/8.0/en/sql-prepared-statements.html#prepared-statements-permitted