Use MySQL console through a PowerShell script

Question

I have to write a PowerShell script that needs to use the mysql.exe console and execute some queries. I started it:

$mysqlC = 'C:\Users\I16A1\Downloads\mysql\bin\mysql.exe'
$arg1 = '-u asset -ptest testasset'
& $mysqlC $arg1

show databases;

As you can see, after setting the console placement and the arguments of connection, I use the call operator (&) to run the MySQL console.

Everything is fine, MySQL console is running but the lines after the call operator are not running, like show databases;.

The thing is I want my PowerShell script to do everything on its own.

Someone know a way to do it? Maybe differently?

i'm pretty sure you would have to call mysql client everytime if you want powershell to do everything. I mean: & $mysqlC $arg1 $command1 ; & $mysqlC $arg1 $command2 ; & $mysqlC $arg1 $command3 etc — 4c74356b41
– 4c74356b41, Commented Oct 21, 2016 at 12:39
ok I see, but how do you write the command? I'm trying it right now, can't really find a way. — Alexandre Brba
– Alexandre Brba, Commented Oct 21, 2016 at 13:03
cyberciti.biz/faq/run-sql-query-directly-on-the-command-line ? — 4c74356b41
– 4c74356b41, Commented Oct 21, 2016 at 16:37

Ansgar Wiechers · Accepted Answer · 2016-10-21 17:22:26Z

What you're trying to do won't work, because your code won't get to the show databases; until you exit from mysql.exe. The usual way to use mysql.exe from a script is to run the executable with each query:

$db   = 'testasset'
$user = 'asset'
$pass = 'test'

$mysql  = 'C:\Users\I16A1\Downloads\mysql\bin\mysql.exe'
$params = '-u', $user, '-p', $pass, $db

& $mysql @params -e 'SHOW DATABASES'
& $mysql @params -e '...'
...

Use splatting for providing the common parameters.

Normally you'd also add the parameters -B (--batch, non-interactive execution, no fancy output formatting) and -N (--skip-column-names, don't show column titles in output) to get more "digestible" output like this:

information_schema
mysql
performance_schema
test

instead of getting default output like this, that would require parsing the data out of the table for further processing:

+--------------------+
| Database           |
+--------------------+
| information_schema |
| mysql              |
| performance_schema |
| test               |
+--------------------+

With that said, a much better approach than working with the commandline client would be using the .Net Connector, e.g. like this:

$server = 'localhost'
$db     = 'testasset'
$user   = 'asset'
$pass   = 'test'

$cs = "server=$server;user id=$user;password=$pass;database=$db;pooling=false"

[void][Reflection.Assembly]::LoadWithPartialName('MySQL.Data')

$cn = New-Object MySql.Data.MySqlClient.MySqlConnection
$cn.ConnectionString = $cs
$cn.Open()

$cmd= New-Object MySql.Data.MySqlClient.MySqlCommand
$cmd.Connection  = $cn
$cmd.CommandText = 'SHOW DATABASES'
$reader = $cmd.ExecuteReader()

$tbl = New-Object Data.DataTable
$tbl.Load($reader)
$reader.Close()
$cn.Close()

$tbl | Format-Table -AutoSize

That way the output you get will be actual objects instead of strings.

So I tried the 2 solutions and it works well. It's true that I prefere the .NET Connector, you are less limited.

Collectives™ on Stack Overflow

Use MySQL console through a PowerShell script

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related