cancel
Showing results for 
Search instead for 
Did you mean: 

Building Agile XML with MarkupBuilder and JDBC Query Results

SOLVED
Dear Community, API Masterminds week 4 has started!

Participate in the exciting online event by posting questions or replies and win! During the entire March, we decide top contributors each week and reward them with great gifts . Congratulate the winners of Week 3 here.
Week 4 task: Ask more questions, bring interesting topics and reply to existing ones in the ReadyAPI Community
Topics to participate in:
See All
Week 4 Leaders:
Members with the biggest number of posts
sonya_m 16 posts
nmrao 14 posts
HimanshuTayal 12 posts
pizzaTime 1 posts
richie 1 posts
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:
Join 2020 API Masterminds
We invite all lovers of API testing to participate in the API Masterminds event. Ask questions or help others by giving solutions and get rewarded.
>> Participate Today
Get Your Free Ticket
SBC2020
Top Kudoed Authors