Active IQ Unified Manager Discussions

WFA function to run mysql query : The solution

sinhaa
13,954 Views

 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.
1 ACCEPTED SOLUTION

sinhaa
13,836 Views

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.

View solution in original post

12 REPLIES 12

sinhaa
13,837 Views

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.

sinhaa
13,663 Views

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.

MaGei
13,396 Views

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

 

MaGei
13,386 Views

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

sinhaa
13,340 Views

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

sinhaa
13,329 Views

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

MaGei
12,904 Views

Hi Sinhaa,

 

thank you very much for your help. I was not available longer time. Therefore i only now checked it.

I found a solution yes it was the way of escaping.

 

Thank you very much for your help and effort.

 

BR

Marcel




 

mirko
11,961 Views

Escaping your query like this is rediculous.

 

Using this method instead

 

def getSetting(name,type)
{
 
 import java.sql.*; 
  
 //Connect to local WFA DB using the built-in credentials
 
 
 String query= 'SELECT * FROM config.settings WHERE name = ? and type = ?';
 
 Connection con=DriverManager.getConnection(
 "jdbc:mysql://localhost:3306/","wfa","Wfa123");
 
 PreparedStatement stmt=con.prepareStatement(query);
 stmt.setString(1, name);
 stmt.setString(2, type);
 ResultSet rs=stmt.executeQuery();
 
 if(rs.next()){
  return (rs.getString('value'));
 
 }
 
}

CAPATEL_NET1984
11,511 Views

I am using EDM pack and trying to use this functionality

 

below query works fine without function:

 

where nasmapping is a tab in the edm excelsheet.

 

 

SELECT
nasmapping.netappcluster,
nasmapping.uniqueindentification
FROM
edm.nasmapping
WHERE
Location= '${Location}'

 

 

when i tried to use same in fucntion getting issues, not able to access property

 

 

function getclusterfromlocation(Location)

{

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

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

String query='SELECT nasmapping.netappcluster, nasmapping.uniqueindentification FROM edm.nasmapping WHERE Location= \'' + Location + '\'' ;

 


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;

 

 

}

 

 

appriciate any help

CAPATEL_NET1984
10,738 Views

attached is a screenshot of excel

mirko
10,738 Views

Don't escape, but use parameters.

Use "?" (questionmark) as placeholder

and use thep prepare statement and setstring methodes.  I assume that if your variable is not a string, there wil also be setinteger, setlong, setdate, ... to be verified

 

def getSetting(name,type)
{
 
 import java.sql.*; 
  
 //Connect to local WFA DB using the built-in credentials
 
 
 String query= 'SELECT * FROM config.settings WHERE name = ? and type = ?';
 
 Connection con=DriverManager.getConnection(
 "jdbc:mysql://localhost:3306/","wfa","Wfa123");
 
 PreparedStatement stmt=con.prepareStatement(query);
 stmt.setString(1, name);
 stmt.setString(2, type);
 ResultSet rs=stmt.executeQuery();
 
 if(rs.next()){
  return (rs.getString('value'));
 
 }
 
}

mirko
10,737 Views

https://docs.oracle.com/javase/7/docs/api/java/sql/PreparedStatement.html 

 

The above url is the official docs for this functionality

Public