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.

Re: WFA function to run mysql query : The solution

For OCUM5.X (DFM) 7-mode users, you can use the below function. WFA bundles, Sybase JConnector 3 device driver.

 

function executeSybaseQuery(query)

{

import java.sql.*;
import com.sybase.jdbcx.*;

SybDriver sybDriver = (SybDriver) Class.forName("com.sybase.jdbc3.jdbc.SybDriver").newInstance();

Connection con = DriverManager.getConnection("jdbc:sybase:Tds:10.228.154.88:2638/monitordb", "sinhaa", "sinhaa");
Statement stmt = con.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM dfm.disks");
rs.next();
a = rs.getString(1);

return a;
}

 

These functions are very useful to get data which is NOT cached by WFA acquistion.

 

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 ]

First of all Thank you for your post.

I want to create a function with a sql query to get the Snapshot Name back from a specific date which is a user input. 

 

My SQL query against the WFA Db is working:

 

SELECT
snapshot.name AS 'name'
FROM
cm_storage_smsv.snapshot
WHERE
snapshot.cluster = '${ClusterName}'+'domainname.xxx'
AND snapshot.vserver = '${VserverName}'
AND snapshot.volume = '${Volumes}'
AND snapshot.timestamp LIKE '${clone_date}%'
ORDER BY
timestamp DESC limit 1

 

But it is not work as function in WFA

 

 

Any Idea what is wrong here, i'm not good in these thing, maybe it is very easy for you

Error Message:


[Error: failed to access property: executeMysqlQuery(xxxx-baas01, xx_01849_gold_01, xx_01849_gold_01_root, 2017-06-07): [Error: failed to access property: xxxx: [Error: could not access property: xxx]
[Near : {... Unknown ....}]

 

Your first example with the main function and input 'show databases'; is working fine.

 

The error was because of wrong call without '' . 

 

 

Thank you in advance.

Marcel

 

Re: WFA function to run mysql query : The solution

[ Edited ]

Meanwhile i Got no errors but also no result back:

funtion:

--------------------------------------

function executeMysqlQuery(clu_name, vs_name, vol_name, clone_date)

{

import java.sql.*;

String query='Select snapshot.name AS \'name\'
from cm_storage_smsv.snapshot,cm_storage.cluster
where
cluster.primary_address = snapshot.cluster
AND
snapshot.cluster LIKE \'' + clu_name + '\'
AND
snapshot.vserver = \'' + vs_name + '\'
AND
snapshot.volume = \'' + vol_name + '\'
AND
snapshot.timestamp = \'' + clone_date + '\'';


//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; 
while(rs.next())
{

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

}

return s;
}

 

 

-------------------

 

Call the function with -> executeMysqlQuery('CLuster', 'VServer', 'Volume', 'Snapshotdate') and then I got when i click Test Function only an empty  Result:

 

 

 

 

Any hint or idea what is wrong with my function.

Thanks

 

Marcel

Re: WFA function to run mysql query : The solution

@MaGei

 

Marcel,

 

It will have to work, just need to figure out the right escaping the quotes etc. I'm trying your query, will update you the fix.

 

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

@MaGei

 

To debug this you have a return statement after creating your query string which retruns the query itself.

1. 

 

String query='Select snapshot.name AS \'name\'
from cm_storage_smsv.snapshot,cm_storage.cluster
where
cluster.primary_address = snapshot.cluster
AND
snapshot.cluster LIKE \'' + clu_name + '\'
AND
snapshot.vserver = \'' + vs_name + '\'
AND
snapshot.volume = \'' + vol_name + '\'
AND
snapshot.timestamp = \'' + clone_date + '\'';

return query;

This shall give you your query as how it will appear for execution. Try this final query on your SQL client and see you are getting the right results and not empty rows or any error.

 

2. Once staisfied that query is fine, remove the return query from above and Make 

String s=''; 

Else I see its will add a word null in the beginning of the first row. I've updated my original post.

 

 

3. Since you are returning only 1 column, you can do

 

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

I'm adding the rows as one string because, one string is all I can return. Breaking it back to rows etc, can be done later.

 

 

Try this and let me know if this help you resolve it. 

 

sinhaa

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