Service Virtualization

I want to print the DB Records with certain limited no. of Records.

select * from <tablename> where column_name = {{col_value}}.

I have done parameterization via Data Set in excel for that column_name. In Excel sheet , say 10 rows are there. My query here is, I want to print all the 10  rows in a single run of the sql query. Is that possible. If yes, please guide me.

Currently, Via Data set -> the sql query will pickup the first value in excel and print the output for that particular value and again second value,... it goes on till 10th value in excel. 

Typically, a step would print each row one at a time as the code iterates over the result set. 

If you would like to print the occurrences once rather than print a line for each occurrence, then I would suggest that you consider looping over the resultset, storing each value in a hold area, and printing the results after all the rows have been iterated.  

Let us know if this is helpful or if your requirements are different.

For example the basic steps might look like this:

Build Hold Area - JSR 223 step

This step, simply creates some type of hold area for the results.  For example, let's say we want to create a property that has a header row separated by a tab character.

import java.lang.StringBuilder;

StringBuilder sb = new StringBuilder();
sb.append("col1\tcol2\r\n");

// Save the hold area to support the loop
testExec.setStateObject( "fl_holdArea", sb );

The read state info is a Read Rows from JDBC Table dataset where you submit your query and iterate. colValue is a property coming from whatever you input source data is:

The Build State Information step is a JSR 223 that takes each row from the result set and appends it to the hold area. The dataset will  control the loop so that only the number of occurs in the result set will be iterated.

import java.lang.StringBuilder;

// Get the hold area created in the first step 

StringBuilder sb = testExec.getStateObject( "fl_holdArea" );

// Get the Column(s) returned in the query by asking testExec to 

// give them back then append them to hold area

sb.append( testExec.getStateValue( "COL1") ).append("\t");
sb.append( testExec.getStateValue( "COL2" ) ).append("\r\n");

// Save the hold area off for the next loop over the result set 
testExec.setStateObject( "fl_holdArea", sb );

return true;

Thie Build State Info step loops onto itself so that each of the results from your query is returned. Change the <COLx>, "COL1", "COL2" and <yourTableName> tags in the query and in the script code based on your requirements to the real column names (case sensitivity applies).

At end of the result set, the step branches to the Print Once step which accesses the {{fl_holdArea}} and sends the output to String.

Thanks much Joel for your step-by-step explanation.

I am not able to understand the step "Read state info and its SQL Query". Where should i provide the dataset for that SQL Query. 

In PrintOnce (JSR 223 ) Step, may i need to provide only {{fl_holdArea}}. Will it provide the complete 'n' set of rows.

Please guide me the above two steps. Thanks in Advance..

If you are asking if you can perform an SQL SELECT against an Excel dataset in DevTest, the answer is no, not out of the box without a customization.

The sample flow in the post focuses on the concept of iterating over a result set and storing off the values. The example was not trying to demonstrate the complete implementation because we do not have all of the requirements. 

Consider that it is likely you need to implement some sort of loop within a loop concept. 

The outer loop reads the Excel data one row at a time. For each row in the Excel sheet, the value of "colValue" is set into a property by DevTest. The inner loop performs a SQL query where {{colValue}} is used as the input in the WHERE clause. The Read Rows from JDBC table executes this query and creates a dataset containing the resultset. This dataset is iterated one row at a time. At the end of iterating over the dataset, Read Rows from JDBC Table executes the Print Row step which prints the results for this "colValue" and returns to the outer loop to process the next Excel row (colValue). 

From an implementation perspective, there are numerous ways to implement (i.e., single test case or via Subprocess).

The example above shows the concept of a single test case.

Set colValue by reading Excel Step reads the input dataset where "colValue" is located.

Read Rows from a JDBC Table is the SQL Query having WHERE someColumn = '{{colValue}}'. This creates the resultset that is iterated by the inner loop.

Print Row and loop step prints one time for the current "colValue" property value.  Long form code inside the Print Row and Loop could be or you might write it to a file or do something else with it:

import java.lang.StringBuilder;

StringBuilder sb = testExec.getStateObject( "fl_holdArea" );

return sb.toString();

If you only wanted to print the hold area once after all of the Excel values are processed, the Excel DS For Query dataset (1st step) would branch to Print Row whose next step is END. The Read Rows from JDBC at END would branch to Set colValue by reading Excel.