Subscribe
Accepted Solution

WFA function to run mysql query : The solution

[ Edited ]

 There have been numerous asks for a WFA funtion to execute mysql queries. Functions have a great advantage that it can execute during a workflow planning and hence result can be seen during a preview. 

 

So here is a sample function :

 

 

WFA -> Designer -> Functions. Add a new fucntion with the below code.

 

function executeMysqlQuery(query)

{

import java.sql.*;  

//Connect to local WFA DB using the built-in credentials

Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/","wfa","Wfa123");

Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery(query);  

//Query is executed, loop over every row and fetch the column.

String s="DATABASE\n----------\n"; 
while(rs.next())
{

s = s + rs.getString('Database') + '\n';

}

return s;
}

Example execution for query 'show databases;'

 

 

function_mysql.png

 

 

Now you can execute any query which the built-in credentials has been granted permission to.

 

 

 

 

This function can also be used to run queries on OCUM mysql DB using the OCUM Database credentials. So you can do resource selection directly from OCUM.

 

sinhaa

 

 

 

 

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

Re: WFA function to run mysql query : The solution

[ Edited ]

I'm seeing some people are having difficulty in using this fuction. I'll provide some help in using it.

 

 

1. Try not to pass the the entire query as parameter to the function. This is not going to be as easy as it was for me in the above example. My query was extremely simple. So pass the variables you want use in your query and then construct your query string.

 

2. Queries have single quotes in them, so you need to escape them with a back-slash.

 

3. You can get columns by respective positions on method getString() and not necessarily using the column name. You can also use getInt() to get Integer columns.

 

 

Example:

 

Suppose your query is:

 

Select cm_storage.vserver.name AS 'VS NAME',
cm_storage.vserver.admin_state from cm_storage.vserver, cm_storage.cluster where cm_storage.cluster.primary_address = '10.20.30.40' AND cm_storage.vserver.name = 'VS001'

So my variables are Cluster Primary Address and Vserver name

 

so my function can look like:

 

function executeMysqlQuery(cluster_ip, vs_name)

{

import java.sql.*;  

//Connect to local WFA DB using the built-in credentials

String query='Select cm_storage.vserver.name AS \'VS NAME\',
cm_storage.vserver.admin_state
from cm_storage.vserver, cm_storage.cluster
where
cm_storage.cluster.primary_address = \'' + cluster_ip + '\'
AND
cm_storage.vserver.name = \'' + vs_name + '\'';


Connection con=DriverManager.getConnection(  
"jdbc:mysql://localhost:3306/","wfa","Wfa123");



Statement stmt=con.createStatement();  
ResultSet rs=stmt.executeQuery(query);  

//Query is executed, loop over every row and fetch the columns.

String s; 
while(rs.next())
{

s = s + rs.getString(1) + ' | ' + rs.getString(2) + '\n';

}

return s;
}

 

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