Non-parameter SQL Variables with MySql Connector/Net and Dapper?

Question

The code below generates the following error:

Parameter '@ID' must be defined.

Am I doing something wrong or is not it possible to use variables in the SQL query with MySQL which aren't Dapper parameters?

In this example, @Slug is a Dapper parameter, but @ID is not. I'm using MySQL so I don't need to DEFINE @ID - it get's defined in the first use.

var sql = @"SELECT @ID := id, slug, Title, Text FROM posts WHERE slug = @Slug;  SELECT * FROM comments where postid = @ID;";
using (var connection = GetOpenConnection())
{
    var posts = connection.QueryMultiple(sql, new { Slug = slug })
        .Map<Post, Comment, int>
        (
        Post => Post.ID,
        Comment => Comment.ID,
        (post, comments) => { post.Comments = comments; }
        );
    return posts.FirstOrDefault();
}

Mike Gleason · Accepted Answer · 2012-02-09 02:18:30Z

23

It turns out "MySql Connector/Net" generates the error.

In order to use Non-parameter SQL Variables with MySql Connector/Net, you have to add the following option to your connection string:

"Allow User Variables=True"

See: http://blog.tjitjing.com/index.php/2009/05/mysqldatamysqlclientmysqlexception-parameter-id-must-be-defined.html

edited Feb 9, 2012 at 2:18

answered Feb 8, 2012 at 17:52

Mike Gleason

1,46914 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Marc Gravell · Accepted Answer · 2012-02-08 08:11:23Z

2

I don't think this is a dapper issue; dapper just takes the SQL you offer, and adds any members from the "args" object that it obviously see in the SQL.

The way to investigate this is to try running the same with DbCommand directly - my guess is that it will fail identical. There is going to be some SQL trick to make it work, but that is between you and MySQL. All dapper is doing is:

creating a command with your sql
defining a parameter called "Slug" and setting the value
invoking the sql and parsing the results

it doesn't touch "ID", and it is correct that it doesn't do so.

answered Feb 8, 2012 at 8:11

Marc Gravell

1.1m273 gold badges2.6k silver badges3k bronze badges

1 Comment

Mike Gleason Over a year ago

You're correct - I tested it using DbCommand and got the same error.

Joel Coehoorn · Accepted Answer · 2012-02-08 02:34:14Z

0

You need to declare the ID variable in your sql code:

var sql = @"DECLARE @ID Int; 
            SELECT @ID := id, slug, Title, Text FROM posts WHERE slug = @Slug;
            SELECT * FROM comments where postid = @ID;";

answered Feb 8, 2012 at 2:34

Joel Coehoorn

418k114 gold badges582 silver badges820 bronze badges

1 Comment

Mike Gleason Over a year ago

I'm actually using MySQL which which I believe automatically defines its type when it is first assigned a value. If I cut at paste the SQL query above into MySQL Workbench and run it, it runs just fine. So, I don't think that's were the error is coming from.

Collectives™ on Stack Overflow

Non-parameter SQL Variables with MySql Connector/Net and Dapper?

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related