CODEWITHSUNDEEP
c#.netmysql
Denis Palnitsky
Denis Palnitsky

Reputation: 18387

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

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

Upvotes: 1

Views: 868

Answers (3)

Denis Palnitsky
Denis Palnitsky

Reputation: 18387

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.

Upvotes: 1

Guffa
Guffa

Reputation: 700650

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.

Upvotes: 0

Jürgen Steinblock
Jürgen Steinblock

Reputation: 31743

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.

Upvotes: 1

Related Questions