SQL

Overview

This action executes SQL statements. It enables you to perform administrative tasks like cleaning a database after a test. It also allows you to get business-related metrics that you can monitor, for example the number of items in a work queue or the number of checkouts during the last minute.

Type Advanced Action
Version 1.0.1
Maturity Stable
Author Neotys
License BSD Simplified
NeoLoad 5.0 (Ultimate, Enterprise or Professional Edition w/ Integration & Advanced Usage option required)
NeoSense Compatible
Requirements None
Bundled in NeoLoad Bundled in NeoLoad 5.1
Download

For NeoLoad 5.0: Jar, Sources

For NeoLoad 5.1+: Bundled in NeoLoad: see the documentation

Warning: because this action first connects to the database, executes the SQL statement then disconnects, it is not suitable for database load testing.

The action produces XML output that includes a status of the executed SQL statement as well as an optional section containing the results of the query. The query results are also stored in NeoLoad variables.

Tip

Use the Advanced Actions Store External Data Entry or Store External Data Entries to store and analyze the retrieved data in NeoLoad.

Parameters

  • type: The type of SQL statement, possible values are QUERY and UPDATE.
  • connectionURL: The JDBC connection URL (jdbc:mysql://localhost:3306/).
  • connection.user (optional): The user name to connect the database.
  • connection.password (optional): The password of the user.
  • connection.<propertyName> (optional): Any property that can be used to customize your connection.
  • sqlStatement: The SQL statement to execute. Must be on one single line with NeoLoad 5.0, multi-line allowed with NeoLoad 5.1+.
  • variableName (optional): The NeoLoad variable to put the value(s) in. More information in Variables section.
  • driverClassName (optional): The fully qualified name of your custom driver class. If you are using Oracle, MS SQL Server, MySQL, PostgreSQL, you don’t need to use this parameter as these drivers are embedded in NeoLoad .
  • includeQueryResults (optional): Default value is true. If set at false, the results are not included in the XML response. Should be “false” to optimize resource consumption when executing a query that returns multiple rows and retrieving the values from the variables.
  • batchSize (optional): The batch number to keep in memory before performing a batch update.

Variables

When executing a query, be careful to specify the variable name, i.e “sqlEntries” and not “${sqlEntries}”.

Notes:“columnName” is the column name as defined in the database. Invalid XML characters are removed, for example count(*) would be renamed to count.

The query result values will be retrieved in the following variables:

  • variableName for value in first row, first column
  • variableName_[columnName] for the first row
  • variableName_[columnName]_[row] when several rows are expected
  • Both variableName_[columnName]_count (recommended) and variableName_[columnName]_matchNr return the number of rows.
  • variableName_[columnName]_rand returns a random value from the column specified
  • variableName_[columnName] can be used as an input for the Store External Data Entries Action in order to store all results values of a column in the Results data.

Status Codes

  • NL-SQL-ACTION-01: There was an issue parsing the parameters.
  • NL-SQL-ACTION-02: The type of SQL statement is invalid.
  • NL-SQL-ACTION-03: There was an error while creating the connection.
  • NL-SQL-ACTION-04: There was an error while executing statements.

Examples

  • An example of removing all lines of a table
    • type: UPDATE
    • connectionURL: jdbc:oracle:thin:@host:1521:ORCL
    • connection.user: my_user_name
    • connection.password: my_password
    • sqlStatement: DELETE FROM table_name

  • An example of getting number of rows
    • type: QUERY
    • connectionURL: jdbc:mysql://host:3306/mysql
    • connection.user: my_user_name
    • connection.password: my_password
    • sqlStatement: SELECT count(*) FROM table_name
    • variableName: countTableName
    • In this example, since the query will return one row of one column, only the countTableName variable will be created.

  • An example of SELECT with a custom driver
    • type: QUERY
    • connectionURL: jdbc:db2://host:50000/db
    • connection.user: my_user_name
    • connection.password: my_password
    • sqlStatement: SELECT * FROM records WHERE date=’2014-11-11′
    • variableName: records
    • driverClassName: com.ibm.db2.jcc.DB2Driver
    • In this example, the following multi-valued variables will be created : records_id and records_date records_id_1 gives access to the id of the first row, records_id_count and records_id_matchNr give access to the number of rows, records_date_rand gives access to the date of a random row.

Changelog

  • 1.0.1 – Better management of binary data content