vCO Action used to call SQL DB

[Uridium454]

Hoping someone can help out.  I am trying to call a custom SQL query from an ASD form utilizing a vCO action.  I can make this happen through the use of a workflow, passing the SQL DB I have setup previously.  Is there a way to call that SQL DB from within the action, and perform the custom query? Any and all help would be greatly appreciated.

Custom query call below where databaseParameter = SQL DB.

var queryResult = databaseParameter.readCustomQuery(query);

[Uridium454 · Accepted solution]

I managed to stumble across a solution to my own issue and thought I would share my findings in hope it helps someone else down the road.  The answer was derived from the following blog - http://practical-admin.com/blog/using-the-vco-to-wfa-database-connection/   Thank you for the great post Andrew!

// ############# Section 1: Get the database connection #############

// get all of the databases that are registered to the SQL Manager

var databases = SQLDatabaseManager.getDatabases();

// a variable to hold our database

var database = null;

// loop through them until we find our database, using the friendly name we configured

for each (var db in databases) {

   if (db.name == "WFA") {

  database = db;

   break;

   }

}

// a bit of checking to make sure our database was found

if (database == null) {

   throw "Unable to find database!";

}

// ############# Section 2: Query the database #############

// the SQL query we want to execute. Javascript does not support multi-line strings

// so we append the each "line" to the same variable to keep it readable, but make

// a single long string

var query = "";

query += "SELECT";

query += " name AS Name,";

query += " primary_address AS 'Primary Address'";

query += " FROM";

query += " cm_storage.cluster";

query += " ORDER BY";

query += " name ASC";

// execute the query

var result = database.readCustomQuery(query);

// a bit of info to log

System.debug("Database query for Cluster Names returned " + result.length + " records");

// ############# Section 3: Parse the result and return values #############

// iterate over the returned records to populate our return array

var data = new Array();

for (var i = 0; i < result.length; i++) {

  System.debug("Found Cluster: " + result[i].getProperty("name"));

  data[i] = result[i].getProperty("name");

}

// return the data

return data;

[ChristianWehner]

Now I understand what you wanted to achieve.

You could build an action with your db as input parameter:

add an general attribute to your workflow where you want to use your database:

And now on your input presentation provide your general attribute 'db' with the correct db into the action:

Cheers Chris

[Uridium454]

Thanks for the reply Chris.  The snag I was running into was trying to use the action from an ASD form.  Unfortunately, I have not found a way to call a workflow from those forms.  it seems that the only option I had was to call an action to fill out a drop down.

[BScott66]

Uridium454,

I have been trying to use the same code for a while and have another post in the community.

First I would ask how are you using the results returned from the database?

For me I have a static table that contains the Lifecycles/Environments. It is a 2 column table with Description and Designation.

What I am trying to accomplish is to use the query to populate a Drop-Down in a ASD Form within a Service Blueprint.

a Drop-Down has 2 fields Value and Label which I am expecting to map from the array/string that is returned from the script/query.

Needless to say I get no results and am left with an empty Drop-Down

Any ideas?

Code used below (As you will see everything is hard coded, I am not passing any attributes or anything at this point,):

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// ############# Section 1: Find the database #############

// the SQL query we want to execute.  Javascript does not support multi-line strings

// get all of the databases that are registered to the SQL Manager

var databases = SQLDatabaseManager.getDatabases();

//  a variable to hold our database

var database = null;

// loop through them until we find our database, using the friendly name we configured

for each (var db in databases) {

    if (db.name == "corpa.cmdb") {

        database = db;

        break;

    }

}

// a bit of checking to make sure our database was found

if (database == null) {

    throw "Unable to find database!";

}

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// ############# Section 2: Query the database #############

// the SQL query we want to execute.  Javascript does not support multi-line strings

// so we append the each "line" to the same variable to keep it readable, but make

// a single long string

var query = "";

query += "select";

query += "    Designation as Value,";

query += "    Description as Label";

query += " from";

query += "    dbo.Env";

// execute the query

var result = database.readCustomQuery(query);

// a bit of info to log

System.debug("Database query for Environments returned " + result.length + " records");

///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////

// ############# Section 3: Parse the result and return values #############

// iterate over the returned records to populate our return array

var data = new Array();

for (var i = 0; i < result.length; i++) {

    System.debug("Found Environment: " + result[i].getProperty("Value"));

    data[i] = result[i].getProperty("Value");

}

// return the data

return data;

Thanks,

Bill S.

[BScott66]

Uridium454,

I notice that you have posted Andrew's posting exactly which only returns a single entity.

Is that what you used to populate your drop-down which contains 2 fields?

Also, Where would I go to see the System.debug output to verify the script is returning data and that it is just the form not accepting the data returned?

[Uridium454]

Bill,

Sorry for the delay on my response. Didn't realize there was an additional question attached to this post. :smileysilly:

To find the database and related objects I used the following bit of code:

     var databaseParameter = System.getModule("com.module.name.here").getDatabase("MY_DB");

This returns the database connection I had previously setup in vRO along with the necessary connection information.

For the SQL query itself I had to work a bit of different magic as I am basing my query off of a custom property that I have bound to the business group.  This allows me to return an array of values based on the custom prop value.  In my case I needed to return billing client information based on who was accessing the form.

The below bit of script returns the business group custom property that I am looking for:

// Note: cafehostID is an input value that must come from the form.  I am sure this can be extracted with a bit of additional code, and the need for the value to be set on the form will be no more.

var vcaccafeHost = vCACCAFEEntitiesFinder.getHost(cafehostID);

var businessGroups = vCACCAFEEntitiesFinder.getBusinessGroups(vcaccafeHost);

var businessGroup;

for each (var bg in businessGroups) {

     if (bg.name == bgName) {

          businessGroup = bg;

     }

}

var bgProps = businessGroup.getCustomProperties();

Once I have the custom property value I am after I then make the necessary query to SQL.

var resultArray = new Array();

for each (var prop in bgProps) {

     if(prop.name == "billingClient"){

          if(prop.value == "ITC-ABC"){

               var query = "SELECT * FROM vwBC WHERE (BILLINGCLIENT LIKE " + "'" + "ITC" + "'" + ' + ' + "'" + '%' + "'" + " OR" + " BILLINGCLIENT LIKE " + "'" + "EXE" + "'" + ' + ' + "'" + '%' + "'" + " OR" + " BILLINGCLIENT LIKE " + "'" + "JUD" + "'" + ' + ' + "'" + '%' + "'" + ")";

          }else if(!(prop.value.split("-")[2])){

               var branch = prop.value.split("-")[0];

               var agency = prop.value.split("-")[1];

               var query = "SELECT * FROM vwBC WHERE BILLINGCLIENT LIKE " + "'" + branch + '-' + agency + "'" + ' + ' + "'" + '%' + "'";

          }else{

               var branch = prop.value.split("-")[0];

               var agency1 = prop.value.split("-")[1];

               var agency2 = prop.value.split("-")[2];

               var query = "SELECT * FROM vwBC WHERE BILLINGCLIENT LIKE " + "'" + branch + '-' + agency1 + '-' + agency2 + "'" + ' + ' + "'" + '%' + "'";

          }

     }

}

var queryResult = databaseParameter.readCustomQuery(query);

// Loop through all of the results pushing desired property into a new result array

for each(var result in queryResult){

  var client = result.getProperty("BILLINGCLIENT");

  resultArray.push(client)

}

// Values in the return are pushed to the drop down list in the vRA form

return resultArray;

The value selected is being sent back to vRO through the ID field of the form. 

Please let me know if this does not make sense, and I would be glad to help further.