Forum Discussion

nosterling's avatar
nosterling
Occasional Contributor
14 years ago

Creating a comma separated list from a JDBC request result

Hi,

I am using SoapUI Pro and have gotten it to run towards my database finally.

I am trying to create a test cases that cleans out particular rows from my database, and i have managed to get the select running to get IDs on the rows, result looks like this.

<Results>
<ResultSet fetchSize="64">
<Row rowNumber="1">
<TRANSACTION_TABLE.TT_ID>42815681</TRANSACTION_TABLE.TT_ID>
</Row>
<Row rowNumber="2">
<TRANSACTION_TABLE.TT_ID>42815683</TRANSACTION_TABLE.TT_ID>
</Row>
<Row rowNumber="3">
<TRANSACTION_TABLE.TT_ID>42815703</TRANSACTION_TABLE.TT_ID>
</Row>
</ResultSet>
</Results>


Now, i want these IDs as a part of of a delete statement which is the next test step
delete from netgiro.transaction_table where TT_ID in (*My Ids*);

I cant have that as a subselect and delete in one step because of some other dependencies, so basically what i need is the first select to create a comma separated list of the IDs or a property transfer that loops through message and picks up each ID and then put it in the delete statement.

I realise this is probably doable via Groovy script but i am really poor at scripting and have miserably failed at creating the script needed

Is it possible to create a property transfer or get the DataSink step to create a csv file out of this? The problem is i dont know how many hit i will get, so i cant just create a property transfer for each ID.

3 Replies

Sort By
  • nosterling's avatar
    nosterling
    Occasional Contributor
    14 years ago
    And yes i know, there are other database tools that could do this, but this is obviously part of a much larger SoapUI test suite that runs webservice requests etc so i would like all of it done in one run...
  • nosterling's avatar
    nosterling
    Occasional Contributor
    14 years ago
    //Parse
    def findtxid = context.expand( '${find txid#ResponseAsXml#//Results[1]/ResultSet[1]}' )
    def txid = new XmlParser().parseText(findtxid)

    //Define the property
    def Txids = ""
    txid.Row.each{a->
    Txids = Txids + "," + a.'TRANSACTION_TABLE.TT_ID'.text()
    }
    Txids = Txids.substring(1)
    log.info "We get: " + Txids

    //Write the value to the the property
    def Result = testRunner.testCase.getTestStepByName( "Txids for delete" )
    Result.setPropertyValue( "Txids", Txids.toString() )