PowerShell MYSQL Query Workflow for Windows Azure SMA

workflow db_query-mysql 
{
        param (
        [Parameter(Mandatory=$true)]
            [string] $Query
    )
    $con = Get-AutomationConnection -Name 'Connection'
    $securepassword = ConvertTo-SecureString -AsPlainText -String $con.Password -Force
    $cred = New-Object -TypeName System.Management.Automation.PSCredential -ArgumentList $con.Username, $securepassword
    
    $RetData = InlineScript{
        
        ##  Connection Variables.
        $server= "MYSQLDB"
        $port = "3306"
        $username= "user"
        $password= "Passwd"
        $database= "database"


        [void][system.reflection.Assembly]::LoadFrom("C:\Program Files (x86)\MySQL\MySQL Connector Net 6.5.4\Assemblies\v2.0\mySQL.Data.dll")

        function global:Set-SqlConnection ( $server = $(Read-Host "SQL Server Name"), $username = $(Read-Host "Username"), $password = $(Read-Host "Password"), $database = $(Read-Host "Default Database") ) {
            $SqlConnection.ConnectionString = "server=$server;user id=$username;password=$password;database=$database;port=$port;"
        }

        function global:Get-SqlDataTable( $Query = $(if (-not ($Query -gt $null)) {Read-Host "Query to run"}) ) {
            if (-not ($SqlConnection.State -like "Open")) { $SqlConnection.Open() }
            $SqlCmd = New-Object MySql.Data.MySqlClient.MySqlCommand $Query, $SqlConnection
            $SqlAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter
            $SqlAdapter.SelectCommand = $SqlCmd
            $DataSet = New-Object System.Data.DataSet
            $SqlAdapter.Fill($DataSet) | Out-Null
            $SqlConnection.Close()
            return $DataSet.Tables[0]
        }

        Set-Variable SqlConnection (New-Object MySql.Data.MySqlClient.MySqlConnection) -Scope Global -Option AllScope -Description "Personal variable for Sql Query functions"
        Set-SqlConnection $server $username $password $database

        $output = Get-SqlDataTable "$Using:Query"
        $output       
    } -PSComputerName $con.ComputerName -PSCredential $cred -PSConfigurationName Microsoft.PowerShell32

    $RetData  
}

Jeremy Tirrell

Read more posts by this author.