Named parameters order has sense for MySql .Net data provider?

Question

I found that in my project order of named parameter has sense.

I call this procedure

CREATE PROCEDURE `test`.`TestProc` (
in myText varchar (5),
in myText2 varchar (100)
)
BEGIN

END

If I add parameters like this:

    command.CommandText = "testProc";
    command.CommandType = System.Data.CommandType.StoredProcedure;
    command.Parameters.AddWithValue("myText2", "aaaaaaaaaaaaaaaaa");
    command.Parameters.AddWithValue("myText", "bbbb");

I get "Data too long" exception. Also I can't reproduce this in test project.

Any thoughts?

---

I found the cause. Looks like bug in MySql .Nat Data Provider. To reproduce the bug you need to call this not under the root user.

Conclusion: MySql.Data version 6.2.3.0. Order of named parameter has sense If you call procedure with non root db account

Answer 1

The name of the parameters don't have to match the columns but the placeholders:

INSERT INTO testproc (mytext, mytext2) VALUES (?mytext, ?mytext2)

I suppose if you use unnamed parameters:

INSERT INTO testproc (mytext, mytext2) VALUES (?, ?)

the order does matter.

I always used the former option but with:

command.Parameters.Add("?mytext2", "aaaaaaaaaaaaaaaaa")
command.Parameters.Add("?mytext", "bbbb")

Don't know if it makes any difference to AddWithValue()

Update: Just noticed that your parameters are missing a questionmark. That will be the problem. Add a questionmark to both, your query and your parameters.

Answer 2

The MySQL driver doesn't support named parameters. The name of the parameters are ignored, and they are applied in the order that you put them in the parameter collection.

Answer 3

Answer that I get from MySql bugtracker:

Reggie Burnett

I don't believe this is a bug. I believe your test user doesn't have the privs to see the body of the proc. Please login to MySQL using the commandline client and the test user account. Do a "show create procedure" on your proc. If the body is null you need to use the "use function bodies=false" option and pass in the parameters in the right order.