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
}