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;

does "SET @a = 1;SELECT @a;" work in standard MySql tool? In TSQL you need to declare the variable before you use it, "DECLARE @a int;SET @a = 1;SELECT @a;" — Adam Straughan
– Adam Straughan, Commented Apr 2, 2011 at 17:24
sakila-schema.sql starts with: SET @OLD_UNIQUE_CHECKS=@@UNIQUE_CHECKS, UNIQUE_CHECKS=0; — bernd_k
– bernd_k, Commented Apr 2, 2011 at 17:35

justinvdk · Accepted Answer · 2019-12-17 14:04:31Z

63

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.

edited Dec 17, 2019 at 14:04

justinvdk

1722 silver badges11 bronze badges

answered Apr 3, 2011 at 15:41

bernd_k

12.1k7 gold badges49 silver badges64 bronze badges

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

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related