Forum Discussion

Perchishka's avatar
Perchishka
Occasional Contributor
8 years ago

JDBC request with date from another JDBC response and stored procedure

I have a JDBC response:

<Results>
    <ResultSet fetchSize="0">
        <Row rowNumber="1">
            <PS_SUBSCRIBE_DATA_290.CHANGED_DATE>2017-02-01 12:13:51.378</PS_SUBSCRIBE_DATA_290.CHANGED_DATE>
        </Row>
    </ResultSet>
</Results>

I need to use this date in another JDBC request

But also ineed to use stored procedure because of the name of the table.

 

I have a SQL query for DBeaver client  and it works: 

select * from get_partition_name('ps_subscribe_history','2017-02-01 12:13:51'::timestamp without time zone)

 

But when i want to do the same sql query in JDBC request:

select * from get_partition_name('ps_subscribe_history', '${JDBC Request#ResponseAsXml#PS_SUBSCRIBE_DATA_290.CHANGED_DATE}')

This produces an error :

 

2017-02-01 18:46:34 - Error getting response; org.postgresql.util.PSQLException: ERROR: invalid input syntax for type timestamp: ""Position: 58

 

Could you help me to resolve this problem?

 

Maybe this  error is caused by an incorrect date format ?

2017-02-01 12:13:51.378

 

  • Perchishka's avatar
    Perchishka
    8 years ago

    Thank you, very much!

    I did it!

     

    assert context.ResponseasXML
def parsedXml = new XmlSlurper().parseText(context.ResponseasXML)
def dt = parsedXml.'**'.find { it.name() == 'PS_SUBSCRIBE_DATA_290.CHANGED_DATE'} as String 

def dd = dt.substring(0, dt.lastIndexOf("."))
log.info( dd )
context.testCase.setPropertyValue('CHANGED_DATE', dd)
  • nmrao's avatar
    nmrao
    Champion Level 3
    8 years ago

    Like it was mentioned in the previous response, use below script assertion for the first jdbc request where you want to extract the date.

     

    assert context.response
def parsedXml = new XmlSlurper().parseText(context.response)
def dt = parsedXml.'**'.find { it.name() == 'PS_SUBSCRIBE_DATA_290.CHANGED_DATE'} as String
context.testCase.setPropertyValue('CHANGED_DATE' dt.substring(0, dt.lastIndexOf(".")))

    Now you can use ${#TestCase#CHANGED_DATE} where you want to use the extracted and stripped timestamp value.

    • PaulMS's avatar
      PaulMS
      Super Contributor
      8 years ago

      The get data feature exists in OS but you have to specify the XPath manually. In this case there is no need for script assertion, just add the missing // to XPath in property expansion.

      • nmrao's avatar
        nmrao
        Champion Level 3
        8 years ago
        This is news to me. I can't found "Get Data", would you mind showing a screen shot?
    • Perchishka's avatar
      Perchishka
      Occasional Contributor
      8 years ago

      Hello!

       

      I tried this code

      assert context.Response
def parsedXml = new XmlSlurper().parseText(context.response)
def dt = parsedXml.'**'.find { it.name() == 'PS_SUBSCRIBE_DATA_290.CHANGED_DATE'} as String 
log.info( parsedXml )
context.TestCase.setPropertyValue('CHANGED_DATE', dt.substring(0, dt.lastIndexOf(".")))

      and this message appears in logs:

      Wed Mar 01 11:55:52 MSK 2017:INFO:subscribed

      but it is not a response of  JDBS request, It is a response of the first Test step in this Test case (((

       

      And as a result i have an error:

       

      "Cannot invoke method lastIndexOf() on null object"

       

      Then I decided to make some changes:

       

       

      assert context.ResponseasXML
def parsedXml = new XmlSlurper().parseText(context.ResponseasXML)
def dt = parsedXml.'**'.find { it.name() == 'PS_SUBSCRIBE_DATA_290.CHANGED_DATE'} as String 
log.info( dt )
context.TestCase.setPropertyValue('CHANGED_DATE', dt.substring(0, dt.lastIndexOf(".")))

      and next message appears in logs:

      Wed Mar 01 12:52:48 MSK 2017:INFO:2017-03-01 11:22:47.036

      And this is what I need

      But i have an error:

      "Cannot invoke method setPropertyValue() on null object"

       

      Could you help me, please?

      What should I change?

       

      • PaulMS's avatar
        PaulMS
        Super Contributor
        8 years ago

        You need a lower case t for context.testCase

  • PaulMS's avatar
    PaulMS
    Super Contributor
    8 years ago

    Hi Perchishka

     

    To get data from the response you need to specify XPath expression.

     

    ${JDBC Request#ResponseAsXml#//PS_SUBSCRIBE_DATA_290.CHANGED_DATE}

    I would usually right click in the SQL query, Get Data, select the step/property then the Select XPath window will appear if the property contains XML.

  • nmrao's avatar
    nmrao
    Champion Level 3
    8 years ago
    So you basically need to transfer the date value from one step to the other?
    If so, Script Assertion suites better in this case.

    PaulMS, I believe Perchishka is using open source which does not the option that you mention.