MySqlConnector: Parameter direction not supported in CommandType.Text

There’s an issue when using parameter direction with Pomelo EF Core provider. Both FromSql() and ExecuteSqlCommand() can accept arrays of MySqlParameter (derived from DbParameter). If the Direction property is set to either InputOutput or Output it will not work as expected.

This issue was already touched on in issue https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql/issues/194#issuecomment-278469618.

This is due to the query being executed as CommandType.Text which uses TextCommandExecutor as opposed to the more advanced parameter support in StoredProcedureCommandExecutor.

While I understand the limitations of CommandType.Text, it does mean there is a disparity between MySqlConnector and System.Data.SqlClient which does handle output parameters. It does this by using sp_executesql to run the query. For example:

set @p1=NULL
exec sp_executesql N'EXEC MyTest @ParamOut OUTPUT',N'@ParamOut int output',@ParamOut=@p1 output
select @p1

Is there a way to do something similar using prepared statements in MySQL? For example:

SET @p1 := NULL;
SET @qry = 'CALL MyTest(@p1)'
PREPARE stmt FROM @qry
EXECUTE stmt
DEALLOCATE PREPARE stmt
SELECT @p1

If this isn’t feasible to do or a design decision is made to not support it then I think an exception should be thrown when Direction property is set to something other than ParameterDirection.Input as it won’t work as expected.

Anyway this is something which would benefit from discussion and I’d like to get other people’s viewpoints on this issue.

About this issue

Original URL
State: closed
Created 7 years ago
Comments: 35 (27 by maintainers)

Most upvoted comments

Prepared statements imply the binary protocol. (Unprepared, i.e., regular, statements use the text protocol.) Furthermore, I believe that SERVER_PS_OUT_PARAMS is only applicable to prepared statements that invoke stored procedures, not prepared arbitrary SQL. This will be covered by #742.

bgrainger on Dec 5, 2019

This repo (MySqlConnector) deals purely with ADO.NET; if you want an answer about EF Core, then https://github.com/PomeloFoundation/Pomelo.EntityFrameworkCore.MySql (or Stack Overflow) is the right place to raise the issue.

(It’s still not clear to me what this issue–ADO.NET output parameters for CommandType.Text commands–has to do with your scenario.)

bgrainger on Dec 3, 2019

Would you be able to grab the OUT params of SP from the OK packet when SERVER_PS_OUT_PARAMS is set?

MySqlCommand.Prepare isn’t currently supported for CommandType.StoredProcedure; it should be able to be supported when that’s implemented: https://github.com/mysql-net/MySqlConnector/issues/742.

bgrainger on Dec 2, 2019

Sure, no prob. The code snippets below using EF Core should help.

var outParam = new SqlParameter("@ParamOut", DbType.Int32)
    { Direction = ParameterDirection.Output };
ctx.Database.ExecuteSqlCommand("EXEC MyTest @ParamOut OUTPUT", outParam);

Inspecting outParam.Value will show the integer value returned from the stored proc.

var outParam = new MySqlParameter() { ParameterName = "@ParamOut",
    DbType = DbType.Int32, Direction = ParameterDirection.Output };
ctx.Database.ExecuteSqlCommand("CALL mytest(@ParamOut)", outParam);

This gives the following MySql.Data.MySqlClient.MySqlException:

‘OUT or INOUT argument 1 for routine testdb.mytest is not a variable or NEW pseudo-variable in BEFORE trigger’

This is due to the following query being sent: CALL mytest(NULL)

When it should really be the following SQL: CALL mytest(@ParamOut)

mguinness on Apr 8, 2017