Dynamic sql generation in jdbc feeder

Gatling (Open-Source)
#1

HI ,

Do we have a concept of dynamic sql generation in jdbc feeder in gatling??

I have the following use case.

  1. user try to register in to our system with unique email id.
  2. system generates a unique code and will be sent to customers email
  3. customer need to enter the unique code to complete the registration process.

This unique id is saved in DB and can be retrieved based on customers email.

but, i am unable to use jdbc feeder in this case, as this doesnt allow dynamic sql generation like (select * form uniqueCode where email=’${email}’).

can you help me on how to handle this scenario.

Thanks,
Sujatha

#2

The built-in jdbc feeder fetches data upfront, BEFORE running the simulation, so it doesn’t suit your use case.
What you want is write and exec(function) where you perform your database query and inject the fetched code into the session.

exec { session =>
val email = session(“email”).as[String]
session.set(“code”, fetchCodeFromEmail(email))
}

Then, properly design your fetchCodeFromEmail method:

  • use a decent connection pool (tomcat-jdbc, BoneCP or HikariCP)
  • use a PreparedStatement
  • depending on your load, you might have to increase the number of threads, as JDBC is a blocking protocol (override default parallelism-max in application.conf, see Akka documentation)
  • if you have the skills, maybe consider using a non blocking driver such as postgresql-async and then implement a full blown Action instead of a simple exec(function)
    Cheers,

Stéphane

#3

Hi Stéphane Landelle, and Sujatha,

I’m facing the same difficulty.
Could you elaborate it in terms of where and what should be written,

right now my query is this:

val feederdb = jdbcFeeder(“jdbc:mysql://xx.x.
169.xxx/usfdbuser”, “username”, “pwd”, “select otp from customer_otp_history where id in (select max(id) from customer_otp_history where bu_id=42 and email_id=’${userName}’ group by email_id)”)

val watchers = scenario(“Watchers”)
.feed(feederUserName)
.exec(createOtp)
.feed(feederdb)

.exec(confirmOtp) //(i’ll use retrieved value from Db in confirm otp api)

and by executing it is giving exception

Exception in thread “main” java.lang.
IllegalStateException: Feeder is now empty, stopping engine
at io.gatling.core.action.SingletonFeed$$anonfun$receive$1.applyOrElse(SingletonFeed.scala:61)
at akka.actor.Actor$class.aroundReceive(Actor.scala:484)
at io.gatling.core.akka.BaseActor.aroundReceive(BaseActor.scala:23)
at akka.actor.ActorCell.receiveMessage(ActorCell.scala:526)
at akka.actor.ActorCell.invoke(ActorCell.scala:495)
at akka.dispatch.Mailbox.processMailbox(Mailbox.scala:257)
at akka.dispatch.Mailbox.run(Mailbox.scala:224)
at akka.dispatch.Mailbox.exec(Mailbox.scala:234)
at scala.concurrent.forkjoin.ForkJoinTask.doExec(ForkJoinTask.java:260)
at scala.concurrent.forkjoin.ForkJoinPool$WorkQueue.runTask(ForkJoinPool.java:1339)
at scala.concurrent.forkjoin.ForkJoinPool.runWorker(ForkJoinPool.java:1979)
at scala.concurrent.forkjoin.ForkJoinWorkerThread.run(ForkJoinWorkerThread.java:107)

#4

Hi,

Stéphane mentions above that the JDBC feeder executes the query BEFORE running the simulation - i.e. every time you call the .feed method, the next row from that single resultset is given to you.

Given that, your query is returning no row because there is no username session variable before the simulation has started.

For your use case, JDBC feeder will not work - you will need to write your own function to connect to the DB, retrieve whatever is required and store in session variables (as suggested by Stéphane).

Thanks,
Barry

#5

Hi Barry,

But i’m Calling the JDBC feeder, right after executing of createOtp api.

like this,
.exec(createOtp)
.feed(feederdb)
.exec(confirmOtp)

Doesn’t positioning of JDBC feeder work if it is placed after the data generated ?

If not, are there any predefined/possible solutions to over come this hiccup?

or any hints?

Thanks,

Krish.

#6

My understanding from Stéphane’s original answer is that the request against the DB is executed once only, before the simulation starts. Each time you call the feeder, the next row from that resultset is given.

As mentioned above, you will need to write your own function to call in place of .feed, in order to retrieve the data you’re after and save it into the session.

Thanks,
Barry