cancel
Showing results for 
Search instead for 
Did you mean: 

Building Agile XML with MarkupBuilder and JDBC Query Results

SOLVED
Highlighted
Occasional Contributor

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>

 

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Occasional Contributor

Re: Building Agile XML with MarkupBuilder and JDBC Query Results

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

View solution in original post

1 REPLY 1
Highlighted
Occasional Contributor

Re: Building Agile XML with MarkupBuilder and JDBC Query Results

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

View solution in original post

New Here?
Join us and watch the welcome video:
Read the Latest News
JuneSCNJ
Top Kudoed Authors