Action to get data from SQL DB in Workflow Presentation

[mg1978]

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 ?

[cdecanini_]

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.

[mg1978]

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 ?

[cdecanini_]

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.

[mg1978]

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.

[bbonev]

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

[cdecanini_ · Accepted solution]

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.

[bbonev]

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.

[mg1978]

So I have now this action

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

[cdecanini_]

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.

[stevenbright1]

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;