Forum Discussion

tonaortega's avatar
tonaortega
Occasional Contributor
8 years ago
Solved

Can I load excel datasource properties into ServiceV Dispatcher script?

Hello,

 

I'm trying to virtualize a service for Login call. It's basically two responses: LoginSuccess and LoginFail. So I'm trying to write a SCRIPT to dispatch the response.

 

Its a REST Request and I'm using JSON.

 

I've added a datasource excel file there and I would like to read the values from, and save them into variables in my dispatcher script. I've been struggling a lot trying to get the proper instruction to do it.

 

My datasource contains user, email, password and a token.

 

What I want to do is, get the user, email, password from datasource and compare the same from the incoming request, compare both and if valid then return LoginSuccess and a token within.

 

Is there any way I can do this? I'm very new to Ready!API so I'm not sure if is feasible. Any other suggestion may be?

 

Thanks!

  • tonaortega's avatar
    tonaortega
    8 years ago

    The solution was to create a mysql DB instance in my server and copy all the info from the spreadsheet into it.

     

    As I need to validate & compare incoming request data in my dispatcher script I used the following script:

     

    This is a REST service:

     

    1. Create Custom Properties with dbName, dbUsername, dbPassword, dbHost and add the values needed.

     

    2. Use this code into dispatcher script:

     

    //Get data from Request
    def jsonSlurper = new JsonSlurper()
    def requestObj = jsonSlurper.parseText(mockRequest.getRequestContent())
    assert requestObj instanceof Map

    // open connection
    def Virtservice = context.getMockService()
    def conString = "jdbc:mysql://" + Virtservice.getPropertyValue( "dbHost" ) + "/" + Virtservice.getPropertyValue( "dbName" )
    log.info conString

     

    //Define SQL Connection as follow:
    def sql = Sql.newInstance(conString,
    Virtservice.getPropertyValue( "dbUsername" ),
    Virtservice.getPropertyValue( "dbPassword" ),
    'com.mysql.jdbc.Driver')

    // Execute Query: Where userId is defined from incoming request data
    def query = "SELECT sessionToken FROM users WHERE userID LIKE '" + requestObj.userId + "';"

    //row value will get the entire row as result from query above.

    def row = sql.firstRow(query)

    //Set property value email with value found in row.email.
    context.setProperty("email",row.email)

    //check for connection in context
    sql.close()

     

    I hope this helps! Let me know if you have any question.

     

    Tona O.

     

     

4 Replies

Sort By
    • tonaortega's avatar
      tonaortega
      Occasional Contributor
      8 years ago

      Hi Tanya,

       

      I've seen this documentation before. Nothing from there could help to make it work. I implemented another solution. What I did is I had to build a local DB and populated with spreadsheet info. Then I pulled info from Dispatcher script using groovy SQL functions. It turns to be more work but finally an easier solution that read spreadsheet.

       

      I appreciate your reply.

       

      I'll post the code later for future reference to other people that could face same problem.

       

      Regards,

      Tona O.

      • TanyaYatskovska's avatar
        TanyaYatskovska
        SmartBear Alumni (Retired)
        8 years ago

        Thanks for sharing your solution with us, Tona!

        We will be waiting for your code. I'm sure it will help other Ready! API users.

  • tonaortega's avatar
    tonaortega
    Occasional Contributor
    8 years ago

    The solution was to create a mysql DB instance in my server and copy all the info from the spreadsheet into it.

     

    As I need to validate & compare incoming request data in my dispatcher script I used the following script:

     

    This is a REST service:

     

    1. Create Custom Properties with dbName, dbUsername, dbPassword, dbHost and add the values needed.

     

    2. Use this code into dispatcher script:

     

    //Get data from Request
    def jsonSlurper = new JsonSlurper()
    def requestObj = jsonSlurper.parseText(mockRequest.getRequestContent())
    assert requestObj instanceof Map

    // open connection
    def Virtservice = context.getMockService()
    def conString = "jdbc:mysql://" + Virtservice.getPropertyValue( "dbHost" ) + "/" + Virtservice.getPropertyValue( "dbName" )
    log.info conString

     

    //Define SQL Connection as follow:
    def sql = Sql.newInstance(conString,
    Virtservice.getPropertyValue( "dbUsername" ),
    Virtservice.getPropertyValue( "dbPassword" ),
    'com.mysql.jdbc.Driver')

    // Execute Query: Where userId is defined from incoming request data
    def query = "SELECT sessionToken FROM users WHERE userID LIKE '" + requestObj.userId + "';"

    //row value will get the entire row as result from query above.

    def row = sql.firstRow(query)

    //Set property value email with value found in row.email.
    context.setProperty("email",row.email)

    //check for connection in context
    sql.close()

     

    I hope this helps! Let me know if you have any question.

     

    Tona O.