Not able to parametarize the limit in stored procedure

[Himadri93]

const command = SELECT * FROM TableName ORDER BY id LIMIT ?;;
await connect.promise().execute(command, [itemsPerPage]);

Hello, i am parameterize the limit VALUE and requesting the query using execute command, its throwing me error status 500,
how can i achieve the same?

[trasherdk]

I don't see any stored procedure in your question.

I'm guessing, you are looking for this: ( from the README.md )

MySQL2 exposes a .promise() function on Connections, to "upgrade" an existing non-promise connection to use promise

// get the client
const mysql = require('mysql2');
// create the connection
const con = mysql.createConnection(
  {host:'localhost', user: 'root', database: 'test'}
);
con.promise().query("SELECT 1")
  .then( ([rows,fields]) => {
    console.log(rows);
  })
  .catch(console.log)
  .then( () => con.end());

[sidorares]

@Himadri93 not sure if related, but have a look at #1239

TLDR: currently execute sends js numbers as DOUBLE mysql type in a PS call parameter, and mysql server > 8.0.22 does not like this combination ( LONG int in expected parameter type and DOUBLE in actual type ). One way to work around this issue right now is to convert your number parameter to a string, in that case its sent as VAR_STRING and for some reason string -> long conversion is allowed )

[sidorares]

I don't see any stored procedure in your question.

My guess is that meant to be "prepared statement" but please correct me if I'm wrong @Himadri93 and you are actually using stored procedures

[JuanGdelaCruz]

Keeps happening as of version '8.0.27'.
With offset & limit in prepared statements.

[sidorares]

Keeps happening as of version '8.0.27'. With offset & limit in prepared statements.

Can you confirm if converting parameter to a string fixes a problem for you @JuanGdelaCruz ?

[JuanGdelaCruz]

Yes it does!

[sidorares]

I wonder if it worth adding as temporary fix a link to error explanation and ways to fix until we have 100% reliable automatic solution

[sidorares]

Only when error is "Incorrect arguments to mysqld_stmt_execute"

[sidorares]

closing as exact duplicate of #1239