Active IQ Unified Manager Articles and Resources

WFA command to insert/update a record in the Playground database of MySQL


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 (

   # is default ip address

  [parameter(Mandatory=$false, HelpMessage="MySql IP address")]


  # 'wfa' is default username

  [parameter(Mandatory=$false, HelpMessage="MySql Username")]



  # 'Wfa123' is default password

  [parameter(Mandatory=$false, HelpMessage="MySql Password")]


  # playground is default database

  [parameter(Mandatory=$false, HelpMessage="MySql Database")]


  # C:\Program Files\NetApp\WFA\mysql\client\MySQL.Data.dll is default path

  [parameter(Mandatory=$false, HelpMessage="MySql Dll Path")]


  [parameter(Mandatory=$true, HelpMessage="Table Name")]


  [parameter(Mandatory=$true, HelpMessage="Column Names")]


  [parameter(Mandatory=$true, HelpMessage="Column Values")]


  [parameter(Mandatory=$true, HelpMessage="Column Keys")]


  # type must be STRING or NUMBER (just to know if we need quotes or not)

  [parameter(Mandatory=$true, HelpMessage="Column Types")]


  [parameter(Mandatory=$true, HelpMessage="Delimiter")]



# set defaults

if(-not $MySqlServer){

    $MySqlServer = ""


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

    $ = $arrNames[$x]

    $item.value = $arrValues[$x]

    $item.isString = ($arrTypes[$x] -eq "string")

    $item.isKey = ($arrKeys -contains $

    $items += $item


# command magic

$nameList = ($items | foreach{$}) -join ","

$valueList = ($items | foreach{if($_.isString){"'$($_.value)'"}else{$_.value}}) -join ","

$whereCriteria = ($items | where{$_.isKey} | foreach{if($_.isString){"$($'$($_.value)'"}else{"$($$($_.value)"}}) -join " AND "

$updatePairs = ($items | where{-not $_.isKey} | foreach{if($_.isString){"$($'$($_.value)'"}else{"$($$($_.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")


    Get-WFALogger -Info -message $("Database is open")

    $comm = New-Object MySql.Data.MySqlClient.MySqlCommand

    $comm.Connection = $connection

    $comm.CommandText = $selectCommand

    $result = $comm.ExecuteReader()


    $exists = ($result[0] -gt 0)

    Get-WFALogger -Info -message $("Executed : $selectCommand")

    Get-WFALogger -Info -message $("Result : $($result[0])")





        $comm.CommandText = $updateCommand

        Get-WFALogger -Info -message $("Record already exists - updating")


        $comm.CommandText = $insertCommand

        Get-WFALogger -Info -message $("Record does not exist - inserting")


    Get-WFALogger -Info -message $("Executing command : $($comm.CommandText)")



} catch {

    Get-WFALogger -Error -message $("Could not run MySQL Query : $($Error[0].Exeception)")

} Finally {


    Get-WFALogger -Info -message $("Connection Closed")


Please Note:

All content posted on the NetApp Community is publicly searchable and viewable. Participation in the NetApp Community is voluntary.

In accordance with our Code of Conduct and Community Terms of Use, DO NOT post or attach the following:

  • Software files (compressed or uncompressed)
  • Files that require an End User License Agreement (EULA)
  • Confidential information
  • Personal data you do not want publicly available
  • Another’s personally identifiable information (PII)
  • Copyrighted materials without the permission of the copyright owner

Continued non-compliance may result in NetApp Community account restrictions or termination.


Great stuff!  Thanks for sharing. 

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate



What Jeremy said.  Truly great stuff, and thanks very much for sharing!!!

- Kevin


Wow!! Thanks for Sharing Mirko. 5 Stars


Hey Mirko,

Nice work! I had the same problem a while back, and used the in-built mysql command to run some SQL directly. I didn't want dependencies on maintaining binaries as the customer had 4+ WFA installations between prod/test/dev. Pasted below in case it's useful to anyone. This example deletes a row in the table.

$key_name = "key to delete"

$key_value = "value of key"

$key_table = "table-name"

# Default credentials for playground database

$MySQLAdminUserName = 'wfa'

$MySQLAdminPassword = 'Wfa123'

$MySQLDatabase = 'playground'


Get-WFALogger -Info -message $("Obtaining MySQL location.")

# Attempt to find mysql.exe based on the wfa tmp working path, or default location if not found

$pwd = Get-Location

$mysqlcmd = $pwd.Path+"\..\..\..\..\..\mysql\bin\mysql.exe"

$mysqlcmd_found = Test-Path $mysqlcmd

if ($mysqlcmd_found -ne $true)


  $mysqlcmd = "C:\Program Files\NetApp\WFA\mysql\bin\mysql.exe"


Get-WFALogger -Info -message $("Preparing Command")

$cmd1 = "DELETE FROM playground." + $key_table + " WHERE " + $key_name + "='" + $key_value + "'"

Get-WFALogger -Info -message $("Executing SQL: " + $cmd1)

# Attempt insert via mysql.exe

& "$mysqlcmd" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "--password=$MySQLAdminPassword" "-B" "-e$cmd1" "$MySQLDatabase"

$ret = $?

Get-WFALogger -Info -message $("Return code: " + $ret )

- Michael.


Great stuff Mirko.

Mic G. - Thanks for sharing your view and code !!!


Hi Michael,

Can you use the mysql.exe also for queries ?  or this non-query only ?



Hey Mirko,

Yep, mysqld.exe is the actual database server, mysql.exe is the client and can be used for running any queries locally. The output of queries is in human-readable format, not programmatic so is harder to work with, but it's good for inserts and deletes.

- Michael.




Sent from my iPhone

Op 18-okt.-2013 om 02:55 heeft "mgoddard" <<>> het volgende geschreven:


WFA command to insert/update a record in the Playground database of MySQL

new comment by mgoddard<> View all comments on this document<>


Hey Mirko, this is a nice feature.  Any reason why it doesnt work with the other DBs?  For example, I changed the command to "Select name from cm_storage.cluster" and it says cm_storage is not a valid database



Because you cannot manipulate the native databases, only datasources can insert in those.


Mirko Van Colen

Professional Services Consultant

M +32 496 122 285


Sorry answered too quickly.

You’re doing a select...

That should work. I’m always using a mysql gui where I can test my queries.


Mirko Van Colen

Professional Services Consultant

M +32 496 122 285