Forum Discussion

markgholland's avatar
markgholland
Occasional Contributor
8 years ago
Solved

Building Agile XML with MarkupBuilder and JDBC Query Results

I just figured out how to do this, but I'm going to post it anyway since I think this script may be useful to others that are trying to do something similar...

 

 

I'm trying to build a xml request dynamically based on jdbc query.

import groovy.sql.Sql
import oracle.jdbc.driver.OracleTypes
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.OracleDriver")

import groovy.xml.MarkupBuilder
import groovy.xml.XmlUtil


//get environment specific(dev1, qa1, etc.) project db connection string properties
def host = context.expand( '${#Project#dbhost}' )
def port = context.expand( '${#Project#dbport}' )
def serviceName = context.expand( '${#Project#dbservicename}' )
def username = context.expand( '${#Project#dbusername}' )
def password = context.expand( '${#Project#dbpassword}' )
def schemaName = context.expand( '${#Project#dbSchemaName}' )

//build connection string
conString = ("jdbc:oracle:thin:@" + host + ":" + port + "/" + serviceName)

//connect to db
def con = Sql.newInstance(conString, username, password, "oracle.jdbc.driver.OracleDriver")

//get order types, these are properties that were transferred from the data source
def orderTypeCodes = context.expand( '${#TestCase#orderTypeCodes}' )
def locale = context.expand( '${TestData#locale}' )

//build query, removed details because they are not important. ordertypeCodes and locale are used in this query
def query = ("SELECT ... ")

//run query and set results
def results = con.rows(query)

//create writer
def writer = new StringWriter()
def builder = new MarkupBuilder( writer )

//define standard order structure
def createOrder( builder, repeat, date, quantity, id, owningId, addedBy, lastModifiedBy, version ) {
repeat.times{
builder.order{
builder.orderDate(date)
builder.orderQuantity(quantity)
builder.orderId(id)
builder.owningId(owningId)
builder.addedBy(addedBy)
builder.lastModifiedBy(lastModifiedBy)
builder.version(version)
}
}
}

//this is the root, not sure where this needs to go...
builder.orders{}


for ( i = 0; i < results.size(); i++ ) {
createOrder(builder, 1, context.expand( '${#TestCase#date}' ), 1, results[i].ORDER_ID, results[i].OWNING_ID, context.expand( '${TestData#appUserID}' ), context.expand( '${TestData#appUserID}' ), 1)
}

return writer.toString()

 

I'm trying to create this:

<orders>
  <order>
    ...
  </order>
  <order>
    ...
  </order>
  <order>
    ...
  </order>
</orders>

But my script results in this:

<orders />
<order>
  ...
</order>
<order>
  ...
</order>
<order>
  ...
</order>

 

  • Here was the solution:

     

    import groovy.sql.Sql
    import oracle.jdbc.driver.OracleTypes
    com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.OracleDriver")
    
    import groovy.xml.MarkupBuilder
    import groovy.xml.XmlUtil
    
    
    //get environment specific(dev1, qa1, etc.) project db connection string properties
    def host = context.expand( '${#Project#dbhost}' )
    def port = context.expand( '${#Project#dbport}' )
    def serviceName = context.expand( '${#Project#dbservicename}' )
    def username = context.expand( '${#Project#dbusername}' )
    def password = context.expand( '${#Project#dbpassword}' )
    def schemaName = context.expand( '${#Project#dbSchemaName}' )
    
    //build connection string
    conString = ("jdbc:oracle:thin:@" + host + ":" + port + "/" + serviceName)
    
    //connect to db
    def con = Sql.newInstance(conString, username, password, "oracle.jdbc.driver.OracleDriver")
    
    //get order types, these are properties that were transferred from the data source
    def orderTypeCodes = context.expand( '${#TestCase#orderTypeCodes}' )
    def locale = context.expand( '${TestData#locale}' )
    
    //build query, removed details because they are not important. ordertypeCodes and locale are used in this query
    def query = ("SELECT ... ")
    
    //run query and set results
    def results = con.rows(query)
    
    //create writer
    def writer = new StringWriter()
    def builder = new MarkupBuilder( writer )
    
    //define standard order structure
    def createOrder( builder, repeat, date, quantity, id, owningId, addedBy, lastModifiedBy, version ) {
     repeat.times{
     builder.order{
     builder.orderDate(date)
     builder.orderQuantity(quantity)
     builder.orderId(id)
     builder.owningId(owningId)
     builder.addedBy(addedBy)
     builder.lastModifiedBy(lastModifiedBy)
     builder.version(version)
     }
     }
    }
    
    //I put the for loop inside, duhh builder.orders{ for ( i = 0; i < results.size(); i++ ) { createOrder(builder, 1, context.expand( '${#TestCase#date}' ), 1, results[i].ORDER_ID, results[i].OWNING_ID, context.expand( '${TestData#appUserID}' ), context.expand( '${TestData#appUserID}' ), 1) } } return writer.toString()

1 Reply

  • markgholland's avatar
    markgholland
    Occasional Contributor

    Here was the solution:

     

    import groovy.sql.Sql
    import oracle.jdbc.driver.OracleTypes
    com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("oracle.jdbc.OracleDriver")
    
    import groovy.xml.MarkupBuilder
    import groovy.xml.XmlUtil
    
    
    //get environment specific(dev1, qa1, etc.) project db connection string properties
    def host = context.expand( '${#Project#dbhost}' )
    def port = context.expand( '${#Project#dbport}' )
    def serviceName = context.expand( '${#Project#dbservicename}' )
    def username = context.expand( '${#Project#dbusername}' )
    def password = context.expand( '${#Project#dbpassword}' )
    def schemaName = context.expand( '${#Project#dbSchemaName}' )
    
    //build connection string
    conString = ("jdbc:oracle:thin:@" + host + ":" + port + "/" + serviceName)
    
    //connect to db
    def con = Sql.newInstance(conString, username, password, "oracle.jdbc.driver.OracleDriver")
    
    //get order types, these are properties that were transferred from the data source
    def orderTypeCodes = context.expand( '${#TestCase#orderTypeCodes}' )
    def locale = context.expand( '${TestData#locale}' )
    
    //build query, removed details because they are not important. ordertypeCodes and locale are used in this query
    def query = ("SELECT ... ")
    
    //run query and set results
    def results = con.rows(query)
    
    //create writer
    def writer = new StringWriter()
    def builder = new MarkupBuilder( writer )
    
    //define standard order structure
    def createOrder( builder, repeat, date, quantity, id, owningId, addedBy, lastModifiedBy, version ) {
     repeat.times{
     builder.order{
     builder.orderDate(date)
     builder.orderQuantity(quantity)
     builder.orderId(id)
     builder.owningId(owningId)
     builder.addedBy(addedBy)
     builder.lastModifiedBy(lastModifiedBy)
     builder.version(version)
     }
     }
    }
    
    //I put the for loop inside, duhh builder.orders{ for ( i = 0; i < results.size(); i++ ) { createOrder(builder, 1, context.expand( '${#TestCase#date}' ), 1, results[i].ORDER_ID, results[i].OWNING_ID, context.expand( '${TestData#appUserID}' ), context.expand( '${TestData#appUserID}' ), 1) } } return writer.toString()