0

I have an issue with Entity Framework 5.0. I'm working with Silverlight 5 and MySQL 5.6 too.

I need to set an environment MySQL variable before each connexion to the MySQL server.

E.g

SET @my_var = 'test';

Under Mysql I don't have any issues.

The following raises an EntityFrameworkException (syntax error near '@'). 

this.ObjectContext.CreateQuery<object>(" SET @my_var = 'test' ");

OR

this.ObjectContext.CreateQuery<object>(" CALL set_my_var('test') ");

This last method raises a MySQLException saying that a DataReader is already open and need to be closed. 

this.ObjectContext.ExecuteStoreQuery<object>(" CALL set_my_var('test') ", null);

I also tried to set a MySQL system environment (no '@') with the same result every time.

Any help will be much appreciated ! Thank you.

Frank
  • 95
  • 1
  • 11

2 Answers2

2

I tried so many things that I misspelled my variable in my code. So the following finaly worked : ctx.ExecuteStoreCommand("SET @my_var = 'test'");

I decided to leave the instruction in the method Initialize of my domain service. This method is inherited of the LinqToEntitiesDomainService class.

But you need to set Allow User Variables=True in your MySQL connection string (ref : Is it possible to use a MySql User Defined Variable in a .NET MySqlCommand?)

You simply need to use a recent version of the MySQL Connector because older versions use the '@' mark to define SQL parameters so it could conflict with custom variables. Now it uses the '?' mark : http://dev.mysql.com/doc/refman/5.0/es/connector-net-examples-mysqlcommand.html

My library was already up to date (6.6.5).

Thank you for the help !

Community
  • 1
  • 1
Frank
  • 95
  • 1
  • 11
0

Since your statement is not a query (i.e. does not return any result) you should use ExecuteStoreCommand. Something like this should work:

ctx.ExecuteStoreCommand("SET @my_var = 'test'")
Pawel
  • 31,342
  • 4
  • 73
  • 104
  • Hello. Thank you for your answer. I had tried this method too but MySQL raises an Exception : `@my_var must be defined`. I can't figure out how to escape the '@' character. I've also tried using a stored procedure setting this variable. No Exception. But it seems that the variable is never set. Should I open manually the connexion to the MySQL server in my context ? I've read in this article that Entity Framework opens and closes connexions as many times as necessary : http://msdn.microsoft.com/en-us/library/vstudio/bb896325%28v=vs.100%29.aspx – Frank Apr 03 '13 at 08:01
  • Do you think that executing this method Entity Framework opens a connexion just for this command ? I tried to do so. I added the connexion instruction into the Initialize method (overriden). I tried to put this instruction into the "Insert" method I'm using but the variable is not. No changes for the moment. – Frank Apr 03 '13 at 08:02