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

by Occasional Contributor on ‎2013-10-11 09:44 AM

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")

}

Comments
goodrum Former NetApp Employee

Great stuff!  Thanks for sharing. 

Jeremy Goodrum, NetApp

The Pirate

Twitter: @virtpirate

Blog: www.virtpirate.com

Frequent Contributor

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

- Kevin

Extraordinary Contributor

Wow!! Thanks for Sharing Mirko. 5 Stars

mgoddard Former NetApp Employee

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'

$MySQLHost = $env:COMPUTERNAME

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.

Frequent Contributor

Great stuff Mirko.


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

Occasional Contributor

Hi Michael,

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

Mirko

mgoddard Former NetApp Employee

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.

Occasional Contributor

Ok

Thx

Sent from my iPhone

Op 18-okt.-2013 om 02:55 heeft "mgoddard" <xdl-communities@communities.netapp.com<mailto:xdl-communities@communities.netapp.com>> het volgende geschreven:

<https://communities.netapp.com/index.jspa>

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

new comment by mgoddard<https://communities.netapp.com/people/mgoddard> View all comments on this document<https://communities.netapp.com/docs/DOC-30478#comment-19081>

Frequent Contributor

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

Steve

Occasional Contributor

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

Regards,

Mirko Van Colen

Professional Services Consultant

M +32 496 122 285

Occasional Contributor

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.

Regards,

Mirko Van Colen

Professional Services Consultant

M +32 496 122 285

Warning!

This NetApp Community is public and open website that is indexed by search engines such as Google. Participation in the NetApp Community is voluntary. All content posted on the NetApp Community is publicly viewable and available. This includes the rich text editor which is not encrypted for https.

In accordance to 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
  • Copyrighted materials without the permission of the copyright owner

Files and content that do not abide by the Community Terms of Use or Code of Conduct will be removed. Continued non-compliance may result in NetApp Community account restrictions or termination.