mg1978
Enthusiast
Enthusiast

Action to get data from SQL DB in Workflow Presentation

Jump to solution

Hi All,

I try to create an action, where I read data from a SQL DB and presented this one as (Predefined list of elements) in the presentation of my workflow.

I have a DB table with Names and I will present this names as a selectec list on my web view in my workflow.

I have a workflow where my customer need to select a costcenter approver and I have all costcenter approvers in my SQLDB.

I know that I can create "Predefined answers" with this names, but than I need to manage the DB tables and the workflow and I will only manage the DB.

Have anyone this already done ?

0 Kudos
1 Solution

Accepted Solutions
cdecanini_
VMware Employee
VMware Employee

At this point you should have a list of the columns / rows when you use the plug-in object as input. If you must have a drop down then you need either to create an action using this object as input and returning an array of string and use it in a string input field with a predefined list property.

I am not sure if the SQL plug-in has the option to show the values as drop down without doing such a transformation in the action.

You can try it with downloading it from here: http://www.vmware.com/products/datacenter-virtualization/vcenter-orchestrator/plugins.html

It may end up to be a lot simpler to maintain. I mentioned the custom DB plug-in because this is what I have used previously and had unfortunaltely no chance to work yet with the SQL plug-in.

Christophe.

If my answer resolved or helped you, please mark it as Correct or Helpful to award points. Thank you! Visit http://www.vcoteam.info & http://blogs.vmware.com/orchestrator for vCenter Orchestrator tips and tutorials - @vCOTeam on Twitter

View solution in original post

0 Kudos
10 Replies
cdecanini_
VMware Employee
VMware Employee

The way to do it is definitely with using action. Create your action and define a return type of array of string. Include the code to get to the records you need. Test this action at first in a workflow and once you manage to get an array of strings as output, use the action for your predefined list of elements / predefined answers.

Christophe.

If my answer resolved or helped you, please mark it as Correct or Helpful to award points. Thank you! Visit http://www.vcoteam.info & http://blogs.vmware.com/orchestrator for vCenter Orchestrator tips and tutorials - @vCOTeam on Twitter
0 Kudos
mg1978
Enthusiast
Enthusiast

I have the following action, but it doesn´t work.

var main = new JDBCConnection();
var con;
var sqlStatement;

sqlStatement = "select APPROVER_FULL_NAME from COST_CENTER ";

try  {
    con = main.getConnection( "jdbc:jtds:sqlserver://*****************:2594/REQUEST_DB;instance=PROD", DBuser, DBpw );
    System.log( "Connection to database successful" );
    var stat = con.createStatement();
    var rs = stat.executeQuery( sqlStatement );
    var i = 0;
    var ColName1 = "APPROVER_FULL_NAME";
    while ( rs.next() )  {
        System.log( "Row[" + i + "] : " + ColName1 + "='" + rs.getString(ColName1));
       
    }
    if ( i == 0 )  {
        System.log( "No rows" );
    }
    rs.close();
    stat.close();
} catch( ex )  {
    throw "Connection to database failed (Reason: " + ex + ")";
} finally {
    if (con) {
        con.close();
    }
}

var myArray = new Array(sqlStatement = "select APPROVER_FULL_NAME from COST_CENTER ");

return myArray;

Do you know what I do wrong ?

0 Kudos
cdecanini_
VMware Employee
VMware Employee

Not sure what is wrong in your script and unfortunatley I have to debug mine ATM.

Another alternative to the action is to create a mini DB plug-in as I did here:

http://www.vcoteam.info/learn-vco/code-snippets-create-your-database-plug-in.html

There is also the SQL plug-in you can install and would certainly allow you to select your cost center records a lot easier.

Christophe.

If my answer resolved or helped you, please mark it as Correct or Helpful to award points. Thank you! Visit http://www.vcoteam.info & http://blogs.vmware.com/orchestrator for vCenter Orchestrator tips and tutorials - @vCOTeam on Twitter
0 Kudos
mg1978
Enthusiast
Enthusiast

Hi

I create now my Plug-in and it works well in a workflow. But I have problems to create an action where I can put this action as "Custom validation" or "Predefined list of elements" in my Workflow presentation.

Here is my workflow.

2071399.png

I need a drop-down filed in my workflow presentation where I get the APPROVER_FULL_NAME listed from the DB and the customer can choose on of them.

0 Kudos
bbonev
VMware Employee
VMware Employee

Hi,

Like Christophe said there's a special SQL plug-in. I  recommend you to use it for your scenario. SQL plug-in hides most of the complexity behind SQL and JDBC. It gives you a set of CRUD database operations which can serve you like basic building blocks for creating a more complex workflow logic.

Best regards,

Boyan

0 Kudos
cdecanini_
VMware Employee
VMware Employee

At this point you should have a list of the columns / rows when you use the plug-in object as input. If you must have a drop down then you need either to create an action using this object as input and returning an array of string and use it in a string input field with a predefined list property.

I am not sure if the SQL plug-in has the option to show the values as drop down without doing such a transformation in the action.

You can try it with downloading it from here: http://www.vmware.com/products/datacenter-virtualization/vcenter-orchestrator/plugins.html

It may end up to be a lot simpler to maintain. I mentioned the custom DB plug-in because this is what I have used previously and had unfortunaltely no chance to work yet with the SQL plug-in.

Christophe.

If my answer resolved or helped you, please mark it as Correct or Helpful to award points. Thank you! Visit http://www.vcoteam.info & http://blogs.vmware.com/orchestrator for vCenter Orchestrator tips and tutorials - @vCOTeam on Twitter

View solution in original post

0 Kudos
bbonev
VMware Employee
VMware Employee

You can create an attribure and then add it to the presentation. It will be shown when the workflow is started and the user can assign a value for it. If you want you can create a custom validation. To do this go to the workflow's presentation. Select the input param or attribute you want to add validation to. At the bottom there are 2 tabs - General and Properties. Select properties and add "Custom validation". Here you can select your own action that will be used for selected field's validation.

0 Kudos
mg1978
Enthusiast
Enthusiast

So I have now this action

2071399_3.png

But if I start my workflow than I don´t get the Names

0 Kudos
cdecanini_
VMware Employee
VMware Employee

I have never created an array this way.

I guess you have to iterate in your approver column records and push each element to an array of strings or possibly use a concat method.

Chrisotphe.

If my answer resolved or helped you, please mark it as Correct or Helpful to award points. Thank you! Visit http://www.vcoteam.info & http://blogs.vmware.com/orchestrator for vCenter Orchestrator tips and tutorials - @vCOTeam on Twitter
0 Kudos
stevenbright1
Enthusiast
Enthusiast

The following code (based on your code above) will add all of the values from a query to a string array and then output the string array. This could be added as an action and then the action could be assigned as a predefined list of values on a workflow's presentation. I have tested it with success.

var main = new JDBCConnection();
var conn;
var sqlStatement="SELECT column1 FROM table";
var myArray = new Array();
var connString = "jdbc:jtds:sqlserver://" + dbServer + ":" + dbPort + "/" + dbName + ";"
System.log( "Connection URL: " + connString);

try  
{     conn = main.getConnection( connString, dbUser, dbPassword );     System.log( "Connection to database successful" );     var stat = conn.createStatement();     var rs = stat.executeQuery( sqlStatement );     var i = 0;     var ColName1 = "column1";     while ( rs.next() ) 
    {         myArray.push(rs.getString(ColName1));     }     rs.close();     stat.close(); }
catch( ex ) 
{     throw "Connection to database failed (Reason: " + ex + ")"; }
finally
{     if (conn)
    {         conn.close();     } } return myArray;
0 Kudos