Question 266293 in Mysql Question posted in
The official MySQL documentation can be found here.

MySqlConnector – Parameter in dynamic string query won't be set

@pname is the parameter set in C# code by:

using (MySqlCommand dynamicQueryCommand = new MySqlCommand(dynamicQueryWithParameter, conn))
{
    dynamicQueryCommand.Parameters.Add("@pName", MySqlDbType.String);
    dynamicQueryCommand.Parameters["@pName"].Value = "John";

    using (MySqlDataReader dynamicQueryReader = dynamicQueryCommand.ExecuteReader())
    {
        string output = "";
        object[] values = new object[dynamicQueryReader.FieldCount];

        while (dynamicQueryReader.Read())
        {
            dynamicQueryReader.GetValues(values);

            output += values[0] + "n";
        }
    }
}

This is the query in dynamicQueryWithParameter:

SET @sql = 'select name from person';

SET @sql = Concat(@sql, ' WHERE name = @pname ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

@pname doesn’t seem to be set.

The output is always that as if @pname would be null.

I would expect the query to look like:

select name from person
where name = 'John'

I tried different format, like:

SET @sql = Concat(@sql, ' WHERE name = '@pname' ');

or

SET @sql = Concat(@sql, ' WHERE name = ''@pname'' ');

But this did not work.

Is that a bug in the MySqlConnector, a syntax error on my side or something different?

Tags:

2

Answers


  1. 0

    Parameters are indeed case sensitive. So modifying your sql to the following fixes the issue.

    SET @sql = 'select name from person';

SET @sql = Concat(@sql, ' WHERE name = @pName ');

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;
    Login or Signup to reply.
  2. 0

    Ignoring the fact that there’s no need to use EXECUTE here, you need USING to bind parameters, using ? for the placeholder in the inner SQL:

    SET @sql = 'select name from person where name = ?';
-- etc
EXECUTE stmt
    USING @pname

    Citation and examples

    However, in this case your SQL should just be:

    select name from person
where name = @pname

    without the manual EXECUTE

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search