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);
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;
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;
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
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.
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.
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?
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.