Access WFA database from command fields using MVEL or access WFA database from a WFA Function

[Ninjas]

Is there an easy way to access the WFA database using MVEL for values not part of a workflow. Essentially I want to be able to run a mysql query as a function.

[mbeattie]

Hi,

I'm not aware of any method to achieve this directly using MVEL.

As an alternative it's possible to use the "Invoke-MySqlQuery" function with a custom WFA command. EG:

#'------------------------------------------------------------------------------
#'Create a query to select the least used aggregate in the resource pool.
#'------------------------------------------------------------------------------
[String]$query = "SELECT id AS 'aggregate_id', node_id, name AS 'aggregate_name', used_size_mb FROM cm_storage.aggregate WHERE id IN (SELECT aggregate_id FROM cm_storage.resource_pool_member WHERE resource_pool_id = (SELECT id FROM cm_storage.resource_pool WHERE name = '$ResourcePoolName')) ORDER BY used_size_mb ASC LIMIT 1;"
#'------------------------------------------------------------------------------
#'Query the least used aggregate in the resource pool.
#'------------------------------------------------------------------------------
Try{
   [Array]$results = Invoke-MySqlQuery -Query $query -ErrorAction Stop
   Get-WFALogger -Info -Message "Invoked SQL Query: ""$query"""
}Catch{
   Get-WFALogger -Error -Message $("Failed invoking SQL query: ""$query"". Error " + $_.Exception.Message)
   Throw "Failed invoking SQL query ""$query"""
}
#'------------------------------------------------------------------------------
#'Assign recordset variables.
#'------------------------------------------------------------------------------
ForEach($result In $results){
   [Int]$aggregateID      = $result.aggregate_id
   [Int]$nodeID           = $result.node_id
   [String]$aggregateName = $result.aggregate_name
   [Long]$usedSizeMB      = $result.used_size_mb
}

You can then assign the value from the recordset as a return parameter using the "Add-WfaWorkflowParameter" function

Add-WfaWorkflowParameter -Name "AggregateName" -Value $aggregateName -AddAsReturnParameter $True

The return parameters will be available in other commands in your workflow using the "Get-WfaWorkflowParameter" function. EG

[String]$aggregateName = Get-WfaWorkflowParameter -Name "AggregateName"

Hope that helps

/matt

[AkashShukla]

Hi,

Could you help us know the exact reason for this requirement.

Thanks,

Akash Shukla

TME
WFA

[sinhaa]

@AkashShukla

MVEL function gives big advatage over getting DB data during command execution. With MVEL fuction I can the DB data during planning/Previw time. The Poweshell function can only get the DB data during execution, so I can't get to see which row will get selected due to SQL query execution during preview.

This is a BIG advangate of MVEL fuction.

sinhaa

[sinhaa]

@Ninjas

Do you really need a function? Whatever a MVEL function to query the WFA DB can get you, you can get it from a User-Input of type Query. Anything and everything.

Some WFA designer skills maybe needed to solve specfic cases, but it can all be done.

Here attaching a sample workflow. You would need WFA4.0 for importing it.

My extremely simple use case is to compare the "comment" provided with a WFA scheme name selected. If they both are same , my command executes, else its disabled. So I've used the "Advanced" tab in my workflow.

1. When comment and Delta are same.

2. When comment and delta are different

 sinhaa