SQL Statement in an imput parameter

[mg1978]

Hi All,

I would like to build a workflow with one parameter. The parameter should take the logon user and search based on the logon user in a SQL DB where server names are mapped to users with the same format like the logon user. The parameter should presented an array of server names which are mapped in the DB to the logon user.

Here an example what I mean:

SQL-Table

User	Server Name
Karl Heinz	SRV1000
Karl Heinz	SRV1001
Peter Mustermann	SRV1002
Peter Mustermann	SRV1003

Karl Heinz login to the vCO or to the webview and start the workflow, he sould only see SRV1000 and SRV1001.

Have somebody an idea how I can build this workflow.

Thanks.

[cdecanini_]

You need the SQL plug-in.

Then you need to create an action. In this action you can use Server.getCurrentUser() to get the current user, then you can get the full name.

From there you can use the SQL plug-in to list the records and return an array of string containing the servers matching the fullname.

Once done you can test the action in a workflow, then use it in the presentation properties "pre-defined list" or a string input.

Christophe.

[mg1978]

Thanks Christophe

do you have an example how I can build the action. I never worked with the SQL plugin.

[cdecanini_]

Unfortunately not. There is a goofd blog article here : http://blogs.vmware.com/orchestrator/2012/02/sql-plug-in-comes-on-the-stage-to-leverage-our-basic-da...

[tschoergez]

Hi!

no problem: You can create an Action that does the SQL query, and returns all the VM names as Array/String.

Then you can bind this Action to a property "Predefined List of Elements" for the second input parameter.

Use the SQL Plugin to defind the connection to the database, then you can create a query with this script:

//database is input of Type SQL:Database
// table is input of Type SQL table
var currentUsersVMs = new Array();
//get the displayName of the workflow starter
var currentUser = Server.getCurrentLdapUser().displayName;
try {
var jdbcConnection = new JDBCConnection();
var connection = jdbcConnection.getConnection(database.connectionURL, database.username, database.password);
System.debug("...connection successful");
var selectStatement = connection.createStatement();
//adjust with your column names to get a string like
//SELECT "Server Name" FROM "yourTable" WHERE "user" = "Karl Mustermann" 
var selectString = "SELECT 'Server Name' FROM \"" + table.name + "\" AS logins WHERE login.name = '" + currentUser + "' ;";
System.debug("Executing query: " + selectString);
var resultSet = selectStatement.executeQuery(selectString);
// depending if you expect users with no VMs
//if (!resultSet) throw "No rows found in table '" + table.name + "'";
while (resultSet.next()) {
//resultSet columns start a 1!
currentUsersVMs.push(resultSet.getStringAt(1));
}
} catch (exception) {
throw "Error reading from database: " + exception;
} finally {
    try { if (resultSet != null) resultSet.close(); } catch (e) {};
    try { if (selectStatement != null) selectStatement.close(); } catch (e) {};
    try { if (connection != null) connection.close(); } catch (e) {};
}
System.debug("VMs for current User: " + currentUsersVMs);
//for action:
return currentUsersVMs;

If you don't want to specify the database in the sql plugin, create the connection URL manually (it follows the JDBC guidelines for connection urls).

And for future improvements you want to change the connection.createStatement() with a PreparedStatement for better security and readabililty.

Cheers,

Joerg

[cdecanini_]

Joerg,

This is how I would have done it before the SQL plug-in was released. I am assuming it would be a lot simpler to do it with it.

Unfortunateley I do not have the time for this ATM. Still interested to see how the same thing would be done with the SQL plug-in.

Christophe.

[tschoergez]

Yeah, the plugin makes it easier. However, the out of the box features are in workflows, not actions, so it does not help directly for input presentation.

However, you can create your own action with something like (not tested, just from mind!)

//databaseParameter again is in parameter of type SQL:Database

var resultingArray = new Array();
var query = "SELECT and so on..."
var resultingActiveRecords = databaseParameter.readCustomQuery(query);
for each (var resultRecord in resultingActiveRecord) {
     var vmname = resultRecord.getProperty("column name...");
     resultingArray.push(vmname);
}

Ok, might be a bit more straight forward :smileylaugh:.

Cheers,

Joerg

[mg1978]

Sorry perhaps I am to stupid for ths here.

I created a action

Then a workflow

I don´t get any result if I start the workflow.

Can somebody help me?

[mg1978]

Here is the action and workflow,

[tschoergez · Accepted solution]

In the Action you have to add the last line

return resultingArray;

to give the results back.

Didn't put that into the second example :smileyblush:.

Cheers,

Joerg

[mg1978]

Thank you very much.

It works now.