Retrieve Data from Result Sets

Use the techniques in the following examples to retrieve data from the result sets returned by the CommServDBQuery activity and the SQLQuery activity.

Specify the Column Value

Consider that you defined a SQL query to retrieve the information of a client with the name cwslab05. To better explain this scenario, the following image shows this query and its result from the SQL Server:

Assuming that you want to access the net_hostname of the client, you can get the value of this property by specifying the column name or index:

 
xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row[1]/col[@name="net_hostname"]}
xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row[1]/col[4]}

Specify the Row and Column Value

Consider that you defined a SQL query to retrieve the information of:

• clients with the name cwslab05, and

• Agents installed on a client whose ID is from a client with the name cwslab05

  To better explain this scenario, the following image shows these queries and their results from the SQL Server:

  

  In this scenario, you will get more than one result set, as shown in the image above. See the following ways to access the data using row and column values:

• Getting the data from the third row in the second result set:

   
  xpath:{/workflow/CommServDBQuery_1/resultSets[2]/row[3]/col[4]}

• Getting the subclient name of the Agent from the first row in the second result set:

   
  xpath:{/workflow/CommServDBQuery_1/resultSets[2]/row[1]/col[@name="subclientName"]}

Retrieve the Number of Result Sets, Rows, and Columns

Since the output of this activity are list types, you can use the xpath function count() to retrieve the number of elements in the list. Use the following xpath expressions to retrieve the number of:

• Result Sets

  xpath:{count(/workflow/CommServDBQuery_1/resultSets)}

• Rows

  xpath:{count(/workflow/CommServDBQuery_1/resultSets[1]/row)}

• Columns

  xpath:{count(/workflow/CommServDBQuery_1/resultSets[1]/row[1]/col)}

  You can also use Java expressions to retrieve the number of result sets, rows and columns as follows:

  java:{xpath:{/workflow/CommServDBQuery_1/resultSets}.size()}}:java
  java:{xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row}.size()}}:java
  java:{xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row[1]/col}.size()}}:java

Manipulate the Output Data by Using Java

You can manipulate the output data programmatically as shown in the below Java code. The code tries to retrieve some client information from the first result set:

//Using .get(0) to work with the first result set
DataSet ds = xpath:{/workflow/CommServDBQuery_1/resultSets}.get(0);
while (ds.hasNext()) { String clientName = ds.get("name"); String hostName = ds.get("net_hostname");
}
// OR
String hostName;
for (DataRow row : ds.getRows()) { if (row.get("name").equals("myserver")) { hostName = row.get("net_hostname"); }
}

However, if you want to iterate through each of the result sets instead of checking only the first one, you can use the .get(n) method on the list object as shown below:

ResultSet resultSets = xpath:{/workflow/CommServDBQuery_1/resultSets}.get(n);
// where n is the index (starting from 0) for the result set that you want to retrieve
for (DataSet ds : resultSets){    
// do something with resultSet
}

Retrieve the Client Names by Using a Loop Activity

Consider that you want to use the ForEach activity to iterate through the SQL query for retrieving the details of all clients in your CommCell. Define one of the following xpath expressions in the ForEach activity:

• If you want to iterate through the client names, then use:

  xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row/col[@name=’name’]}

• If you want to iterate through all the columns (which are the client details) for the first client in the list, then use:

  xpath:{/workflow/CommServDBQuery_1/resultSets[1]/row}

Related Topics

Built-In Activities for Workflows - Utilities