How to fetch playground DB data in a command

[SERVICEAMDS]

Hello,

I want to create workflow where user enters a business unit name and command creates an export policy on a cluster whose data is stored in the playground database

I created a playground table that holds data abount business units and corresponding clusters and their IP addresses:

create table bu (

  id int(11) not nul auto_increment primary key,

  bu varchar(255),

  cluster_name varchar(255),

  cluster_ip varchar(255),

  index(bu)

);

I managed to create playground tables and successfully created filters and finders based on that.

But I cannot find out how to use information from SQL directly in a command.

I wanted to create a modification of "Create Export policy" command where user enters a business unit name and command creates an export policy on a cluster whose data is stored in the playground database, like this:

param (
  [parameter(Mandatory=$true, HelpMessage="Business Unit Name")]
  [string]$BU
)

... I need to fetch here data from playground database, like this:
"select cluster_name from playground.bu_data where bu = '$BU"'
# connect to controller
Connect-WfaCluster $Cluster  <====== how can I get this $Cluster?

Any hint would be greatly appreciated.

[abhit]

There is a thread posted by mirko in communities on how to

run sql queries via command.

You can search in the same in communities.

I will try to send the link tomorrow if you can't find or if nobody else responds.

Abhi

[shailaja]

The link referred to by Abhi is:

https://communities.netapp.com/docs/DOC-30478

It has good examples on how to fetch data from playground DB

Thanks,

Shailaja

[abhit]

There is a thread posted by mirko in communities on how to

run sql queries via command.

You can search in the same in communities.

I will try to send the link tomorrow if you can't find or if nobody else responds.

Abhi

[sinhaa]

Two ways to do it. You can pick what suits you better. Both have their advantages.

1. You can declare $Cluster as another Input Parameter to your command. Something like below

Param(

  ....

  ......

  [Parameter(Mandatory = $true,  HelpMessage="Cluster")]

  [string]$Cluster

  )

Now in your workflow, you for this parameter declare variable as $Cluster_name and go to Setup > User-Inputs tab. Double-click to edit this variable input from string to type: Query. Now write your query and you get the Cluster.

2. You can make use of the mysql .NET connector and the attached powershell module to run queries directly from your command.

• Download and install on your WFA server  the mysql .NET Windows (x86, 32-bit), MSI Installer . http://dev.mysql.com/downloads/connector/net/

You can use the below code snippet in your command to run mySQL queries directly from your command.

### begin snip ###

$ConnectionString = "server=localhost;port=3306;uid=wfa;pwd=Wfa123"

#Your query

$Query = "select * from playground.bu"

Try {

  [void][System.Reflection.Assembly]::LoadWithPartialName("MySql.Data")

  $Connection = New-Object MySql.Data.MySqlClient.MySqlConnection

  $Connection.ConnectionString = $ConnectionString

  $Connection.Open()

  $Command = New-Object MySql.Data.MySqlClient.MySqlCommand($Query, $Connection)

  $DataAdapter = New-Object MySql.Data.MySqlClient.MySqlDataAdapter($Command)

  $DataSet = New-Object System.Data.DataSet

  $DataAdapter.Fill($dataSet, "data")

# list of row objects

  $query_result = $DataSet.Tables[0]

  }

Catch {

  Write-Host "ERROR : Unable to run query : $query `n$Error[0]"

}

Finally {

  $Connection.Close()

  }

## end snippet.

The 2nd option has some big advantages over the 1st one.

[ktim]

Here is another approach which I use since it does not require installation of additional software (I believe that the .NET mysql modules will be included with future releases of WFA so the approach above will be appropriate then)...:

### begin snip ###

# Default credentials for playground database

$MySQLAdminUserName = 'wfa'

$MySQLAdminPassword = 'Wfa123'

$MySQLDatabase = 'playground'

$MySQLHost = "localhost"

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 = "$($env:ProgramFiles)\NetApp\WFA\mysql\bin\mysql.exe"

}

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

$cmd1 = "DELETE FROM playground.myTable " +

    "WHERE TargetCluster='$TargetCluster' AND "+

    "TargetVserver='$TargetVserver' AND "+

    "TargetVolume='$TargetVolume'"

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

# Attempt delete via mysql.exe

$output = & "$mysqlcmd" "--user=$MySQLAdminUserName" "--host=$MySQLHost" "--password=$MySQLAdminPassword" "-B" "-f" "-e$cmd1" "$MySQLDatabase" 2>&1

$ret = $?

if ($ret -eq $false) {

          throw $("Operation failed: $output")

}