Hi all,
I needed to insert / update a record in the WFA MySQL playground database and I didn't find anything online, so I started digging.
First of all you need the MySQL.Data.dll for .NET 2.0. You can easily find this on the web.
I then copied them under ...\wfa\mysql\client\
Here is the code for the command, have fun !
What I expect is :
- the ip of the mysql (defaults to localhost)
- username of mysql (defaults to wfa)
- password of mysql (defaults to Wfa123)
- database name (defaults to playground)
- path of the dll (defaults to C:\Program Files\NetApp\WFA\mysql\client\MySQL.Data.dll)
- table name
- column names : a delimited string of the column names
- column values : a delimited string of the column values you want to add/update
- column types : a delimited string of the column types (string or number) => just to know if quotes are in order
- column keys : a delimited string of the column primary keys
- delimiter : which delimiter to use
param (
# 127.0.0.1 is default ip address
[parameter(Mandatory=$false, HelpMessage="MySql IP address")]
[string]$MySqlServer,
# 'wfa' is default username
[parameter(Mandatory=$false, HelpMessage="MySql Username")]
[string]$Username,
# 'Wfa123' is default password
[parameter(Mandatory=$false, HelpMessage="MySql Password")]
[string]$Password,
# playground is default database
[parameter(Mandatory=$false, HelpMessage="MySql Database")]
[string]$Database,
# C:\Program Files\NetApp\WFA\mysql\client\MySQL.Data.dll is default path
[parameter(Mandatory=$false, HelpMessage="MySql Dll Path")]
[string]$DllPath,
[parameter(Mandatory=$true, HelpMessage="Table Name")]
[string]$Table,
[parameter(Mandatory=$true, HelpMessage="Column Names")]
[string]$Names,
[parameter(Mandatory=$true, HelpMessage="Column Values")]
[string]$Values,
[parameter(Mandatory=$true, HelpMessage="Column Keys")]
[string]$Keys,
# type must be STRING or NUMBER (just to know if we need quotes or not)
[parameter(Mandatory=$true, HelpMessage="Column Types")]
[string]$Types,
[parameter(Mandatory=$true, HelpMessage="Delimiter")]
[string]$Delimiter
)
# set defaults
if(-not $MySqlServer){
$MySqlServer = "127.0.0.1"
}
if(-not $Database){
$Database = "playground"
}
if(-not $DllPath){
$DllPath = "C:\Program Files\NetApp\WFA\mysql\client\MySQL.Data.dll"
}
if(-not $Username){
$Username = "wfa"
}
if(-not $Password){
$Password = "Wfa123"
}
Get-WFALogger -Info -message $("Create Database Connection")
Get-WFALogger -Info -message $("MySqlServer : $MySqlServer")
Get-WFALogger -Info -message $("Database : $Database")
Get-WFALogger -Info -message $("Username : $Username")
Get-WFALogger -Info -message $("DllPath : $DllPath")
# creating connection string
$ConnectionString = "server=$MySqlServer;uid=$UserName;pwd=$Password;database=$Database;"
Get-WFALogger -Info -message $("ConnectionString : $ConnectionString")
# building sql commands
# split the columns and force to array
$arrNames = @($Names.ToLower().split($Delimiter))
$arrValues = @($Values.Replace("'","''").split($Delimiter))
$arrKeys = @($Keys.ToLower().split($Delimiter))
$arrTypes = @($Types.ToLower().split($Delimiter))
$columnLength = $arrNames.length
# check valid input
# - at least one name/value pair
# - same number of name, values & types
# - valid number of keys
if(($columnLength -eq 0) -or ($columnLength -ne $arrValues.length) -or ($columnLength -ne $arrTypes.length) -or ($arrKeys.length -eq 0) -or ($arrKeys.length -gt $columnLength)){
Get-WFALogger -Info -message $("Name/Value pairs are not equal in numbers or Keys are incorrect")
throw "Wrong Name/Value pairs or wrong keys"
}
$items = @()
for($x = 0;$x -lt $columnLength;$x++){
$item = "" | Select name,value,isString,isKey
$item.name = $arrNames[$x]
$item.value = $arrValues[$x]
$item.isString = ($arrTypes[$x] -eq "string")
$item.isKey = ($arrKeys -contains $item.name)
$items += $item
}
# command magic
$nameList = ($items | foreach{$_.name}) -join ","
$valueList = ($items | foreach{if($_.isString){"'$($_.value)'"}else{$_.value}}) -join ","
$whereCriteria = ($items | where{$_.isKey} | foreach{if($_.isString){"$($_.name)='$($_.value)'"}else{"$($_.name)=$($_.value)"}}) -join " AND "
$updatePairs = ($items | where{-not $_.isKey} | foreach{if($_.isString){"$($_.name)='$($_.value)'"}else{"$($_.name)=$($_.value)"}}) -join ", "
# build the commands
$selectCommand = "SELECT count(*) FROM $Table WHERE $whereCriteria"
$insertCommand = "INSERT INTO $Table($nameList) VALUES($valueList)"
$updateCommand = "UPDATE $Table SET $updatePairs WHERE $whereCriteria"
try {
$ErrorActionPreference = "Stop"
# load MySQL driver and create connection
[system.reflection.Assembly]::LoadFile($DllPath) | Out-Null
Get-WFALogger -Info -message $("Loaded MySql DLL")
$connection = New-Object MySql.Data.MySqlClient.MySqlConnection
Get-WFALogger -Info -message $("Creating connection object")
$connection.ConnectionString = $ConnectionString
Get-WFALogger -Info -message $("ConnectionString = $ConnectionString")
$connection.Open()
Get-WFALogger -Info -message $("Database is open")
$comm = New-Object MySql.Data.MySqlClient.MySqlCommand
$comm.Connection = $connection
$comm.CommandText = $selectCommand
$result = $comm.ExecuteReader()
$result.Read()
$exists = ($result[0] -gt 0)
Get-WFALogger -Info -message $("Executed : $selectCommand")
Get-WFALogger -Info -message $("Result : $($result[0])")
$connection.Close()
$connection.Open()
if($exists){
$comm.CommandText = $updateCommand
Get-WFALogger -Info -message $("Record already exists - updating")
}else{
$comm.CommandText = $insertCommand
Get-WFALogger -Info -message $("Record does not exist - inserting")
}
Get-WFALogger -Info -message $("Executing command : $($comm.CommandText)")
$comm.ExecuteNonQuery()
} catch {
Get-WFALogger -Error -message $("Could not run MySQL Query : $($Error[0].Exeception)")
} Finally {
$connection.Close()
Get-WFALogger -Info -message $("Connection Closed")
}
