WFA function to run mysql query : The solution

[sinhaa]

 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;'

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

[sinhaa · Accepted solution]

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;
}

[sinhaa]

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

[MaGei]

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]

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]

@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

[sinhaa]

@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

[MaGei]

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]

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]

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]

attached is a screenshot of excel

[mirko]

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]

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

The above url is the official docs for this functionality