Forum Discussion

markgholland's avatar
markgholland
Occasional Contributor
9 years ago

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 reques...
  • markgholland's avatar
    9 years ago

    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()