4 Using Data Parameterization

OpenScript allows users to parameterize script data inputs to perform data driven testing. OpenScript uses the following types of data sources:

4.1 Understanding Data Driven Testing (Parameterization)

Data Driven Testing, or parameterization, allows you to quickly and efficiently create automated data-driven tests.

The OpenScript Modules record parameters defined by each page of the application-under-test to a script. Data sources are used to hold input data that can be automatically fed as parameters into your application when the script is run. You can use the OpenScript Data Parameterization features to define variable values in script parameters and substitute values from Databank files, Databases, or Data Tables for the variable values.

During playback, the parameters in the application are filled with values from the Data source. Databank files can be easily created or modified using any simple text editor, spreadsheet, word processor, or database application. Users can create sophisticated unattended regression tests to thoroughly exercise the application by using varied input data.

Data Input Parameterization enables users to parameterize recorded script inputs to perform data driven testing in either the script GUI view or code view. These inputs could be form field inputs for Web applications but could also be other types of script inputs that users may parameterize. Types of inputs users may parameterize include:

  • Any user entered input data (i.e. parameterize the data I entered for the search field)

  • Test case values (i.e. parameterize a text string for validation so I can use different inputs for comparison during playback)

  • Recorded navigations (i.e. parameterize a starting navigation so I can navigate to different host servers during playback)

  • Recorded user actions/object identified (i.e. parameterize a link object path so I can click on different links during playback)

Data Input Sources enables users to drive input values from an external CSV file, Data Table/Excel file or other external data source such as a database (i.e. using a database query to pull inputs from a database table).

Data Parameterization GUI View enables users to configure the inputs they want to parameterize and the data source they want to drive the inputs from through a substitute variable GUI interface. For example, the "ticker" query string parameter for "Page [4] Ticker List" in the Tree View is set to the variable value "{{fmstocks_data.ticker,orcl}}".

Within the variable "{{fmstocks_data.ticker,orcl}}", "fmstocks_data" is the name of the Databank file, ".ticker" identifies the field name within the Databank file, and "orcl" is the recorded value.

Data Parameterization Code View Commands enable users to specify the inputs they want to parameterize and the data source they want to drive the inputs from through data parameterization in the code view.

