bernd_k bernd_k - 1 year ago 79
SQL Question

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

I simply want to execute the following Mysql statement

SET @a = 1;SELECT @a;

with MySql.Data.MySqlClient


$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

$cmd=new-object MySql.Data.MySqlClient.MySqlCommand($sql,$conn)
$ds=New-Object system.Data.DataSet
$da=New-Object MySql.Data.MySqlClient.MySqlDataAdapter($cmd)

I get a fatal error.

When I replace $sql by either



$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.


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

mysql> SOURCE

Answer Source

I found the solution in this bolg

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 of MySql.Data.