How to query database using function in WFA

Sometimes you need to get information from the database (WFA cache) but you don't want it to happen in the userinput, and you don't want to bother using a custom dictionary.

Examples : get the next available lun id, import settings (instead of hard-coding customer information in your workflows).  It also adds a level of re-usability to your workflows.  And better, you can share settings between multiple workflows (dns servers for example, domain information, ...)

Credits : Tim Kleingeld


Note : this is just a sample, you will want to decide yourself what data and how to return it.

def getDatabaseInformation(variable1) 
{

 import java.sql.*;  
  
 //prep the query

 String query= 'SELECT * FROM myscheme.mytable WHERE name = ?';

 //prep the connection 

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


 //prep the statement (to add variables) 
 PreparedStatement stmt=con.prepareStatement(query); 

 //inject variable in placeholder (? questionmark in query)
 stmt.setString(1, name);

 // execute
 ResultSet rs=stmt.executeQuery(); 

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


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

  // do something with your results
  result = (rs.getString('myfield'));

 }

 return result 
}