I'm using a JSR223 Sampler (Groovy) in JMeter to read an SQL query from a file and process it before execution. The file contains key-value pairs in a semicolon-separated format, where the query key holds the SQL statement, and other keys define placeholder values.
After trying several ways of trying to pass sql queries as param from bash script I ran into several string escape issues that were hard to debug. Following methods works better as it is java syntax and works much better.
This is done by adding JSR223 preprocessor element into your thread group.
import .apache.jmeter.threads.JMeterContextService
import .apache.jmeter.threads.JMeterVariables
import java.nio.file.*
import .apache.jmeter.util.JMeterUtils
import java.text.SimpleDateFormat
import java.nio.file.Paths
// Function to clean SQL query by replacing newlines with spaces and removing extra spaces
String cleanQuery(String query) {
return query.replaceAll("[\r\n]+", " ").replaceAll("\s+", " ").trim()
}
// Function to substitute placeholders in the query with provided values
String substitutePlaceholders(String query, Map<String, String> properties) {
properties.each { key, value ->
if (key != "query") { // Skip the query key itself
query = query.replace("\$" + key, value) // Simple string replacement instead of regex
}
}
return query
}
It can read jmeter params and variables and also set them for later use
// Function to generate jtl results filename
String generateResultsFilename(String sqlFilePath) {
// Get current timestamp in the format hhmmss_MMDDYYYY
String timestamp = new SimpleDateFormat("HHmmss_MMddyyyy").format(new Date())
if (sqlFilePath == null || sqlFilePath.trim().isEmpty()) {
throw new IllegalArgumentException("SQL file path is empty or not provided.")
}
// Extract only the filename (without path)
String fileName = Paths.get(sqlFilePath).getFileName().toString()
String pathName = Paths.get(sqlFilePath).getParent().toString();
// replace file extension
String baseName = fileName.replaceAll(/\.[^.]*$/, ".jtl")
// Construct the new filename
return pathName + "\\" + timestamp + "_" + baseName
}
// Retrieve the file name parameter from JMeter properties
String fileName = JMeterUtils.getPropDefault("SQL_FILE", "")
if (fileName == null || fileName.trim().isEmpty()) {
throw new IllegalArgumentException("SQL file name is not provided in JMeter properties under 'SQL_FILE'")
}
try {
// Read the properties from the file
String fileContent = new String(Files.readAllBytes(Paths.get(fileName)), "UTF-8").trim()
// Parse the properties from semicolon-separated format
Map<String, String> properties = fileContent.split(";").collectEntries { entry ->
def parts = entry.split("=", 2)
parts.length == 2 ? [(parts[0].trim()): parts[1].trim()] : [:]
}
// Extract the query property
String sqlQuery = properties.get("sqlQuery", "").trim()
if (sqlQuery.isEmpty()) {
throw new IllegalArgumentException("No 'query' property found in the SQL file: " + fileName)
}
//
String p1 = properties.get("p1", "").trim()
String p2 = properties.get("p2", "").trim()
//
log.info("raw query=" + sqlQuery)
log.info("param p1=" + p1)
log.info("param p2=" + p2)
// Clean the query
sqlQuery = cleanQuery(sqlQuery)
log.info("cleaned query=" + sqlQuery)
// Substitute placeholders with values
sqlQuery = substitutePlaceholders(sqlQuery, properties)
log.info("parametrized query=" + sqlQuery)
// substitute param placeholder with actual parameters
// show final query
log.info("Final SQL query: " + sqlQuery)
// Store the query in a JMeter variable for later use
vars.put("SQL_QUERY", sqlQuery)
log.info("SQL query successfully loaded and cleaned from file: " + fileName)
// create name for results file
String resultsFile = generateResultsFilename(fileName)
// Store it in a JMeter variable
vars.put("RESULTS_FILE", resultsFile)
log.info("JTL Results weill be stored file: " + resultsFile)
} catch (Exception e) {
log.error("Error processing SQL file: " + fileName, e)
throw new RuntimeException("Failed to process SQL file: " + fileName, e)
}
My Requirements:
Read the SQL file path from a JMeter property (e.g., SQL_FILE defined in user.properties).
Extract the query property and perform placeholder substitution with the remaining properties.
Ensure formatting is correct:
Replace newlines with spaces.
Convert multiple spaces into a single space.
Perform variable substitution (e.g., $name → John Doe).
Generate a formatted SQL query and store it in a JMeter variable (SQL_QUERY). Generate a unique filename based on the original SQL file name (without the extension) and a timestamp (hhmmss_MMDDYYYY).
Current Issues:
The variable substitution is not working as expected. Placeholders like $name are not getting replaced. The file name includes the full path, and I only need the base file name without the extension.
I'm using a JSR223 Sampler (Groovy) in JMeter to read an SQL query from a file and process it before execution. The file contains key-value pairs in a semicolon-separated format, where the query key holds the SQL statement, and other keys define placeholder values.
After trying several ways of trying to pass sql queries as param from bash script I ran into several string escape issues that were hard to debug. Following methods works better as it is java syntax and works much better.
This is done by adding JSR223 preprocessor element into your thread group.
import .apache.jmeter.threads.JMeterContextService
import .apache.jmeter.threads.JMeterVariables
import java.nio.file.*
import .apache.jmeter.util.JMeterUtils
import java.text.SimpleDateFormat
import java.nio.file.Paths
// Function to clean SQL query by replacing newlines with spaces and removing extra spaces
String cleanQuery(String query) {
return query.replaceAll("[\r\n]+", " ").replaceAll("\s+", " ").trim()
}
// Function to substitute placeholders in the query with provided values
String substitutePlaceholders(String query, Map<String, String> properties) {
properties.each { key, value ->
if (key != "query") { // Skip the query key itself
query = query.replace("\$" + key, value) // Simple string replacement instead of regex
}
}
return query
}
It can read jmeter params and variables and also set them for later use
// Function to generate jtl results filename
String generateResultsFilename(String sqlFilePath) {
// Get current timestamp in the format hhmmss_MMDDYYYY
String timestamp = new SimpleDateFormat("HHmmss_MMddyyyy").format(new Date())
if (sqlFilePath == null || sqlFilePath.trim().isEmpty()) {
throw new IllegalArgumentException("SQL file path is empty or not provided.")
}
// Extract only the filename (without path)
String fileName = Paths.get(sqlFilePath).getFileName().toString()
String pathName = Paths.get(sqlFilePath).getParent().toString();
// replace file extension
String baseName = fileName.replaceAll(/\.[^.]*$/, ".jtl")
// Construct the new filename
return pathName + "\\" + timestamp + "_" + baseName
}
// Retrieve the file name parameter from JMeter properties
String fileName = JMeterUtils.getPropDefault("SQL_FILE", "")
if (fileName == null || fileName.trim().isEmpty()) {
throw new IllegalArgumentException("SQL file name is not provided in JMeter properties under 'SQL_FILE'")
}
try {
// Read the properties from the file
String fileContent = new String(Files.readAllBytes(Paths.get(fileName)), "UTF-8").trim()
// Parse the properties from semicolon-separated format
Map<String, String> properties = fileContent.split(";").collectEntries { entry ->
def parts = entry.split("=", 2)
parts.length == 2 ? [(parts[0].trim()): parts[1].trim()] : [:]
}
// Extract the query property
String sqlQuery = properties.get("sqlQuery", "").trim()
if (sqlQuery.isEmpty()) {
throw new IllegalArgumentException("No 'query' property found in the SQL file: " + fileName)
}
//
String p1 = properties.get("p1", "").trim()
String p2 = properties.get("p2", "").trim()
//
log.info("raw query=" + sqlQuery)
log.info("param p1=" + p1)
log.info("param p2=" + p2)
// Clean the query
sqlQuery = cleanQuery(sqlQuery)
log.info("cleaned query=" + sqlQuery)
// Substitute placeholders with values
sqlQuery = substitutePlaceholders(sqlQuery, properties)
log.info("parametrized query=" + sqlQuery)
// substitute param placeholder with actual parameters
// show final query
log.info("Final SQL query: " + sqlQuery)
// Store the query in a JMeter variable for later use
vars.put("SQL_QUERY", sqlQuery)
log.info("SQL query successfully loaded and cleaned from file: " + fileName)
// create name for results file
String resultsFile = generateResultsFilename(fileName)
// Store it in a JMeter variable
vars.put("RESULTS_FILE", resultsFile)
log.info("JTL Results weill be stored file: " + resultsFile)
} catch (Exception e) {
log.error("Error processing SQL file: " + fileName, e)
throw new RuntimeException("Failed to process SQL file: " + fileName, e)
}
My Requirements:
Read the SQL file path from a JMeter property (e.g., SQL_FILE defined in user.properties).
Extract the query property and perform placeholder substitution with the remaining properties.
Ensure formatting is correct:
Replace newlines with spaces.
Convert multiple spaces into a single space.
Perform variable substitution (e.g., $name → John Doe).
Generate a formatted SQL query and store it in a JMeter variable (SQL_QUERY). Generate a unique filename based on the original SQL file name (without the extension) and a timestamp (hhmmss_MMDDYYYY).
Current Issues:
The variable substitution is not working as expected. Placeholders like $name are not getting replaced. The file name includes the full path, and I only need the base file name without the extension.
Share Improve this question asked Mar 12 at 23:00 Gajendra SheteGajendra Shete 112 bronze badges2 Answers
Reset to default 0In order to evaluate a JMeter Variable (or Function) and get the real value instead of placeholder use CompoundVariable class like:
new .apache.jmeter.engine.util.CompoundVariable(your-variable-here).execute()In order to get the filename from the full path you can use FilenameUtils class like:
.apachemons.io.FilenameUtils.getName(your-path-to-file)
More information on Groovy scripting in JMeter: Apache Groovy: What Is Groovy Used For?
You can also use ksh script to feed input files to jmeter. Simple example would be like this
import java.nio.file.*
import java.text.SimpleDateFormat
import java.util.concurrent.ConcurrentHashMap
import java.util.concurrent.CopyOnWriteArrayList
// Get current timestamp for the filename
def timestamp = new SimpleDateFormat("yyyyMMdd_HHmmss").format(new Date())
def filePath = Paths.get("summary_${timestamp}.csv")
// Check if the CSV file exists, if not, write the header row
if (!Files.exists(filePath)) {
def header = "Test Start Time,Test End Time,Elapsed Duration (sec),Total Requests,Total Response Time (ms),Average Response Time (ms),Min Response Time (ms),Max Response Time (ms),Standard Deviation (ms),Success Count,Failure Count,Success Rate (%),Failure Rate (%),Throughput (requests/sec)\n"
Files.write(filePath, header.getBytes(), StandardOpenOption.CREATE)
}
// Retrieve or initialize a shared map for aggregation using JMeter properties
def globalResults = props.get("aggregateResults") as ConcurrentHashMap
if (globalResults == null) {
globalResults = new ConcurrentHashMap()
globalResults.put("responseTimes", new CopyOnWriteArrayList<Double>()) // Store all response times
globalResults.put("startTime", System.currentTimeMillis()) // Capture start time
globalResults.put("completedThreads", 0) // Track the number of completed threads
props.put("aggregateResults", globalResults)
}
// Synchronize updates to avoid concurrency issues
synchronized (globalResults) {
def responseTime = prev.getTime() as double // Ensure it's a double
def success = prev.isSuccessful() ? 1 : 0
// Retrieve response time list and add the new response time
def responseTimes = globalResults.get("responseTimes") as CopyOnWriteArrayList<Double>
responseTimes.add(responseTime)
// Update counters
def requestStats = globalResults.get("stats") ?: [
count: 0, totalTime: 0.0, successCount: 0, failureCount: 0,
minTime: Double.MAX_VALUE, maxTime: Double.MIN_VALUE
]
requestStats.count += 1
requestStats.totalTime += responseTime
requestStats.successCount += success
requestStats.failureCount += (1 - success)
requestStats.minTime = Math.min(requestStats.minTime, responseTime)
requestStats.maxTime = Math.max(requestStats.maxTime, responseTime)
globalResults.put("stats", requestStats)
// Track completed threads to ensure the summary is written once
globalResults.put("completedThreads", globalResults.get("completedThreads") + 1)
}
// Summarize results only at the end of the test
if (globalResults.get("completedThreads") == ctx.getThreadGroup().getNumberOfThreads()) {
def stats = globalResults.get("stats")
def responseTimes = globalResults.get("responseTimes") as CopyOnWriteArrayList<Double>
def endTime = System.currentTimeMillis()
def startTime = globalResults.get("startTime")
def durationSeconds = (endTime - startTime) / 1000.0
def totalRequests = stats.count as double
def totalTime = stats.totalTime as double
def successCount = stats.successCount
def failureCount = stats.failureCount
def minTime = (totalRequests > 0) ? stats.minTime : 0.0
def maxTime = (totalRequests > 0) ? stats.maxTime : 0.0
def avgTime = (totalRequests > 0) ? totalTime / totalRequests : 0.0
def successRate = (totalRequests > 0) ? (successCount / totalRequests) * 100.0 : 0.0
def failureRate = (totalRequests > 0) ? (failureCount / totalRequests) * 100.0 : 0.0
def throughput = (durationSeconds > 0) ? totalRequests / durationSeconds : 0.0
// Calculate Standard Deviation
def stdDev = 0.0
if (totalRequests > 1) {
def mean = avgTime
def variance = responseTimes.stream()
.mapToDouble { rt -> Math.pow(rt - mean, 2) }
.sum() / (totalRequests - 1)
stdDev = Math.sqrt(variance)
}
// Build the CSV row
def csvRow = new StringBuilder()
csvRow.append("${new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date(startTime))},")
csvRow.append("${new SimpleDateFormat('yyyy-MM-dd HH:mm:ss').format(new Date(endTime))},")
csvRow.append("${String.format('%.2f', durationSeconds)},")
csvRow.append("${totalRequests.intValue()},")
csvRow.append("${totalTime.intValue()},")
csvRow.append("${String.format('%.2f', avgTime)},")
csvRow.append("${minTime.intValue()},")
csvRow.append("${maxTime.intValue()},")
csvRow.append("${String.format('%.2f', stdDev)},")
csvRow.append("${successCount},")
csvRow.append("${failureCount},")
csvRow.append("${String.format('%.2f', successRate)},")
csvRow.append("${String.format('%.2f', failureRate)},")
csvRow.append("${String.format('%.2f', throughput)}\n")
// Write the row to the CSV file
Files.write(filePath, csvRow.toString().getBytes(), StandardOpenOption.APPEND)
log.info("CSV Summary written to: ${filePath.toAbsolutePath()}")
// Clean up stored properties
props.remove("aggregateResults")
}