For example, the "ticker" query string parameter for "Page [4] Ticker List" in the Tree View appears as http.querystring(http.param("ticker", "{{fmstocks_data.ticker,orcl}}") in the http.get method code in the Code View, as follows (line breaks and spacing added for clarity):

beginStep("[4] Ticker List", 3422);
{
   http.get(6, "http://testserver2/fmstocks/{{LINK_1_3,TickerList.asp}}", 
        http.querystring(http.param("ticker", "{{fmstocks_data.ticker,orcl}}"), 
             http.param("company", "")),  null, true, "ASCII", "ASCII");
}
endStep()

4.2 Using Script Databanks

Databanks are used to hold unlimited amounts of input data that can be automatically fed into your Web application. During playback, the parameters in the Web page are filled with values from the Databank file. The Databank and script parameter shortcut menu options allow you to map parameters in a script to fields in a Databank file as variable names.

Scripts must be configured to use Databanks. Use the Script Properties option on the Script menu to specify the Databank file(s) to use with a script in the Assets properties. Scripts can be configured to use more than one Databank file.

When you record a script that has a navigation that uses parameters, the parameter nodes appear under the Query String node:

In the Code View, the parameters appear in the http.param parameters of the http.querystring parameter.

When you configure the Databank(s) to use with the script, the Get next Databank record from databank name node and Java code are added to the script.

Select the script parameter node to map to a Databank and use the Substitute Variable option on the right-click shortcut menu to select the Databank field name to map to the parameter. The Databank file and field name appear in the parameter node of the script tree.

The variable appears in the Code view in the http.param parameters of the http.querystring parameter.

Use the Playback iterations to playback using the records in the Databank. You can also use custom code to loop through Databank records and assign values to variables.

4.2.1 Configuring Databanks

You must configure the Databank to use with a script before you can get records from the Databank to use in a script.

To configure Databanks to use with a script:

  1. Open or create a Script project.

  2. Select Script Properties from the Script menu.

  3. Select the Script Assets type. See Section 3.3.8, "Adding Script Assets" for additional information about adding script assets.

  4. Select Databanks.

  5. Click Add.

  6. Select Databank.

  7. Select CSV file or Database. Once a databank is defined as CSV or Database (SQL), the databank type cannot be changed to the other type.

  8. For CSV files:

    1. Select the Repository from the My Repositories tree.

    2. Select the Databank file from the repository.

    3. Set the Type to Databanks (*.csv, *.txt).

      Type: Specifies the type of databank file to add to the script (*.csv, *.txt).

    4. Select the Charset to use.

      Charset - specifies the character set encoding used for the databank file. The suggested charset encoding of the databank .csv file is the native charset of user machine. For US machines the suggested encoding is cp1252. For East European machines, the suggested charset is cp1251. If a databank file was saved with UTF-8 encoding with Unicode byte order mark (BOM), OpenScript detects it, and sets encoding to UTF-8. If the charset used for the databank file is different from the charset of the user machine or UTF-8 with BOM, then you must the correct charset. Otherwise, the databank will not be read correctly and may cause a script failure. You can select the correct charset from the Charset list or enter the correct Charset in the field.

      During playback, the Agent will define the charset for reading the databank file in following order:

      • If file has UTF-8 encoding with BOM, then it will be used.

      • The Charset specified during asset configuration will be used.

      • If no charset specified (case of 9.1 scripts), then UTF-8 encoding will be used.

    5. Enter an alias name to use for the Databank or leave the default alias name. The default alias name is the name of the .CSV Databank file.

      Alias: Specifies an alias name to use for the Databank. The Databank file name is the default. The Databank alias name is the name that appears when you add a Databank record retrieval node to a script tree.

    6. Set the Relative to option. The Relative to current script and Relative to a repository options specify how the current script will locate the specified script asset. The Relative to a repository option locates the script asset by a repository path such as, [Repository: Default] Default!/WebTutor, if the asset is selected from a repository. The Relative to current script option locates the script asset by a relative path such as ../WebTutor. Selecting the The Relative to current script option is not recommended as script-relative paths are more brittle than repository-relative paths if scripts are moved or shared.

      The following are guidelines when using script assets in a team or distributed environment:

      • Do not use Absolute Paths when referring to assets or saving assets. Oracle Load Testing does not support absolute paths.

      • OpenScript, Oracle Test Manager, Oracle Load Testing, and all command-line agents should all use the same shared repository names and paths.

      • Do not refer to an asset in another repository by a relative path.

    7. Click OK.

    8. Click OK to add the Databank file.

  9. For Databases, a Databanks Database Assets dialog box appears. This dialog box lets you specify the database and query to use as a databank. Contact your Database Administrator for the appropriate settings for your database. The following options are available:

    1. Specify the Database Driver.

      Oracle Thin - This driver option applies to Oracle databases.

      • Hostname - Specify the host name of the machine running the database. This is not required for a JDBC:ODBC or Custom driver setting.

      • Port - Specify the port for the driver you selected. For example, the default port for an Oracle Thin JDBC driver is 1521. Modify the port number if necessary. This is not required for a JDBC:ODBC or Custom driver setting.

      • SID - Specify the database or server ID.

      • Service name - Enter the Service name used for the Oracle database.

      ODBC - This driver option is available as an option for SQL and Oracle databases and any other database for which you have a JDBC:ODBC Bridge driver.

      • Data Source - Specifies the data source for the ODBC driver.

    2. Specify the URL, username, password, query string, and databank alias.

      • URL - Specifies the URL to use to connect to the database.

      • Username - Enter the username for connecting to the database, if required for authentication.

      • Password - Enter the password for connecting to the database, if required for authentication.

      • Query - Specify a single SQL query that returns all the rows needed as databank values. The SQL query cannot contain PL/SQL or SQL*Plus code. Only pure SQL is supported. You must ensure that the query returns the column names (i.e. databank fields) that the script expects.

        If you have a large database-backed databank, but will only use a small portion of the records in the test, then use the "Where" clause in the SQL query to minimize the amount of records retrieved from the database. For example, if you have database with 200000 records and only need to have 100 iterations retrieving records 201 through 301 sequentially starting from record 201. use a query such as Select * From LargeTable Where id > 200 AND id < 302. The start record will be 1 and no is range set. This reduces the databank preparation time and minimizes the amount of records retrieved from the database.

        Use the "Order By" clause in the query to make sure the results returned from the database are ordered as intended. For example, if you have a database table with Columns id, firstName, and lastName that is populated it with the following data:

        1, John, Smith
        2, Jane, Doe
        [...]
        400, Maria, Sanchez
        [...]
        200000, Sachin, Rajaram
        

        If you use the query Select * From users, the results of query could be unordered, meaning the first record in databank could be 400, Maria, Sanchez instead of 1, John, Smith. Using the query Select * From users Order By id would order the first databank record as 1, John, Smith as expected.

      • Alias: Specifies an alias name to use for the Databank. The Databank alias name is the name that appears when you add a Databank record retrieval node to a script tree.

    3. Click Test to verify the connection to the database.

    4. Click OK.

    5. Click OK to add the Databank file. For Databases used as databanks, a copy of all data is retrieved and indexed before the start of the test. The data is not read live during the test. See Section 4.2.4, "Playing Back Scripts With Iterations" for additional information about playing back scripts with databanks.

4.2.2 Creating or Editing Databank Files

Databank files are comma-separated value (".csv" or ".txt") files with the addition of formatting rules specific for databanks and rules derived from Excel formatting. Databanks can also be data retrieved from a database using an appropriate query to that generates data that conforms to the .csv databank file formatting rules.

When you open a Databank file from the Assets Script Properties, the Databank file opens in a text editor view. You can edit the Databank file directly in the text editor view. You can also create or edit databank files using another text editor or spreadsheet that can export to .csv formatted text files.

The general databank file formatting rules are as follows:

  • The first line of the databank defines the field headers (column titles). A comma is used as the field header delimiter (no spaces). The field header names are user defined. For example, FirstName,LastName,Mail,Phone defines four field headers for a databank file. The field headers can be referenced in the script code to specify valid databank variables. For example, if the first line of a databank with the alias name "myDB" contains the field headers user and password, the following databank variables are valid in a script configured to use the "myDB" databank: db.myDB.user and db.myDB.password.

  • Each line in the file following the field headers defines a databank record.

  • A line can end with a Line Feed (LF) character or Carriage Return/Line Feed (CR LF) characters.

  • Each databank record consists of field data (columns). A comma is used as the field delimiter (different line for each record, no spaces around commas). For example, John,Smith,JohnS@company.com,x993 defines the field data for a databank record corresponding to the field headers FirstName,Lastame,Mail,Phone.

  • Each databank record must have the same number of fields as the number of field headers. For example, if a databank file has four field headers in line 1 as FirstName,LastName,Mail,Phone, each databank record on lines 2 through n must have four field data columns in each record. The databank field data record john,smith,JohnS@company.com,x993 is correct. However, john,smith,JohnS@company.com is incorrect as this record contains only three fields. Insert an extra comma to leave a field column blank. For example Sachin,Bhat,,x783. As follows:

    FirstName,LastName,Mail,Phone
    John,Smith,JohnS@company.com,x993
    Mary,Ellen,MaryE@company.com,x742
    Sachin,Bhat,,x783
    
  • A quotation mark (") is used as an escape character for embedding new line characters (LF, CR) or comma (,) inside of a databank record. All escaped records, regardless if it has embedded LF, CR, or comma or not, should start with a quotation mark and end with a quotation mark followed by comma or CR/LF. For example, if a data value contains a comma, place quotation marks around the value, as follows:

    John,Smith,"Anytown, MA",(603) 993-0000
    

    New lines may be embedded inside of quotation marks, as follows:

    field1,"field2 contains two lines: Line one.
    Line two.",field3
    

    To use a quotation mark as itself not as an escaped character, escape the quotation mark. The correct format is "". Quotation marks in the middle of a record should be escaped always. For example the following record,

    THIS IS BEGINNING AND ""THIS IS END""
    

    is formatted correctly. The following record,

    THIS IS BEGINNING AND "THIS IS END"
    

    is not formatted correctly.

  • Blank lines are stripped out and ignored.

The character encoding of the CSV file is determined by an (optional) byte-order-mark (BOM) at the beginning of the file. Programs such as Notepad++ or Excel set this byte-order mark when users save a text document with a specific encoding character set like UTF-8. If no byte-order mark is specified, the CSV reader uses character set assigned to a databank asset, when the user adds the databank asset to a script, or uses the current platform's default character set to read the file (for example, cp1252 on most Windows English installations) for legacy databanks prior to Version 9.2.

4.2.3 Getting Databank Records

To get Databank records to use with a script:

  1. Open or create a script project.

  2. Configure the Databank to use with a script in the Script Assets Properties.

  3. Select the script node where you want to use the Databank record.

  4. Select the Script menu and then select Other from the Add sub menu.

  5. Expand the General node and select Get Next Databank Record.

  6. Select the databank or click New to add a new databank.

  7. Select the Record.

  8. Click OK.

  9. Select the Databank alias to specify the Databank file to get the record from.

  10. Click OK. A GetNextDatabankRecord: databank alias node will be added to the script.

    In the Java Code view, a getDatabank("databank alias").method (); will be added to the script code depending upon the type of record selected:

    getDatabank("customer").getNextDatabankRecord();
    getDatabank("customer").getFirstRecord();
    getDatabank("customer").getLastRecord();
    getDatabank("customer").getRecord(5);
    
  11. Right click the parameter node in the script tree that you want to substitute with a Databank variable and select Substitute Variable.

  12. If necessary, expand the Databanks node and select the Databank field you want to use as the input parameter data.

  13. Click Finish.

  14. The script node name/value pair changes to show the Databank alias name, field name, and recorded value as a variable value. For example:

    login = {{db.customer,login,ta906}}
    

    In the Java Code view, the parameter code changes to show the Databank alias name, field name, and recorded value as a variable value. For example:

    http.postdata(http.param("login", "{{db.customer,login,ta906}}")
    
  15. Click the Playback toolbar button to playback the script once to verify the it plays back correctly.

4.2.3.1 Getting Databank Records Using the API

You can use the additional API methods available with getDatabank("databank alias")in the Java Code view to retrieve specific records from the databank. This section provides examples of the available methods.

4.2.3.1.1 Databank API Usage Notes

These API methods are not compatible with databank's iteration settings Randomly or Shuffle Records. The Databank Exception "incompatible with db setting" will be thrown if these methods are used with the Randomly or Shuffle Records iteration settings.

The records obtained through these API calls are not counted against the usage count of all records. It is possible for an infinite script loop to occur if the When Out of Records iteration setting is set to Stop the User, but the script only uses these API calls to read records.

4.2.3.1.2 Loading a Databank

The following example uses the load() method to get a new databank that will override a statically defined databank in OpenScript:

getDatabank("alias").load("repository", "dbPathRelToRepository", "settings");

The load() method is used to programmatically override static databanks in a script. The load() method is used with functional testing scripts only. The parameters for the load() method are as follows:

repository - a String specifying the repository to look inside to locate the databank file. Valid repositories are mapped using the Manage Repositories options on the Tools menu. An example repository could be named "Default" and map to C:\OracleATS\OFT.

dbPathRelToRepository - a String specifying the path to the databank file within the named OpenScript repository. The file extension is not required. For example, it can be specified as "databank1", "databank1.csv", or "databank1.txt". Any leading file separator on the path, such as / or \ is ignored. The dbPathRelToRepository parameter cannot be null.

settings - a String specifying the databank settings to apply to the loaded databank. For example "startIndex=10:select=SEQUENTIAL:whenOut=LOOP_FOREVER". See the -dbopts settings in the General Section of Appendix A, "Specifying Command Line Settings" for the databank settings. If null, the settings revert to the default databank settings.

The following example shows how to load a databank file "euroCustomer" dynamically from the Databanks/files folder in the default repository and use the default settings.

public void run(){
 getDatabank("customer").load("default", "Databanks/files/euroCustomer", null);
 getDatabank("customer").getNextDatabankRecord();
 web.textbox("....").setText("{{db.customer.column1}}");
}

The following example shows how to load a databank file "euroCustomer.csv" to dynamically override the statically mapped databank file "customer.csv" from the Databanks/files folder in the default repository and use the default settings.

public void run(){
 //Alias "customer" is defined as a static Databank Asset customer.csv.
 getDatabank("customer").getNextDatabankRecord();
 web.textbox("....").setText("{{db.customer.accountNumber}}");

 //Override Alias "customer" with databank "euroCustomer.csv"
 getDatabank("customer").load("default", "Databanks/files/euroCustomer", null);
 getDatabank("customer").getNextDatabankRecord();
 web.textbox("....").setText("{{db.customer.accountNumber}}");
}
4.2.3.1.3 Getting a Record Count

The following example uses the getDatabankRecordCount() method to get the record count from the "customer" databank and prints the value to the Results view:

int recordCount = getDatabank("customer").getDatabankRecordCount();
String count = Integer.toString(recordCount);
info("Record Count = " + count );
4.2.3.1.4 Getting a Specific Record

The following example uses the getRecord(n) method to get a specific record from the "customer" databank and prints the value to the Results view:

getDatabank("customer").getRecord(5);

The following code example use a For statement to loop through all records in the databank:

int recordCount = getDatabank("customer").getDatabankRecordCount();
for (int i=1; i<=recordCount; i++) {
   info("Record count = " + 
      Integer.toString(i) +
      " of " + 
      Integer.toString(recordCount));
   getDatabank("customer").getRecord(i);
}
4.2.3.1.5 Getting the First Record

The following example uses the getFirstRecord() method to get the first record in the "customer" databank:

getDatabank("customer").getFirstRecord();
4.2.3.1.6 Getting the Last Record

The following example uses the getLastRecord() method to get the last record in the "customer" databank:

getDatabank("customer").getLastRecord();

4.2.4 Playing Back Scripts With Iterations

OpenScript allows repetitive playback of navigations in a script. The iterations can be performed with or without databanks.

  1. Start OpenScript.

  2. Open the script to play back.

  3. Configure the script to use a databank as described in Section 4.2.1, "Configuring Databanks".

  4. Select Iterate from the Script menu or click the toolbar button. The resulting dialog box has the following options:

    Use Databanks: When selected, databanks will be used for script playback. Databanks configured for the script show the following settings:

    • Name: Lists the alias name(s) for the databank file(s).

    • Range: Lists the range of databank records to use for script playback. This list corresponds to the Range option selected for each databank file.

    • Start: Lists the starting databank record to use for script playback. This list corresponds to the Starting Record specified for each databank file.

    • Select Record: Lists the how databank records are selected for script playback. This list corresponds to the Select Next Record setting selected for each databank file.

    • When Out of Records: Lists the action to take when the databank file is out of records during script playback. This list corresponds to the When Out of Records setting selected for each databank file.

    • Data: Lists the data in the Starting Record of each databank file.

    Databank Source: This section shows the following information about the selected databank:

    • Alias: Shows the alias name of the selected databank file and the number of rows in the file.

    • Type: Shows the type of the selected databank file. Databanks can be CSV text files or databases.

    • Source: Shows the path and filename of CSV text files or the database query used for database databanks. While there is not a maximum file size, the recommended maximum sizes is 200 MB. The only limitation is how long it takes to generate the index. The databank must be indexable within 30 seconds, by default. This setting is configurable in the "Databank Setup Timeout" setting in the General Preferences.

    Databank Settings: This section specifies the settings to use for the selected databank:

    • Advance to Next Record: Specifies when the virtual user should advance to the next databank record during script playback. The master script being played is always the script that triggers when an iteration occurs. The following options are available:

      • When Script Requests a Record: The databank record advances every time a script explicitly requests a record during script playback. A record request corresponds to the script Java code calling the getDatabank(alias).getNextRecord() method. This is the default behavior.

      • Each Occurance: The databank record advances when a script refers to a databank column (i.e. databank field) in the script. A record request corresponds to the script Java code evaluating a parameterized value such as {{db.fmstocks_data.ticker}}. You can specify that any column advances to the next record or specify a particular databank column advances to the next record. For example, if you have an employee databank with a firstName field and the firstName column is specified as the Column value, the databank record advances only when the {{db.employees.firstName}} value in the script Java code is evaluated on script playback. Select the databank field name as the Column value or select <Any> to allow any field to advance the databank record.

      • Each Iteration of Script: The databank record advances before a script containing the databank starts another playback iteration.

    • Select Next Record: Specifies how a new record is selected from the databank when the databank record advances. The following options are available:

      • Sequentially: The databank records increment one by one in sequential order from the start of the specified range. When multiple virtual users are running, records are distributed in sequential order across all virtual users.

      • Randomly: The databank records are selected at random from the databank. The same record may be used multiple times before all records are exhausted. Random record selection is only provided for databanks that can be indexed. When configuring databank settings, if the databank file is too large to index, the Randomly or Shuffle record options may not be available. The When Out of Records setting does not apply when Random is selected.

      • By Shuffling: The databank records are selected at random from the databank ensuring that once a record is selected, it is never selected again. The setting works similar to selecting a random card from a deck until no cards are left. Shuffle mode only supports databanks containing fewer than 200,000 records. For databanks containing more than 200,000 records, you can shuffle the values in the actual data file or you should use the Randomly mode.

      • Use Seed: Specifies a randomization seed value to use when using the Randomly or Shuffle modes. Use the same seed across multiple tests to create the same sequence of random numbers for all tests. If 0 or not specified, a seed is generated automatically based on the current time.

    • When Out of Records: Specifies the action the virtual user takes if all databank records in the specified range have been used and a new record is requested. The following options are available:

      • Loop Over Range: Loops back to the first record in the range after all records in the range are used and continues distributing records. Use the Maximum Iterations settings to prevent the virtual user from running forever.

      • Keep the Same Record: Continues to use the last record requested after all records in the range are used. No additional records are requested from the databank. Any calls in the Java code to getNextDatabankRecord() are ignored after all records are used. Custom Java code may be used in the script to have Virtual users request an individual record using getRecord(n), getLastRecord(), or getFirstRecord().

      • Stop the User: The virtual user immediately stops running the next time a record is requested from the databank after all records in the range are used. The virtual user will stop regardless of how many iterations are specified by the Maximum Iterations settings.

    • Range: Specifies the range of records to use. The following options are available:

      • All Records: When selected, the virtual user uses all records in the databank. The first record is 1.

      • Specific Records: When selected, the virtual user uses a subset of records in the databank. Specify the first and last records to use for the range. The range includes both the starting and ending record in the specified range.

    • Starting Record: Specifies which databank record to use first. The first record in a databank is 1. The starting record must be within the specified range of records. For example if you select Specific Records and set the range to 5:10, the starting record must be at least 5, but not more than 10.

    Maximum Iterations: This section specifies the maximum number of iterations of a main script's run() section to complete:

    • Run no more than [ ] iterations: Specifies the maximum number of iterations. If a databank exhausts all records and When Out of Records specifies Stop the User, the virtual user will always stop running, even if the specified number of iterations has not completed.

  5. Select Use Databanks.

  6. Select which databank file to specify the settings for if more than one database is configured for the script.

  7. Specify the settings for the databank file.

  8. Select the Run no more than [ ] iterations option and set the iteration count to the desired number of playback iterations.

  9. Click OK.

    You can view the progress of the script playback in the Console View. You can review the results of script playback in the Results View.

4.2.4.1 Notes and Limitations

Certain setting combinations are not allowed, or may cause exceptions when the script is run. The following are situations to be aware of when using iteration options.

  1. The When Out of Records option is not available when Select Next Record is set to Randomly. When random is selected, an infinite supply of random records exists.

  2. Virtual users may still request an individual record using getRecord(n) after all records are used up, and When Out of Records is set to Keep the Same Record.

  3. The getRecord(n), getFirstRecord(), and getLastRecord() Java code methods do not advance the record cursor used by getNextDatabankRecord(). Therefore:

    getNextDatabankRecord();// returns 1
    getRecord(7);// returns 7
    getNextDatabankRecord();//returns 2, not 7
    
  4. The getRecord(n), getFirstRecord(), and getLastRecord() Java code methods throw an exception when they are invoked if Select Next Record is set to Shuffle Records or Randomly.

  5. The getRecord(n), getFirstRecord(), and getLastRecord() Java code methods throw an exception if they are invoked and the databank is not indexed.

  6. Use Seed is only available when Select Next Record is set to Shuffle Records or Randomly.

  7. A specific databank range and starting index may not be set if the databank cannot be indexed.

  8. The Select Next Record: Shuffle Records and Randomly options are only allowed when the databank can be indexed.

  9. The Select Next Record: Shuffle Records is only allowed when the databank can be indexed and when there are fewer than 200,000 records.

4.2.4.2 Using Very Large Databanks

If you want to use an extremely large databank (for example, records in the millions), use the follow procedure:

  1. Make sure the script does not use these databanking methods in the script code:

    getRecordNumber() 
    getFirst() 
    getLast() 
    getRecord(n)
    
  2. Set the Databank Setup Timeout to a very small value, for example, 1 second.

    1. Select OpenScript Preferences from the View menu.

    2. Select General in the preferences.

    3. Set Databank Setup Timeout to 1 (sec).

  3. Set the databank setting to Sequential mode.

    1. Select Iterate from the Script menu.

    2. Make sure Select Next Record is set to "Sequentially".

  4. Save the script and playback in Oracle Load Testing. The index preparation will timeout (expected). Although the index will not be generated, Oracle Load Testing will still be able to run with the databank using Sequential mode and deliver records to Virtual users.

4.3 Using Data Tables

The Data Table is a spreadsheet-like data table for Functional testing scripts. There are two versions of the Data Table: the view/edit Data Table and the runtime Data Table. The view/edit Data Table can be accessed using the Data Table option on the View menu. The view/edit Data Table tab appears in the tab views of the OpenScript main window. The view/edit Data Table content can be changed by manually inputting data into cells or by importing an Excel file before playback.

The Data Table API provides methods for accessing the data in the Data Table programmatically within functional test scripts during playback of a script.

When you play back a script and select the results in the Results view, the Details view includes a Result Data Table tab that shows the runtime Data Table resulting from the playback of the script. Changes made to the runtime Data Table during script playback do not appear in the view/edit Data Table. The Result Data Table can be exported to an Excel file to view the changes.

The following sections explain how to use Data Tables within functional test scripts.

4.3.1 Enabling the Data Table Service

The Data Table Service provides programmatic access to data stored in a Data Table using the Data Table API. The Data Table service must be enabled to provide access to the Data Table view and the runtime Data Table.

To enable the Data Table Service:

  1. Record a functional test script.

  2. Select Script Properties from the Script menu.

  3. Select Modules.

  4. Select the Data Table option and click OK.

  5. Select Data Table from the View menu to show the view/edit Data Table tab view. The runtime Data Table is accessed usingthe Data Table API.

4.3.2 Entering Data Manually

Data can be entered into the view/edit Data Table manually by editing individual cell contents. You can right-click on a data table cell to open the shortcut Edit menu.

To enter data into the view/edit Data Table manually:

  1. Enable the Data Table service for the functional test script and show the Data Table view.

  2. Right-click on the table cell and select from the shortcut menu. The Data Table right-click edit menu can be used to add data to the Data Table manually. The following options are available on the shortcut menu:

    • Edit: Changes the selected cell to text edit mode. Type data into the cell and press Enter.

    • Cut: Cuts the data from the selected cell.

    • Copy: Copies the text for the selected cell to the clipboard.

    • Paste: Pastes text from the clipboard to the selected cell.

    • Delete: Deletes the text from the selected cell.

    • Insert Row Before: Inserts a new row into the table before the selected row.

    • Insert Row After: Inserts a new row into the table after the selected row.

    • Delete Row: Deletes the selected row from the table.

    • Insert Column Before: Inserts a new column into the table before the selected column.

    • Insert Column After: Inserts a new column into the table after the selected column.

    • Rename Column: Opens a dialog box for specifying a new heading name for the selected column.

    • Delete Column: Deletes the selected column from the table.

  3. Type the data into the table cell and press Enter.

Additional worksheets can be added to the Data Table or removed from the Data Table using the options on the worksheet shortcut menu.

To add worksheets to or remove worksheets from a Data Table:

  1. Right-click on a worksheet tab in the Data Table view to open the worksheet shortcut menu. The Data Table right-click worksheet menu can be used to add worksheets to and remove worksheets from the Data Table manually. The worksheet shortcut menu has the following options:

    • Insert Sheet Before: Inserts a worksheet tab into the Data Table before the selected worksheet tab.

    • Insert Sheet After: Inserts a worksheet tab into the Data Table after the selected worksheet tab.

    • Rename Sheet: Opens a dialog box for specifying a new name for the selected worksheet tab.

    • Delete Sheet: Deletes the selected worksheet from the Data Table. A confirmation dialog box appears to confirm the deletion.

  2. Use the worksheet shortcut menu options as needed to add or remove worksheets in the Data Table.

4.3.3 Importing Data from a Spreadsheet File

Data can be loaded into a Data Table from an Excel spreadsheet file.

To load data from an Excel spreadsheet file:

  1. Enable the Data Table service for the functional test script and show the Data Table view.

  2. Click the Import toolbar button in the Data Table View. The Data Table Import dialog box has the following options:

    File: Specifies the name of the file to import. Click the Browse button to select a file.

    Sheets: Lists the worksheets in the selected spreadsheet file. Select or clear the check boxes to specify which worksheets to import.

    Overwrite existing sheet: When selected, existing worksheets with the same name will be overwritten in the Data Table.

    Use first row as column name: When selected the first row of the imported spreadsheet file is used as the column name, replacing the default column names.

  3. Enter or select the file to import.

  4. Select the worksheets to import.

  5. Select or clear the Overwrite and Use first row options.

  6. Click OK.

4.3.4 Exporting Data to a Spreadsheet File

Data in a Data Table can be exported to an Excel spreadsheet file.

To Export data from a Data Table to an spreadsheet file:

  1. Enable the Data Table service for the functional test script and show the Data Table view.

  2. Click the Export toolbar button in the Data Table View. The Export Data Table to Excel document dialog box has the following options:

    Sheets: Lists the worksheets in the Data Table. Select or clear the check boxes to specify which worksheets to export.

    File: Specifies the name of the file to import. Click the Browse button to specify the name and location to save the file.

    Full path: Shows the full path and file name where the Data Table data will be exported.

  3. Specify a file name or use the Browse button to save a new file.

  4. Click OK.

4.3.5 Changing Data During Script Playback

The runtime Data Table content can be changed during script playback in the following ways:

  • manually when playback is paused by a breakpoint

  • manually when playback is paused by an exception

  • manually when playback is paused using the Pause toolbar button

  • programmatically at runtime using the datatable API

Changes to the runtime Data Table can be saved can be saved to the script's session result folder during script playback using the datatable.save method. The view/edit Data Table and Result Data Table can be exported to an Excel file.

The following sections provide examples of how to change data in the runtime Data Table programmatically using the datatable API.

4.3.5.1 Getting and Setting Cell Values

You can use the datatable API to get and set Data Table values programmatically during playback of a script. The following examples show how to get and set values using the datatable API getValue() and setValue() methods.

4.3.5.1.1 Getting Data by Row and Column Value

The following example script code retrieves the value in the cell at row 1, column A of the current worksheet and prints the value to the Results view:

Object cellValue1 = datatable.getValue(1, "A");
info("cell value = " + cellValue1.toString());
4.3.5.1.2 Getting Data by Sheet, Row, and Column Value

The following example script code retrieves the value in the cell at row 1, column A of the worksheet named "Sheet1" and prints the value to the Results view:

Object cellValue2 = datatable.getValue("Sheet1", 1, "A");
info("cell value = " + cellValue2.toString());
4.3.5.1.3 Setting Data by Row and Column Value

The following example script code sets the value in the cell at row 1, column A to a boolean value of true:

datatable.setValue(1, "A", true);

The following example script code sets the value in the cell at row 1, column A to a double value of 10.5:

datatable.setValue(1, "A", 10.5);

The following example script code sets the value in the cell at row 1, column A to a String value of myString:

datatable.setValue(1, "A", "myString");
4.3.5.1.4 Setting Data by Sheet, Row, and Column Value

The following example script code sets the value in the cell at row 1, column A of "Sheet1" to a boolean value of true:

datatable.setValue("Sheet1", 1, "A", true);

The following example script code sets the value in the cell at row 1, column A of "Sheet1" to a double value of 10.5:

datatable.setValue("Sheet1", 1, "A", 10.5);

The following example script code sets the value in the cell at row 1, column A of "Sheet1" to a String value of myString:

datatable.setValue("Sheet1", 1, "A", "myString");

4.3.5.2 Adding and Deleting Rows and Columns

You can use the datatable API to add and delete Data Table rows and columns programmatically during playback of a script. The following examples show how to add and delete rows and columns using the datatable API addColumn(), deleteColumn(), insertRow(), and deleteRow() methods.

4.3.5.2.1 Adding Columns

The following example script code adds a new column named New Column to the current worksheet after the last column in the worksheet:

datatable.addColumn("New Column");

The following example script code adds a new column named New Column to the current worksheet before the column with an index value of 0:

datatable.addColumn("New Column", 0);

The following example script code adds a new column named New Column to the worksheet named "Sheet1" after the last column in the worksheet:

datatable.addColumn("Sheet1", "New Column");

The following example script code adds a new column named New Column to the worksheet named "Sheet1" before the column with an index value of 0:

datatable.addColumn("Sheet1", "New Column", 0);
4.3.5.2.2 Deleting Columns

The following example script code deletes the column named A from the current worksheet:

datatable.deleteColumn("A");

The following example script code deletes the column named A from the current worksheet named "Sheet1":

datatable.deleteColumn("Sheet1", "A");
4.3.5.2.3 Adding Rows

The following example script code adds a new row to the current worksheet before the row with an index value of 0:

datatable.insertRow(0);

The following example script code adds a new row to the worksheet named "Sheet1" before the row with an index value of 0:

datatable.insertRow("Sheet1", 0);
4.3.5.2.4 Deleting Rows

The following example script code deletes the row before the row with an index value of 1 from the worksheet named "Sheet1":

datatable.deleteRow("Sheet1", 1);

4.3.5.3 Adding and Deleting Worksheets

You can use the datatable API to add and delete worksheets programmatically during playback of a script. The following examples show how to add and delete worksheets using the datatable API addSheet() and deleteSheet() methods.

4.3.5.3.1 Adding Worksheets

The following example script code adds a new worksheet named "Sheet1" to the Data Table:

datatable.addSheet("Sheet1");

The following example script code adds a new worksheet named "Sheet1" to the Data Table before "Sheet2":

datatable.addSheet("Sheet1", "Sheet2");
4.3.5.3.2 Deleting Worksheets

The following example script code deletes the worksheet named "Sheet1" from the Data Table:

datatable.deleteSheet("Sheet1");

4.3.5.4 Getting Worksheet, Row, and Column Counts

You can use the datatable API to get sheet, row, and column counts programmatically during playback of a script. The following examples show how to get sheet, row, and column counts using the datatable API getSheetCount(), getRowCount(), and getColumnCount() methods.

4.3.5.4.1 Getting Worksheet Counts

The following example script code gets the sheet count from the Data Table and prints the value to the Results view:

int sheetCount = datatable.getSheetCount();
info("Sheet count = " + sheetCount);
4.3.5.4.2 Getting Row Counts

The following example script code gets the row count from the current worksheet and prints the value to the Results view:

int rowCount = datatable.getRowCount();
info("row count = " + rowCount);

The following example script code gets the row count from the worksheet named "Sheet1" and prints the value to the Results view:

int rowCount1 = datatable.getRowCount("Sheet1");
info("row count Sheet1 = " + rowCount1);
4.3.5.4.3 Getting Column Counts

The following example script code gets the column count from the worksheet named "Sheet1" and prints the value to the Results view:

int columnCount = datatable.getColumnCount("Sheet1");
info("column count Sheet1 = " +columnCount);

The following example script code gets the column count from the worksheet with an index of 0 and prints the value to the Results view:

int columCount0 = datatable.getColumnCount(0);
info("column count Sheet index 0 = " + columnCount);

4.3.5.5 Getting the Current Sheet and Row

You can use the datatable API to get the current sheet, row, and column programmatically during playback of a script. The following examples show how to get the current sheet, row, and column using the datatable API getCurrentSheet() and getCurrentRow() methods.

4.3.5.5.1 Getting the Current Sheet

The following example script code gets the name of the current sheet from the Data Table and prints the value to the Results view:

String currentSheet = datatable.getCurrentSheet();
info("Current Sheet = " + currentSheet)
4.3.5.5.2 Getting the Current Row

The following example script code gets the current row from the current worksheet and prints the value to the Results view:

int currentRow = datatable.getCurrentRow();
info("Current row = " + currentRow);

4.3.5.6 Setting Next and Previous Rows

You can use the datatable API to set the next and previous row programmatically during playback of a script. The following examples show how to set the next and previous rows using the datatable API setNextRow() and getCurrentRow() methods.

4.3.5.6.1 Setting the Next Row

The following example script code sets the next row of the current sheet in the Data Table:

datatable.setNextRow();
4.3.5.6.2 Setting the Previous Row

The following example script code sets the previous row of the current sheet in the Data Table:

datatable.setPreviousRow();

4.3.5.7 Importing and Exporting Documents and Sheets

You can use the datatable API to get the import and export spreadsheet documents and worksheets programmatically during playback of a script. The following examples show how to import and export spreadsheet files and worksheets using the datatable API importExcel(), importSheet(), exportToExcel() and exportSheet() methods.

4.3.5.7.1 Importing an Excel Spreadsheet Document

The following example script code imports the myXls.xls Excel spreadsheet document into the Data Table:

datatable.importExcel("c:\\myXls.xls");
4.3.5.7.2 Importing Worksheets

The following example script code imports the single worksheet named "SourceSheet" from the myXls.xls Excel spreadsheet document and adds it to the Data Table with the name "DestinationSheet":

datatable.importSheet("c:\\myXls.xls", "SourceSheet", "DestinationSheet");

The following example script code imports all worksheet from the myXls.xls Excel spreadsheet document and adds them to the Data Table overwriting any sheets with the same names:

datatable.importSheets("c:\\myXls.xls", true);

The following example script code imports the specified list of worksheets from the myXls.xls Excel spreadsheet document and adds them to the Data Table overwriting any sheets with the same names:

import java.util.List;
import java.util.ArrayList;
//[...]
List<String> sheetList = new ArrayList<String>();
sheetList.add("Sheet1");
sheetList.add("Sheet2");
datatable.importSheets("c:\\myXls.xls", sheetList, true);

The following example script code imports the specified list of worksheets from the myXls.xls Excel spreadsheet document and adds them to the Data Table overwriting any sheets with the same names using the first row:

import java.util.List;
import java.util.ArrayList;
//[...]
List<String> sheetList = new ArrayList<String>();
sheetList.add("Sheet1");
sheetList.add("Sheet2");
datatable.importSheets("c:\\myXls.xls", sheetList, true, true);
4.3.5.7.3 Exporting an Excel Spreadsheet Document

The following example script code exports the myXls.xls Excel spreadsheet document from the Data Table to a file:

datatable.exportToExcel("c:\\myXls.xls");
4.3.5.7.4 Exporting Worksheets

The following example script code exports the single worksheet named "SourceSheet" from the Data Table to the myXls.xls Excel spreadsheet document with the name "DestinationSheet":

datatable.exportSheet("c:\\myXls.xls", "SourceSheet", "DestinationSheet");

The following example script code imports all worksheet from the myXls.xls Excel spreadsheet document and adds them to the Data Table overwriting any sheets with the same names:

datatable.importSheets("c:\\myXls.xls", true);

The following example script code exports the specified list of worksheets from the Data Table to the myXls.xls Excel spreadsheet document:

import java.util.List;
import java.util.ArrayList;
//[...]
List<String> sheetList = new ArrayList<String>();
sheetList.add("Sheet1");
sheetList.add("Sheet2");
datatable.exportSheets("c:\\myXls.xls", sheetList);

4.3.5.8 Using Data Tables with Parent and Child Scripts

You can use the datatable API to change data in the Data Table of a parent script that runs a child script programmatically during playback of parent and child scripts. The following examples show how to change data in parent scripts from child scripts using the datatable API getParentDatatable() and getGlobalDatatable() methods.

4.3.5.8.1 Accessing the Parent Data Table from a Child Script

The following example script code shows how a child script can access and change data in the Data Table of the parent script. Both the parent and child scripts must have the Data Table service enabled. It is important that it always should be verified that the return value is not null. In the child script, make sure the Data Table service is enabled and create a parent Data Table instance:

import oracle.oats.scripting.modules.datatable.api.DataTableService;
//[...]
DataTableService parentDatatable = datatable.getParentDatatable();
  if(parentDatatable != null) 
   {
      info("set parent datatable value");
      parentDatatable.setValue(0,"A", 30);
      parentDatatable.save(); 
   }

In the parent script, run the child script using the getScript("alias").run() method:

getScript("childScript").run(1);

The Data Table results appear in the parent script Results and the Results Data Table of the Details view.

4.3.5.8.2 Accessing the Top-Most Data Table in Chain of Parent Scripts

The following example script code shows how a child script can access and change data in the top-most Data Table in a chain of parent scripts. All scripts in the chain must have the Data Table service enabled. In the child script, make sure the Data Table service is enabled and create a global Data Table instance:

import oracle.oats.scripting.modules.datatable.api.DataTableService;
//[...]
DataTableService globalDatatable = datatable.getGlobalDatatable(); 
info("set global datatable value"); 
globalDatatable.setValue(0, "B", 20);
globalDatatable.save();

If the script containing this code is run as a stand alone script the return value is a datatable of the script itself.