This chapter provides instructions on using the OpenScript Utilities Module, which provides commonly used testing functions.
The Utilities Module is an extension to the Basic Module. The OpenScript Utilities module includes the following features:
Text File Processing. Read values from text files including CSV and XML files as well as copy and move files in the file system.
Databases. Read values from various databases such as Oracle as well as other JDBC-ODBC Compliant databases
XML XPath Expressions. Generate XPath expressions from valid XML files.
You can use the Utilities Module API to enhance recorded scripts with additional testing functionality. Commands that are specific to the Utilities Module are part of the "utilities" class.
You can use the utilities API to read values from text files including CSV and XML. The following sections explain how to use the utilities API.
The Utilities API includes a getFileService()object with methods for working with text files such as reading lines of text from a file or appending to a file. The following examples show some ways to use getFileService.
To add code that reads text from a file:
Create a script project.
Open the Java Code view.
Add the readLines() method to specify the file to read. The following example shows how to parse the lines of text in a file and print to the OpenScript console view:
import java.io.File;
//[...]
String[] lines = utilities.getFileService().readLines("C:/Sample.txt");
for (String line : lines) {
info(line);
}
To add code that appends text to a file:
Create a script project.
Open the Java Code view.
Add the appendStringToFile() method to specify the file to which to append text strings. The following example shows how to create a new file and append lines of text to the file:
import java.io.File;
//[...]
utilities.getFileService().createDestinationFile("myFile.txt", false);
String line1 = "This is a new line 1";
String line2 = "This is a another new line 2";
String contents = "\n" + line1 + "\n" + line2;
utilities.getFileService().appendStringToFile("myFile.txt", contents);
The Utilities API includes a loadCSV() object for working with data from a Comma Separated Value text file.
To add code that loads and prints data from a .CSV file:
Create a script project.
Open the Java Code view.
Add the loadCSV method to specify the file to read. For this example the file, "C:\customer.csv" contains this data:
FirstName,LastName,MiddleInitial John,James,R Mary,Simpson,J
The following example shows one way to parse a table of text in a .CSV file and print values to the OpenScript console view:
import java.io.File;
import java.util.List;
//[...]
String filePath = "c:\\";
String csvFile = filePath + "fmstocks_data.csv";
File file = new File(csvFile);
Table table = utilities.loadCSV(csvFile);
//Print the CSV file
String columns = "";
int columnNumber = table.getColumns().getColumnCount();
String [] columnNames = table.getColumns().getColumnNames();
for (int index=0; index<columnNumber; index++)
columns += columnNames[index] + " ";
info(columns);
List <Row> rows = table.getRows();
for (int index=0; index<rows.size(); index++) {
String [] rowValue = rows.get(index).getAll();
String rowContent = "";
for (int columnIndex=0; columnIndex<rowValue.length; columnIndex++)
rowContent += rowValue[columnIndex] + " ";
info(rowContent);
}
The Utilities API includes a loadXML() object for reading text from a XML formatted text file.
To add code that reads text from a .XML file:
Create a script project.
Open the Java Code view.
Add the loadXML method to specify the file to read. For this example the file, "C:\grocery.xml" contains this data:
<?xml version="1.0" encoding="utf-8"?> <Oceans> <ocean name="Arctic"/> <ocean name="Atlantic"/> <ocean name="Indian"/> <ocean name="Pacific"/> <ocean name="Southern"/> </Oceans>
The following example shows how to parse a table of text in a .XML file and print values to the OpenScript console view:
XML xml = utilities.loadXML("C:/oceans.xml");
XML root = xml.getChildren()[0];
info(root.getTagName());
XML[] oceans = root.getChildren();
for (XML ocean : oceans){
info(ocean.getAttribute("name"));
}
Getting values from a database requires a database definition, a database SQL query or SQL execute and a disconnect from the database. This section explains how to manually add database actions to a script. See Section 3.2.7, "Importing Database Capture Files" for additional information about importing a DBReplay capture file or SQL statements from a plain SQL and PL/SQL statements .SQL script file to generate an OpenScript load testing script.
To get values from a database:
Create a database script project.
Select the node where you want to add the database definition.
Select the Script menu and then select Other from the Add sub menu.
Click OK.
Specify the database definition information.
Database Driver - specify the database driver to use.
Oracle Thin (oracle.jdbc.driver.OracleDriver) - when selected, the database connection uses the Oracle Thin database driver. Specify the following connection information:
Hostname - specify the name of the host machine on which the database is located.
Port - specify the port number to use.
SID - when selected, specify the System ID to identify the particular database on the system.
Service Name - when selected, specify the Service Name defined as the alias for the database instance.
ODBC (sun.jdbc.odbc.JdbcOdbcDriver) - when selected, the database connection uses the ODBC database driver. Specify the following connection information:
Data source - specify the name of the ODBC data source to which to connect.
URL - specify the URL to use to access the database.
Username - specify a user name to log into the database.
Password - specify a password to log into the database.
Alias - specify an alias name to use to identify the database definition. The alias appears in the script tree for the definition and is selected when adding database actions to the script.
Test - test the connection to the database based upon the specified driver and database information.
Click Test to verify a successful connection.
Click OK.
Select the node where you want to add the database connection. The OpenScript database connect method is optional. The database connect is invoked automatically when calling execute or query methods
Select the Script menu and then select Other from the Add sub menu.
Select the database alias and click OK.
Select the node where you want to add the database query or execute statement.
Select the Script menu and then select Other from the Add sub menu.
Expand the Database node and select SQL Query or SQL Execute.
Specify the SQL statement to query or execute and click Add.
Specify a data type and define a name for the parameter.
Click OK.
Click OK.
Select the node where you want to add the database disconnect.
Select the Script menu and then select Other from the Add sub menu.
Expand the Database node and select Disconnect.
Select the database alias and click OK.
In the Java Code view, the utilities.getSQLService() methods will be added to the script code for each database script action (additional code and comments added):
//define database
utilities.getSQLService().define("oracledb",
"oracle.jdbc.driver.OracleDriver", "00.000.000.000", "myuserID",
decrypt("ZgEQLMIUx8EVDAhfAenvyg=="));
//connect to database
utilities.getSQLService().connect("oracledb");
//execute SQL statement
String query = "Create table Employee (ID number(4) not null unique, " +
"FirstName varchar2(40) not null, LastName varchar2(40) not null, " +
"Country varchar2(40), HireDate date)";
info("Query: " + query);
utilities.getSQLService().execute("oracledb", query);
//execute update SQL statement
query = "Insert into Employee (ID, FirstName, LastName, Country, HireDate) " +
"Values (101, 'Tom', 'Smith', 'USA', '01-JAN-95')";
utilities.getSQLService().executeUpdate("oracledb", query);
//query SQL statement
query = "Select * from Employee";
Table table = utilities.getSQLService().query("oracledb", query);
//print table
for (int i=0; i<table.getRowCount(); i++) {
Row row = table.getRow(i);
String [] rowValue = row.getAll();
String rowContent = "";
for (int col=0; col<rowValue.length; col++)
rowContent += rowValue[col] + " ";
info(rowContent);
}
//disconnect from database
utilities.getSQLService().disconnect("oracledb");
A SQL Query test can be used to test data values retrieved from a database using a SQL query against expected values.
To add a SQL Query test:
Create a database script project.
Select the node where you want to add the SQL Query test.
Select the Script menu and then select Other from the Add sub menu.
Expand the Database node and select SQL Query.
Click OK.
If you have already created a database connection, select the database alias. If you have not already created a database connection, click New and specify a new database definition. See Section 20.3, "Getting Values from a Database" for additional information about creating a database definition.
Enter a SQL Statement to use to define the data to retrieve from the database.
Click Test to verify the data is retrieved from the database. If the Test Results dialog does not appear listing the data from the SQL Statement, verify the SQL Statement is correctly structured.
Click Close to close the Test Results dialog box.
Click Create SQL Query Test.
Enter a name for the test.
Set the Verify only, never fail option.
Select the Test Data option:
Entire Table - when selected, the entire table of data retrieved from the SQL Statement is included in the test.
Filter table by query- when selected, only the data that matches the filter query is included in the test. Enter a SQL query and click Apply to apply the filter to the test data.
Apply - applies the query filter to the test data.
Enable testing on specific data values by selecting or clearing the check boxes in each cell. Click Enable All to enable testing on all data values in the grid. Click Disable All to disable testing on all data values in the grid, then select individual cells manually.
[Row] - shows the row number of the test data.
[Table column name(s)] - shows the name(s) of the database table field(s) retrieved from the database by the SQL Statement. Each column in the SQL Query Test will show table field names as the column headers. Select the check box to enable testing or clear the check box to disable testing on specific data values.
For each selected table cell, specify the SQL Query Test Details:
SQL Query Test Details - shows the test details for the selected table cell.
Value from DB - shows the actual value of the data retrieved from the database for the selected table cell.
Enable - when selected, testing for the selected cell is enabled.
Cell - shows the row and column information for the selected table cell.
Row - the row number of the selected table cell.
Column Name - the column name of the selected table cell. This is the field name retrieved from the database.
Value Type - specifies the data type for the value in the selected table cell.
Operator - specifies the test operator used to compare the data value in the selected table cell against the expected value.
Expected Value - specifies the expected value to compare against the value retrieved from the database.
Substitute Variable - opens a dialog box for selecting a script variable to use for the Expected Value.
Click OK when finished. New Query and Query Test nodes will be added to the script tree.
In the Java Code view, the utilities.getSQLService() methods will be added to the script code for the database query and SQL test:
utilities.getSQLService().query(9, "mydb", "Select FName from Employee", null);
{
utilities.getSQLService().assertQuery(
null, "mySQLtest", null,
utilities.getSQLService().cell(1, "FNAME",
"Aaron", SQLTestOperator.StringExact),
utilities.getSQLService().cell(2, "FNAME",
"Adrian",SQLTestOperator.StringExact)));
}
When you play back the script, the test results appear in the Results view.
You can use the utilities API to execute a SQL call database procedure statement and return a list object for an out type parameter value list.
You can use the utilities.getSQLService().callProcedure("recid", "alias", "sql", "params") method to call the procedure and return a list object for out type, where:
recid is an optional Integer specifying the recorded ID.
alias is a String specifying the user-defined database alias specified for the database containing the procedure.
sql is a String specifying the SQL statement to be sent to the database, typically a static SQL to call database procedure statement.
params is an optional List<Object> object containing all the SQLService.parameter or SQLService.SQLParameterType-wrapped parameter values by index. The index starts with 1.
The following example shows the code used to define and connect to a database, call a database procedure, and disconnect from the database:
utilities.getSQLService().define("local_XE_DB",
"oracle.jdbc.driver.OracleDriver",
"jdbc:oracle:thin:@localhost:1521/XE", "system",
;deobfuscate("6GaD7eW3kGVe5TKHmuI/+w=="));
utilities.getSQLService().connect("local_XE_DB");
utilities.getSQLService().callProcedure(44, "local_XE_DB",
"Begin\n insertInfo2(014,'anna14',21,'F','ecnu14',
'History','1288',to_date(?,'yyyy-mm-dd'));\nEnd;",
utilities.parameters(
SQLService.parameter("1989-02-18",
SQLService.SQLParameterType.In)));
utilities.getSQLService().disconnect("local_XE_DB");
The Utilities Module includes an XPath generator utility that you can use to generate an XPath Expression to a selected element from a valid XML file.
To use the XPath Generator:
Create an XML file that contains the tags and values to use to generate the XPath expression. The following is an example of a simple XML file that can be used with the XPath Generator:
<?xml version="1.0" encoding="utf-8"?> <Oceans> <ocean name="Artic"/> <ocean name="Atlantic"/> <ocean name="Indian"/> <ocean name="Pacific"/> <ocean name="Southern"/> </Oceans>
Create and record a test script. The Tools menu appears on the OpenScript menu bar for functional and load test scripts.
Select Generate XPaths from the Tools menu.
Click Browse and select the XML file to load.
Expand the XML tree under the Tags section of the XML file.
Select the XML tag to use to generate the XPath. The generated XPath appears in the XPath Expression field in a form similar to /Oceans/ocean[1]/@name.
Use the Ctrl+C and Ctrl+V keyboard combinations to copy and paste the generated XPath to a method in the Java Code tab of the script view.
The XPath Expression can be used in the utilities findByXPath API method, as follows:
utilities.loadXML("filePath").findByXPath(xpath, xml)