Forum Discussion

dumbuser's avatar
dumbuser
Occasional Contributor
7 years ago

setProprertyValue string to integer to insert into database

From REST Request I get a json response which contains an accountID which I want to use in the next teststep as and id in insertion to database.

 

In the REST request I use script assertion: 

import net.sf.json.groovy.JsonSlurper
def jsonResponse = new JsonSlurper().parseText(messageExchange.responseContent)
assert null != jsonResponse.accountId, "accountId of response either does not have value or null"

context.testCase.testSuite.setPropertyValue('ACCOUNT_ID', jsonResponse.accountId.toString())

In the next JDBC step I use a property userId which has value ${#TestSuite#ACCOUNT_ID}. 

But when I try the INSERT, I get a error that column is of type bigint but expression is of type character varying. I've tried typing it like so :userId::integer. But then I get an error which says "No value specified for parameter 2". I suppose it's because the string I get is an array.

 

The question is how can I setPropertyValue as a single int? There is no way that there could be an array of numbers. Userid is always just one number.


I've tried everything the groovy script like toInteger() and so on, but it won't let me do that. 

 

Can someone suggest something?

  • JHunt's avatar
    JHunt
    7 years ago

    Rao has identified the issue correctly, just got the order a little wrong.

     

    You should be able to spot the problem following this:

     

    def parse = {json -> new net.sf.json.groovy.JsonSlurper().parseText(json)}

def json1 = '{"accountId":1000092192}'
def json2 = '[{"accountId": 1000092192}]'

assert parse(json1).accountId.toString() == '1000092192'
assert parse(json2).accountId.toString() == '[1000092192]'

assert parse(json2).first().toString() == json1
assert parse(json2).first().accountId.toString() == '1000092192'

     Alternative notation:

     

    assert parse(json2)[0] == parse(json2).first()
  • JHunt's avatar
    JHunt
    Community Hero
    7 years ago

    Rao has identified the issue correctly, just got the order a little wrong.

     

    You should be able to spot the problem following this:

     

    def parse = {json -> new net.sf.json.groovy.JsonSlurper().parseText(json)}

def json1 = '{"accountId":1000092192}'
def json2 = '[{"accountId": 1000092192}]'

assert parse(json1).accountId.toString() == '1000092192'
assert parse(json2).accountId.toString() == '[1000092192]'

assert parse(json2).first().toString() == json1
assert parse(json2).first().accountId.toString() == '1000092192'

     Alternative notation:

     

    assert parse(json2)[0] == parse(json2).first()
  • dumbuser's avatar
    dumbuser
    Occasional Contributor
    7 years ago

    Not sure what you mean about the format, but it's PSQL and looks something like this.

     

    INSERT INTO table (text, boolean, boolean, date, int, int, user_id, text, boolean, int)
VALUES ('some text', 0, 0, '2001-01-01', 11111, 1111, :userId, 'some text', 0, 111);

    I changed all the names of the columns except user_id to their types.

    user_id is int as well

  • JHunt's avatar
    JHunt
    Community Hero
    7 years ago

    Are you asking for help about SoapUI or about PSQL side? I can't help you with PSQL, but does your PSQL work in a basic case, where you just write a value directly into the query?

     

    You haven't posted your JSON - you mentioned something coming out as an array? I couldn't reproduce it with a simple JSON:

     

    def JSON = '{"accountId":123}'

    context.testCase.setPropertyValue("accountId", new net.sf.json.groovy.JsonSlurper().parseText(JSON).accountId.toString())

    def expected = '''\
    INSERT INTO table (a, b, c, d, e, f, accountId, h, i, j)
    VALUES ('some text', 0, 0, '2001-01-01', 11111, 1111, 123, 'some text', 0, 111);\
    '''

    def actual = context.expand('''\
    INSERT INTO table (a, b, c, d, e, f, accountId, h, i, j)
    VALUES ('some text', 0, 0, '2001-01-01', 11111, 1111, ${#TestCase#accountId}, 'some text', 0, 111);\
    ''')

    assert expected == actual

    If you need to get a single item out of an array, can you just get the 0th element?

     

    def a = ["123"]
assert a[0] == "123"
    • dumbuser's avatar
      dumbuser
      Occasional Contributor
      7 years ago

      Yes, the PSQL works just fine when I write a value in directly.

       

      And about the id coming as an array I'm not actually sure. It shouldn't be an array, but it's kind of confusing.

       

      When I run 

      context.testCase.testSuite.setPropertyValue('ACCOUNT_ID', jsonResponse.accountId.toString().toInteger())

      I get an error: For input string: "[1000092192]"

      The id is shown, but in brackets. In the json response it's not in brackets (which is correct). So is it an array? 

       

      I've also tried

      context.testCase.testSuite.setPropertyValue('ACCOUNT_ID', jsonResponse.accountId.toString().replace("[", "").replace("]", "").toInteger())

      I get an error "No signature of method: com.eviware.soapui.impl.wsdl.WsdlTestSuite.setPropertyValue() is applicable for argument types: (java.lang.String, java.lang.Integer) values: [ACCOUNT_ID, 1000092192] Possible solutions: setPropertyValue(java.lang.String, java.lang.String), getPropertyValue(java.lang.String)" 

       

      I guess it's for strings only and I can't use toInteger() here.

       

      So I tried another option

      def i = Integer.parseInt(context.testCase.testSuite.getPropertyValue(jsonResponse.accountId.toString()))

      but in that case I get an error that it's null. Which it can't be?? I mean, I just saw that the string contains the correct value.

       

       

      I think I should somehow get an int value here in the groovy script and then I shouldn't have a problem in the insertion. But I can't figure out how.

       

       

       

      JSON response:

      [{
   "accountId": 1000092192,
   "user": "blabla",
   "code": "1121212",
   "firstName": "testfirst",
   "familyName": "testfamily",
   "country": "US",
   "anotherId": 23121
}]

       

       

       

      • nmrao's avatar
        nmrao
        Champion Level 3
        7 years ago

        That is not problem with brackets. It is an array of values with single element.

        Try below:

        context.testCase.testSuite.setPropertyValue('ACCOUNT_ID', jsonResponse.accountId.first().toString().toInteger())

         

         

  • dumbuser's avatar
    dumbuser
    Occasional Contributor
    7 years ago

    Thank you so much!! I struggled so much with it and in the end it was quite an easy fix. As always...

     

     

    For future refrence: 

     

    I also had to change my SQL query a bit. While :userId::integer didn't work,

    (select cast (:userId as int))

    made all the difference.