JMeter, how to write JDBC query results as csv, including column names?

Question

I would like to write the results of my JDBC query as csv file, including a line with the names of the columns.

I am able to write results by defining a JDBC PreProcessor in which I assign the Result variable name to resultSet, and a JSR223 PreProcessor where I include the below script to write to csv.

However, the variable returned by vars.getObject("resultSet"); is a Collection, so I am unable to get the column name information. Is it possible to pass the ResultSet object to the JSR223 PreProcessor so that I can export column names?

resultSet = vars.getObject("resultSet");
result = new StringBuilder();

//for (int i = 0; i <= resultSet.getMetaData().getColumnCount(); i++) {
//  result.append(resultSet.getMetaData().getColumnName(i));
//  result.append(',');
//}
//result.append(System.getProperty("line.separator"));

for (Object row : resultSet ) {
    iter = row.entrySet().iterator();
    while (iter.hasNext()) {
        pair = iter.next();
        result.append(pair.getValue());
        result.append(",");
    }
    result.append(System.getProperty("line.separator"));
}

org.apache.commons.io.FileUtils.writeStringToFile(new File("/tmp/data", "results.csv"), result.toString(), "UTF-8");

score 1 · Accepted Answer · answered Jan 03 '18 at 06:44

Your resultSet already has column names. As per Debugging JDBC Sampler Results in JMeter article:

Regarding working with the Result Variable Name, as mentioned previously, it is an ArrayList of HashMaps, one map per result set row. The map entries count depends on the column values, the map entry name will be the same as the result set column name, and the map entry value will be the value itself.

So you need to amend your code to read the column names as well, something like:

resultSet = vars.getObject("resultSet")
result = new StringBuilder()

def randomRow = resultSet.get(org.apache.commons.lang3.RandomUtils.nextInt(0,resultSet.size()))

randomRow.each { k, v -> 
    result.append("${k}").append(",")
}
result.append(System.getProperty("line.separator"))

for (Object row : resultSet ) {
    iter = row.entrySet().iterator()
    while (iter.hasNext()) {
        pair = iter.next()
        result.append(pair.getValue())
        result.append(",")
    }
    result.append(System.getProperty("line.separator"))
}

org.apache.commons.io.FileUtils.writeStringToFile(new File("foo.csv"), result.toString(), "UTF-8")

Thanks, that makes sense... but is it possible to get the ResultSetMetadata? Would also be useful to know the column datatype — ab11, Jan 03 '18 at 14:13

score 0 · Answer 2 · answered Jan 02 '18 at 21:29

There are a number of CSV libraries in Java, Apache Commons CSV, https://commons.apache.org/proper/commons-csv/, OpenCSV, http://opencsv.sourceforge.net/.

Personally instead of trying to run a generic SQL statement such as SELECT * FROM table_name; I'd be specific in stating what columns you actually need. Then you know what column names you are going to be adding to the CSV file.

If you do want to create something generic that also can parse the names of the columns, this answer may provide what you're looking for - Retrieve column names from java.sql.ResultSet

The issue is that the resultSet object provided by `vars.getObject("resultSet");` is of type `ArrayList` not of type `ResultSet` — ab11, Jan 02 '18 at 21:48

JMeter, how to write JDBC query results as csv, including column names?

2 Answers2