In SQL query how to pass data values fetching from CSV

Gatling (Open-Source)
1

Query is : In SQL query how to pass data values fetching from CSV

Currently, i have hardcoded value mobile_number = ‘971555555666’ in my SQL query like below exapmle

private val sqlQuery = “SELECT otp_number FROM auth where mobile_number = ‘971555555666’ order by date_generated desc limit 1”

I want to fetch mobile number from CSV and pass in "mobile_number = " parameter how can i do this.

Please help

2 
val MobileRegistrationFeeder = csv(fileName = "dataDetails.csv").circular

//Define Request
def OTP_Validate(): ChainBuilder = {
  feed(MobileRegistrationFeeder)

//Reading mobile number from CSV
    .exec { session =>
      println("mobileNumber")
      println(session("mobileNumber").as[String])
      session
    }
    .pause(5)
    .repeat(1) {

      // load and register JDBC driver for MySQL
      //Class.forName("com.mysql.cj.jdbc.Driver")

      val dbConnectionString = "jdbc:mysql://[db.com:3306](http://db.com:3306)"
      val sqlQuery = "SELECT otp_code FROM otp where mobilenumber = '${mobileNumber}'  order by date_generated desc limit 1"
      val sqlUserName = "abc"
      val sqlPassword = "123"
      val message = "${data}"

      println("mobileNumber")
      // SourceFeederBuilder[Any
      val sqlQueryFeeder: FeederBuilderBase[Any] = jdbcFeeder(

        dbConnectionString,
        sqlUserName,
        sqlPassword,
        sqlQuery)

      feed(sqlQueryFeeder)
        .exec { session =>
          println("otp_code")
          println(session("otp_code").as[String])
          session
        }
3

Above is the full code which i tried to fetch mobile number from CSV and pass the fetched mobile number in SQL.
what syntax I should be used to send mobile parameters.

Looking for your replay

Many thanks
Pooja

4

Why not create a function that returns a jdbcdatafeeder and has the parameter as a input? Just make the val sqlQuery composed of a prefix and postfix and put the inputparameter mobileNumber in between?

5

I tried something like this, but still its not working. When im reading CSV data from session, from this session mobile numbers are not passing to sql query. Please can someone help me with solutions

 val MobileRegistrationFeeder = csv(fileName = "UserDetails.csv").circular
 var mobile_Number = ""
 var OTP_num = ""

 def mobilenumber_fromCSV() = {
   feed(MobileRegistrationFeeder)
     .exec {
       session =>
         mobile_Number = session("mobileNumber").as[String]
         println(mobile_Number)
         session
     }

/* }
 println(mobile_Number + "mobile number out of method")

 def SQL_queryexecution() = {
   mobilenumber_fromCSV()*/
   // load and register JDBC driver for MySQL
   val dbConnectionString = "jdbc:mysql://[abci.com:3306](http://abci.com:3306)"
   //look for pulling mobile number from NewRegisteredUserDetails.csv
   val sqlQuery = "SELECT otp_code FROM otp where mobile_dest = '" + mobile_Number + "' order by date_generated desc limit 1"
   println(sqlQuery)
   val sqlUserName = "abc"
   val sqlPassword = "123"
   // SourceFeederBuilder[Any
   val sqlQueryFeeder: FeederBuilderBase[Any] = jdbcFeeder(
     dbConnectionString,
     sqlUserName,
     sqlPassword,
     sqlQuery)

   feed(sqlQueryFeeder)
     .exec { session =>
       println("otp_code")
       OTP_num = (session("otp_code").as[String])
       println("OTP_num")
       session
     }
6

Dear Gatling team,

mobile number are dynamic numbers are coming in response not able to save the response in a global variable, how to pass mobile number in SQL query ?

If i’m hardcoading logics are working fine.Please help

//Define Request
def DealerRegisterwithMobile(): ChainBuilder = {

var Destination_m = “”;

feed(customFeeder)
.repeat(1) {
feed(MobileRegistrationFeeder)
.exec(http(requestName = “New Dealer Register with Mobile”)
.post(url = “abc/otp/generate”)
.body(StringBody(
“”"

{“firstName”: “${firstName}”,“lastName”: “${lastName}”,“mobileNumber”: “${number}”}

“”".stripMargin)).asJson
.check(status.is(200)
)
.check(jsonPath(path = “$.data.firstName”).saveAs(key = “firstName”))
.check(jsonPath(path = “$.data.lastName”).saveAs(key = “lastName”))
.check(jsonPath(path = “$.data.mobileNumber”).saveAs(key = “mobileNumber”))

)
}
.pause(2)

.repeat(1) {
/* .exec(session => session.set(“Destination_m”, session(“mobileNumber”).as[String]))
println(Destination_m + “mobileNumber fro, session”)*/

val dbConnectionString = “jdbc:mysql://abc.com:3306”
val sqlQuery = “SELECT otp_code FROM otp_auth where otp_destination = + mobileNumber + order by date_generated desc limit 1”

println(sqlQuery)

Many Thanks
Pooja

7

Don’t try to do this:

  1. Session data only exist in the scope of the Session. You can’t use the Feeder API for this. Feeders are global datasources.
  2. You’ll will just destroy performance when trying the perform blocking JDBC queries in Gatling’s asynchronous requests
    Instead, prefetch all the data in memory or in a file before starting the actual test, turn it into a Map, and fetch data from this Map.
8

Hi Stephane,

To prefetch value on file, we have validation on OTP is validated only for 2 min.

My scenario is to register user using Rest API and I want to read the verification code from the database. In SQL query i want to pass mobile number, but currently not able to get value out of session. Please suggest how can we fulfill this requirement using gatling

val MobileRegistrationFeeder = csv(fileName = "data/dealeruser/registration/NewRegisteredUserDetails.csv").circular
var mobile_Number = ""

//Define Request
def DealerRegisterwithMobileOTP_Validate(): ChainBuilder = {
  feed(MobileRegistrationFeeder)
    .exec { session =>
      mobile_Number = session("mobileNumber").as[String]
      println(mobile_Number)
      session
    }

    .pause(5)
      // load and register JDBC driver for MySQL
      //Class.forName("com.mysql.cj.jdbc.Driver")
       println(mobile_Number + "mobile_Number")
       val dbConnectionString = "jdbc:mysql://db:3306"
       val sqlQuery = "SELECT otp_code FROM otp_auth where otp_destination = '" + mobile_Number + "' order by date_generated desc limit 1"
  println(sqlQuery)
       val sqlUserName = "abc"
       val sqlPassword = "123"
       val message = "${data}"

      // SourceFeederBuilder[Any
      val sqlQueryFeeder: FeederBuilderBase[Any] = jdbcFeeder(
        dbConnectionString,
        sqlUserName,
        sqlPassword,
        sqlQuery)

feed(sqlQueryFeeder)

9

You could consider using https://github.com/rbraeunlich/gatling-jdbc. It’s a third party plugin, so you won’t get any help for it here. Try StackOverFlow.

Then If you can’t manage with that, I’m afraid the kind of help you’re requesting is too time consuming for a community mailing list and you should consider getting a consulting/mentoring contract.

Regards,