How can I use a MySql User Defined Variable in a .NET MySqlCommand?

Question

I simply want to execute the following Mysql statement

SET @a = 1;SELECT @a; 

with MySql.Data.MySqlClient

[System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")  $password = 'mypassword'  $sql = "SET @a = 1;SELECT @a;"  $server = 'localhost' $port = 3306 $database = 'test' $User = 'root'   $conn=new-object MySql.Data.MySqlClient.MySqlConnection $connectionstring =  "Server=$Server;Port=$port;Database=$DataBase;Uid=$User;Pwd=$Password;allow zero datetime=yes" $conn.ConnectionString = $connectionstring  $conn.Open()   $cmd=new-object MySql.Data.MySqlClient.MySqlCommand($sql,$conn) $ds=New-Object system.Data.DataSet $da=New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd) $da.fill($ds)  $conn.close() $ds.tables[0] 

I get a fatal error.

When I replace $sql by either

$sql = "SELECT DATABASE();" 

or

$sql = "SELECT 1;" 

I get the expected result.

I found this question, but it doesn't solve my problem.

I'm trying to port SQLIse (a part of the SQLPSX project ) to the MySQL version MySQLIse.

I want to process any simple valid mysql statements.

EDIT:

I was trying to run parts of the sakila-schema.sql the mysql demo database install script which runs by something like

mysql> SOURCE C:/temp/sakila-db/sakila-schema.sql;

Answer 1

I found the solution in this blog

I have to add

;Allow User Variables=True 

to the connection string:

$connectionstring = "Server=$Server;Port=$port;Database=$DataBase;Uid=$User;Pwd=$Password;allow zero datetime=yes;Allow User Variables=True" 

works. I tested it with version 6.3.6.0. of MySql.Data.