Subscribe

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

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.

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

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

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

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

Hi,

 

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

 

Thanks,

Akash Shukla

TME
WFA

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

@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

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.

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

@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.

 

WFA_userinput1.png

 

 

 

2. When comment and delta are different

 

WFA_userinput2.png

 

 

 sinhaa

 

 

 

 

 

If this post resolved your issue, help others by selecting ACCEPT AS SOLUTION or adding a KUDO.