Gatling JDBC with dynamic where clause

Question

I'm using Gatling with the JDBC feeder and would like to dynamically add a parameter to the JDBC feeder's where clause based on the response from a previous request. Here is my example, I'm trying to do a post that will create a user, then have the feed grab the user's generated UUID using the userId returned from the create user request, then post some data with the UUID.

val dbConnectionString = "jdbc:mysql://localhost:3306/user"
val sqlQuery = "SELECT user_uuid FROM users where user_id = '${userId}'"
val sqlUserName = "dbUser"
val sqlPassword = "dbPassword"
val sqlQueryFeeder = jdbcFeeder(dbConnectionString, sqlUserName, sqlPassword, sqlQuery)
val uuidPayload = """{"userUUID":"${user_uuid}"}"""

val MyScenario = scenario("MyScenario").exec(
(pause(1, 2))
.exec(http("SubmitFormThatCreatesUserData")
  .post(USER_CREATE_URL)
  .body(StringBody("""{"username":"[email protected]"}""")).asJson
  .header("Accept", "application/json")
  .check(status.is(200))
  .check(jsonPath("$..data.userId").exists.saveAs("userId")))
.feed(sqlQueryFeeder) 
.exec(http("SubmitStuffWithUUID")
  .post(myUUIDPostURL)
  .body(uuidPayload).asJson
  .header("Accept", "application/json")
  .check(status.is(200)))
)

I have verified the following:

1) The user data does get inserted into the DB correctly on the form post 2) The userId is returned from that form post 3) The userId correctly saved as a Gatling session variable 4) The SQL query will execute correctly if I hard-code the user id variable

The problem I have is that when I have the Gatling ${userId} parameter on the JDBC feeder's where clause it appears the userId variable isn't used, I get an error saying java.lang.IllegalArgumentException: requirement failed: Feeder must not be empty. When I replace the ${userId} with a hard-coded userId everything works as expected. I would just like to know how I can use the userId session parameter in my JDBC feeder's where clause.

James Warr James Warr · Accepted Answer · 2019-09-10T00:13:46

The call to jdbcFeeder(dbConnectionString, sqlUserName, sqlPassword, sqlQuery) to create a jdbc feeder only takes strings as parameters, not gatling expressions (like ${userId}).

In your scenario as posted, you are not really using feeders as intended - they are generally used to have different users pick up different values from a pool of values whereas you have a static user name and are only taking the first value returned from the db. It's generally not a good idea to have fetching of external data in the middle of your scenarios as it can make timings unpredictable.

Could you just look up and hardcode the user_uuid? The best approach would be to get all your user data and look up things like uuids in the before block of the simulation. However, you can't use the gatling DSL there.

You could also use a scala variable to store the user_uuid and define a feeder inline, but this would get messy if you do need to support multiple users

Gatling JDBC with dynamic where clause

1 Answers