Cannot retrieve OUT param of Stored Procedure in result set

[CaptShmo]

I don't see anything in the documentation about this, so maybe it's not supported, but the result set has an "undefined" value included in it when I make the attempt so I feel like it's trying to do it but something is going wrong.

I have a stored procedure which has a single OUT param I pass into it, and am trying to somehow get at that value when the result is returned (I am using the Promise method). See some example code below to elaborate.

Assume I have a table named "test_table" with two columns, "id" and "name". I have a single test row in there, id = 1, name = 'myTestName'. I have a mysql2 'dbConnection' variable in node which is successfully connecting to my database (this is using the database pooling method with a mysql8 database).

Stored Procedure example:

CREATE PROCEDURE IF NOT EXISTS P_TestTable_GetId (
      i_name
      OUT o_my_output_id
  )
  BEGIN

      SELECT id from test_table
      WHERE name = i_name
      LIMIT 1
      INTO o_my_output_id;
  
  END

Then, in node / mysql2, I am attempting to do something like this:

const getIdQueryResult = await dbConnection.query(
`CALL P_TestTable_GetId(?, @o_my_output_id)`, [ 'myTestName' ]
);

console.log(getIdQueryResult);

When I execute this, I do get results in getIdQueryResult, but the '@myOutputId" out param is nowhere to be found. There is, however, an 'undefined' key in there, which doesn't exist when I don't pass in '@o_my_output_id', so I think it's trying to fulfill this request but either my syntax is wrong or it's unable to comply for whatever reason.

I have tried tons of different combinations here, passing variables in with the query, different naming conventions for the params, etc. If this isn't supported please let me know, and if it is supported please put it in the documentation how this is accomplished.

Thanks!

[sidorares]

hi @CaptShmo sorry somehow missed this message

I think you are supposed to access OUT variable with extra select query, see examples in https://dev.mysql.com/doc/refman/8.0/en/create-procedure.html

in your case:

await dbConnection.query(
`CALL P_TestTable_GetId(?, @o_my_output_id)`, [ 'myTestName' ]
);
const getIdQueryResult = await connection.query('SELECT @o_my_output_id');
console.log(getIdQueryResult);

[sidorares]

re "undefined" - this is what mysql2 returns for insert/update/delete statements when no column definitions are sent from the server in the response, only "ok packet" with affectedRows etc status