View RSS Feed


Understanding JMeter Element- JDBC Request Sampler

Rate this Entry
by , 06-21-2015 at 01:46 AM (1975 Views)
Understanding JMeter Element- JDBC Request Sampler

This sampler lets you send an JDBC Request (an SQL query) to a database.
Before using this you need to set up a JDBC Connection Configuration Configuration element
If the Variable Names list is provided, then for each row returned by a Select statement, the variables are set up with the value of the corresponding column (if a variable name is provided), and the count of rows is also set up. For example, if the Select statement returns 2 rows of 3 columns, and the variable list is A,,C, then the following variables will be set up:
A_#=2 (number of rows)
A_1=column 1, row 1
A_2=column 1, row 2
C_#=2 (number of rows)
C_1=column 3, row 1
C_2=column 3, row 2

If the Select statement returns zero rows, then the A_# and C_# variables would be set to 0, and no other variables would be set.

Old variables are cleared if necessary - e.g. if the first select retrieves 6 rows and a second select returns only 3 rows, the additional variables for rows 4, 5 and 6 will be removed.

Name- Descriptive name for this sampler that is shown in the tree.
Variable Name- Name of the JMeter variable that the connection pool is bound to. This must agree with the 'Variable Name' field of a JDBC Connection Configuration.

Query Type- Set this according to the statement type:
Select Statement
Update Statement - use this for Inserts and Deletes as well
Callable Statement
Prepared Select Statement
Prepared Update Statement - use this for Inserts and Deletes as well
Edit - this should be a variable reference that evaluates to one of the above

When "Prepared Select Statement", "Prepared Update Statement" or "Callable Statement" types are selected, a Statement Cache per connection is used by JDBC Request. It stores by default up to 100 PreparedStatements per connection, this can impact your database (Open Cursors).
SQL Query- SQL query. Do not enter a trailing semi-colon. There is generally no need to use { and } to enclose Callable statements; however they may be used if the database uses a non-standard syntax. [The JDBC driver automatically converts the statement if necessary when it is enclosed in {}]. For example:
select * from t_customers where id=23
CALL SYSCS_UTIL.SYSCS_EXPORT_TABLE (null,?, ?, null, null, null)
Parameter values: tablename,filename
Parameter types: VARCHAR,VARCHAR
The second example assumes you are using Apache Derby.

Parameter values- Comma-separated list of parameter values. Use ]NULL[ to indicate a NULL parameter. (If required, the null string can be changed by defining the property "jdbcsampler.nullmarker".)
The list must be enclosed in double-quotes if any of the values contain a comma or double-quote, and any embedded double-quotes must be doubled-up, for example:
"Dbl-Quote: "" and Comma: ,"

There must be as many values as there are placeholders in the statement even if your parameters are OUT ones, be sure to set a value even if the value will not be used (for example in a CallableStatement).
Parameter types- Comma-separated list of SQL parameter types (e.g. INTEGER, DATE, VARCHAR, DOUBLE) or integer values of Constants when for example you use custom database types proposed by driver (-10 for OracleTypes.CURSOR for example).
These are defined as fields in the class java.sql.Types.

Variable Names- Comma-separated list of variable names to hold values returned by Select statements, Prepared Select Statements or CallableStatement. Note that when used with CallableStatement, list of variables must be in the same sequence as the OUT parameters returned by the call. If there are less variable names than OUT parameters only as many results shall be stored in the thread-context variables as variable names were supplied. If more variable names than OUT parameters exist, the additional variables will be ignored.

Result Variable Name- If specified, this will create an Object variable containing a list of row maps. Each map contains the column name as the key and the column data as the value. Usage:
columnValue = vars.getObject("resultObject").get(0).get("Column Name");
Handle ResultSet- Defines how ResultSet returned from callable statements be handled:
Store As String (default) - All variables on Variable Names list are stored as strings, will not iterate through a ResultSets when present on the list.
Store As Object - Variables of ResultSet type on Variables Names list will be stored as Object and can be accessed in subsequent tests/scripts and iterated, will not iterate through the ResultSet
Count Records - Variables of ResultSet types will be iterated through showing the count of records as result. Variables will be stored as Strings.

Disclaimer: The article/post is posted with the purpose of sharing knowledge and information.
The article may contain references, extract or content from other informative sources.
Researched/Authored/Compiled by -
Ronak Shah
Practice Head - Software Testing (QA), CIGNEX Datamatics

vBulletin Optimisation provided by vB Optimise v2.6.0 Beta 4 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
User Alert System provided by Advanced User Tagging v3.0.9 (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Questions / Answers Form provided by vBAnswers (Pro) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
vBNominatevBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
Username Changing provided by Username Change (Free) - vBulletin Mods & Addons Copyright © 2016 DragonByte Technologies Ltd.
BetaSoft Inc.
Digital Point modules: Sphinx-based search
All times are GMT -8. The time now is 12:45 PM.

Copyright BetaSoft Inc.