Node-mysql2: Connection's query() vs execute()

[hezjing]

Hi

What is the difference between execute and query? The following statements,

connection.execute('INSERT INTO documents SET ?', { name: 'john' })
will throw an error {"code":"ER_PARSE_ERROR","errno":1064,"sqlState":"#42000"}

However, connection.query('INSERT INTO documents SET ?', { name: 'john' }) works fine.

[sidorares]

execute prepares statement first and then executes it ( or next time you use same query it's not prepared but reused). Main difference is that with query parameters are interpolated on the driver side, while with execute() they are interpolated on server.

Query:

   driver -> server: query "INSERT INTO documents SET name='john'" 
   server => driver: ok, insertId, ...

   driver -> server: query "INSERT INTO documents SET name='smith'" 
   server => driver: ok, insertId, ...

Execute:

     driver -> server: prepare "INSERT INTO documents SET name=?"
     server -> driver: statement id = 1

     driver -> server:   execute 1, parameters: 'john'
     server -> driver: ok, insertId, ...

     driver->server: execute 1, parameters: 'smith'
     server -> driver: ok, insertId, ...

[hezjing]

But from the documentation, the execute() will prepare the statement internally.

[sidorares]

"internally" = transparently to user, you don't need to make two prepare+execute calls. The statement is prepared by mysql server