Thursday, March 17, 2011

SQL Server PowerShell Extensions and MySQL

For quite a while now I have been looking for an easy way to use MySQL databases with PowerShell. Everything I had read and found about it earlier made it seem like quite a hassle. All of that seems to have changed for me today when I found the latest release of the SQLPSX included a mysql module as well as the SQL Server and Oracle modules they had previously had.

Well, I dug right in and figured out what was necessary to get this running. With a little assistance from get-help and some previous knowledge of I was able to get a sample script up and running. Note that beyond installing SQLPSX, you will also need to install the MySQL Connector/Net to provide the .NET bindings that this module needs.

Below you will see some sample PowerShell code that I got working. It just simply connects to the server and queries the user table, and spitting it out. Of course this is just a proof of concept, you can see

# simple script to test the new mysql module in sqlpsx
$server = 'servername'
$user = 'dbuser'
$pass = 'dbpass'
$database = 'databasename'

Import-Module mysqllib

$conn = New-MySQLConnection -server $server -user $user -password $pass -database $database

$res = Invoke-MySQLQuery "select * from user" -conn $conn

foreach ($line in $res) {
Write-Host $line.User
Of course this is just a proof of concept, but you can see the simplicity of how this can be used. Hopefully in the future, I will have time to show some greater use of this new found tech.