Mysql Connector Set Variable

MySql is a nice database engine, C# is nice programming language. However when you put them together you get something not nice at all. At least from a viewpoint of someone like me who is spoiled by nice, clean and seamless database integration Linq for SQL provides for MSSQL. Unfortunately when you put together MSSQL and PHP you also get an ugly thing that doesn’t quite work.

One of the main problems with integrating MySQL with .NET languages is complete lack of documentation for MySQL Connector. CHM file that comes bundled with it is a joke – it merely lists class members and has a couple of stub articles. There are some sample pieces of code but after that you are on your own.

Just recently I ran into a problem of not being able to SQL variable from query.  If you write something like this:

using (MySqlCommand cmd = this.mySql.CreateCommand())
cmd.CommandText = “SET @today=DATE(NOW())”;
return cmd.ExecuteNonQuery();

you’ll get “Parameter ‘today’ must be defined.” exception. I searched the internet and only thing I could find was this message from mysql .net maillist with no replies. Typical shortcoming of open-source of nobody wanting to do the dirty work of documenting their code properly is somewhat offset by the fact that you can poke around the code yourself. So I did.

Solution is there but it’s anything but obvoius – append Allow User Variables=true; to your MySQL connection string. This will cause MySql connector to skip checking parameters mentioned in the query for existence. There is a downside to this solution – you’ll not be able to easily catch bugs in your code caused by mistyped query parameter names.

Hopefully at some point MySql connector developers will realize that passings parameters into query and settings MySQL connection variables are two different pieces of functionality and each should have it’s own independent syntax.

Join the Conversation


  1. You saved my day =O)
    I think this has changed from MySQL Connector prior to 6+ because before upgrading I could work with variables without specifying that parameter in the connectionstring.

Leave a comment

Your email address will not be published. Required fields are marked *