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 ?
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.
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.
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 ?
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.
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.
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.
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
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.
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.
So I have now this action
But if I start my workflow than I don´t get the Names
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.
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;