mysql stored procedures

[Myth06]

When I work with queries written on backend, it works fine. But when call stored procedures from 'client.query' and run using restClient, it first gives result set and at second run gives{affectedRows:0,lastInsetId:0} .What is the problem? Is there a specific way to call stored procedure different from nodejs?

[lideming]

Hi, could you please put some code here that can reproduce the problem?

[Ingrim4]

Hey just encountered the same problem. The reason this happens is that a stored procedure can select rows as well as update rows at the same time so you need to read two packets because the server will return:

1. the selected rows
2. the affected rows

Here's a cheap and hacky work around util this is fixed:

query(query: string, params?: any[]): Promise<any> {
  return this.client.useConnection(async connection => {
    const result = await connection.query(query, params);
    if (query.toLowerCase().startsWith('call ')) {
      await (connection as any).nextPacket();
    }
    return result;
  });
}

[Ingrim4]

From the official documentation:

As of MySQL 5.7.5, the resultset is followed by an OK_Packet, and this OK_Packet has the SERVER_MORE_RESULTS_EXISTS flag set to start processing the next resultset.

The client has to announce that it wants multi-resultsets by either setting the CLIENT_MULTI_RESULTS or CLIENT_PS_MULTI_RESULTS capability.

I would guess that multi-resultset aren't handled correctly since I didn't see any handling of the server status flags in received packets which would normaly indicate a trailing OK_Packet.