ER_MAX_PREPARED_STMT_COUNT_REACHED

[okbrown]

Hi,
Can someone please explain why I get this error ER_MAX_PREPARED_STMT_COUNT_REACHED when I am following this documentation

Here is my code:

export const getById = (id) => {
  return db().then(async (connection) => {
    const sql = await connection.format(`
      SELECT * FROM ${baseTables} 
      WHERE ${baseWhere} 
      AND status = 'ACTIVE' 
      AND id = ?`,
      [ id ]
    );
    const resultSet = await connection.execute(sql);
    const row = get(resultSet, "[0].[0]");
    if (row) {
      const rowItem = t.buildRow(row);
      connection.unprepare(sql);
      connection.release();

      return rowItem;
    }
  });
};

Yet every couple months I get this error: ER_MAX_PREPARED_STMT_COUNT_REACHED
I have read:
#158
#393
#702

Should I do the manual version, and do it like this instead?

export const getById = (id) => {
  return db().then(async (connection) => {
    
     const sql = await connection.prepare(`
      SELECT * FROM ${baseTables} 
      WHERE ${baseWhere} 
      AND status = 'ACTIVE' 
      AND id = ?`,
      [ id ]
    );

    const resultSet = await connection.execute(sql);
    const row = get(resultSet, "[0].[0]");
    if (row) {
      const rowItem = t.buildRow(row);
      connection.close();

      return rowItem;
    }
  });
};

Thanks.

[afwn90cj93201nixr2e1re]

Release connection and unprepare before if condition. Coz row can be empty.

[sidorares]

@okbrown you are formatting sql interpolatining parameters on the client and then send it to server to prepare ( each time parameter is different it's a new prepared statement because sql text is different )

Should I do the manual version, and do it like this instead?

main problem is that "format" is client-side interpolation while "prepare" is server-side

Your second example can be still automatic, just send constant query separate from variable parameters with .execute() or .prepare()

export const getById = async (id, connection) => {  
    const resultSet = await connection.execute((`
      SELECT * FROM ${baseTables} 
      WHERE ${baseWhere} 
      AND status = 'ACTIVE' 
      AND id = ?`,
      [ id ]
    );
    const row = get(resultSet, "[0].[0]");
    if (row) {
      const rowItem = t.buildRow(row);
      return rowItem;
    }
  return null;
};