SmartBear Connect 2018
Join us at SmartBear Connect 2018 on October 29th & 30th in Boston, MA.
Come meet the team behind your tools – get your ticket today!
Reply
Highlighted
Occasional Contributor

Write SoapUI Pro testsuite output to a database?

I am constructing a dashboard for our systems and would like to know how to send testsuite output to a database (MSSQL).  I configured the database connection in SoapUI Pro and can ping the database from the server that ReadyAPI runs on.  

 

Is there a way to send testsuite results directly to a database?  

Staff

Re: Write SoapUI Pro testsuite output to a database?

Thank you for posting to our Community Forum.

 

Here is a script that you can insert in the "Teardown Script" field of each TestSuite which writes the results to a file:

 

fos = new FileOutputStream('C:/temp/test-log.txt', true )

pw = new PrintWriter( fos )


pw.write(testSuite.name + ": "+runner.status.toString())

pw.close()
fos.close()

Since you need to write this into a DB, you can replace the file-writing code with JDBC code to write into a specified DB. Unfortunately I do not have a sample of that but this should help you.

 

Let me know if you have any questions/concerns.

Steven
SmartBear Staff
New Contributor

Re: Write SoapUI Pro testsuite output to a database?

I have same requirement to log SecurePro test result in own dashboard (report portal.io)
Where we need to store test results in junit result xml file.

Is there any way to store test results in junit xml format.
Occasional Contributor

Re: Write SoapUI Pro testsuite output to a database?

Thank you for this help!  Using this in the TestSuite TearDown script, the results is writes is "Finished".  It does not

give Pass/Fail status.  I'm trying to write to a database we use for a system dashboard and would like to show the pass/fail results.  Is there a way to do that?

Occasional Contributor

Re: Write SoapUI Pro testsuite output to a database?

Once again thank you for the help!  We actually would like to ask anyone who reads this if they could provide the script we need to write to a database instead of the file.

Contributor

Re: Write SoapUI Pro testsuite output to a database?

Hello there! below is the groovy script I am using to write test results to SQL server database. My requirements are slightly different. I wanted to record pass/fail status of Restful services only from a given testSuite. 

 

My approach was to have this script at event-handler level and run only after a REst step.

Also, I wanted to capture few things like if rest step failed at request level or due to assertions and store the response in a file.

 

Assumption: If HTTP Status assertion is valid then request is working but additional check needs to be done using other assertion methods such as script, compliance xpath etc... So each Rest step has Valid Http status code assertion.

 

import net.sf.*
import net.sf.json.*
import net.sf.json.groovy.*
import groovy.sql.Sql
import groovy.json.JsonSlurper
import java.text.DateFormat
import com.eviware.soapui.model.testsuite.Assertable

//Get Some info
def TestSource = context.expand('${#TestSuite#TSNAME}')
def FilePath = context.expand('${#TestSuite#ResponsePath}')
def TestExec = context.expand('${#TestCase#RUNTIME}')
def TestCase = context.getTestCase().name
def TestStep = context.getCurrentStep()
def RestStepName = TestStep.getLabel()

def date
def URL
def ResTime
def httpStatusCode
def Response
def FileName
def DataVal
def RequestStatus


// Append TestStep name to testcase if more than one REST step is in the testcase //
if(context.testCase.getTestStepsOfType(com.eviware.soapui.impl.wsdl.teststeps.RestTestRequestStep).size()>1){
	                                                                                                        TestCase = TestCase + ' - ' + RestStepName
}else{
	 TestCase = RestStepName
}

//Perform below actions only if TestStep is a REST call //
if (TestStep.config.type == 'restrequest'){

                                           log.info "-------------------------------------------------------"
                                           log.info "Running ResultLogger..."

                                           date = new Date().format("MM/dd/yyyy hh:mm:ss a") //Get current date//
                                           URL = context.httpMethod.getURI() //Get REST URL//
                                           ResTime = context.httpMethod.getTimeTaken() //Get response Time//
                                           Response = context.expand('${'+RestStepName+'#Response}').toString()
                                                                 

                                           //Get Http Status Code //
                                           try{
                                               def httpResponseHeaders = context.testCase.testSteps[RestStepName].testRequest.response.responseHeaders
                                               def httpStatus = httpResponseHeaders["#status#"]
                                                   httpStatusCode = (httpStatus =~ "[1-5]\\d\\d")[0]
                                           }catch(e){
	                                                httpStatusCode = "ERROR"
                                           }

                                           //## Generate dynamic file name to store responses ##//
                                           FileName = RestStepName + ".txt"  
                                           FileName = FilePath + FileName

                                           def jsonfile = new File(FileName)
                                               jsonfile.delete()
                                               log.info "Writing response to $FileName"
    	                                          jsonfile << Response

                                           //## Capture Assertion Status ##//
                                           def Assertion, ErrorLevel, TestStatus
                                           try{TestStep.getAssertionList().any{
	                                                                         if(it.status.toString() != 'VALID'){
	                           	                                                                               Assertion = 'Fail'	                                                                        	
	                                      }else {
	                               	          Assertion = 'Pass'
	                               	          return
	                                      }     
    
                                           }
 
                                           }catch(e){
	                                                log.info "Assertion script didn't run" 
                                           }

                                           
                                           // Get HTTPStatusCode assertion status
                                           TestStep.getAssertionList().each{
                                         	                               if(it.name.contains("HTTPStatusCode")){
                                         	                                                                      RequestStatus =  it.status.toString()
                                         	                                                                      log.info "HTTPStatusCode assertion status is $RequestStatus..."
                                         	                              } 
                                           }                                    



                                          //##Get TestStatus ##//
                                           if(RequestStatus == 'VALID' && Assertion == 'Pass' ){
	                                                                                            TestStatus = 'PASS' 
                                           }else{ 
	                                            TestStatus = 'FAIL'
                                           }
                                           
                                           //## Get Error Level ##//
                                          if(RequestStatus != 'VALID' && TestStatus == 'FAIL'){
	                                                                                            ErrorLevel = 'Error at Request Level'
                                          }else if(Assertion == 'Fail' && TestStatus == 'FAIL'){
	                                                                                           ErrorLevel = 'Error at Assertion Level'
                                          }else{
	                                           ErrorLevel = ''
                                          }



                                           //######################################//
                                          //## Establish SQL Server Connection ##//
                                          //####################################//
                                          def sql =Sql.newInstance("jdbc:sqlserver://SERVERNAME;Database=DATABASENAME;integratedSecurity=true","com.microsoft.sqlserver.jdbc.SQLServerDriver")
                                          log.info "Connected to SQL server..."

                                          //## Insert Test Results to SQL Server ##//     
			                           SQLQry = ""
			                           SQLQry += "insert into DATABASENAME.DBO.TEST_RESULT values ("
			                           SQLQry += "'" + TestSource                                + "', "
			                           SQLQry += "'" + TestCase                                  + "', "
			                           SQLQry += "'" + URL                                       + "', "
			                           SQLQry += "'" + httpStatusCode                            + "', "
			                           SQLQry += "'" + ResTime                                   + "', "
			                           SQLQry += "'" + TestExec                                  + "', "
			                           SQLQry += "'" + FileName                                  + "', "
			                           SQLQry += "'" + TestStatus                                + "', "
			                           SQLQry += "'" + ErrorLevel                                + "', " 
			                           SQLQry += "'" + date                                      + "'  " 
			                           SQLQry += ")" 
			
			                           log.info "Inserting record in SQL server..." 
			                           log.info "SQL statemement is...  $SQLQry"
			                           sql.execute SQLQry //Execute SQL statement//


                                          log.info "Closing SQL connection..."
                                          //## Close SQL connections ##//
                                          sql.close() //## SQL Server Connection ##//

                                          log.info "Finished ReportLogger..."
                                          log.info "-------------------------------------------------------"

                                           

}

Hope this helps!

 

To answer your question to test case status as Pass/Fail instead of Finished you could use below script.

 

def GetTCName = testRunner.getTestCase().getLabel()
def GetStatus = testRunner.getStatus()
long StartTime = testRunner.getStartTime()
def TimeTaken = testRunner.getTimeTaken()
def GetTime = new Date(StartTime + TimeTaken).format("MM/dd/yyyy hh:mm:ss a")


if("${GetStatus}" == 'FINISHED')
{
GetStatus = 'SUCCESSFUL'
}
else
{
GetStatus = 'FAIL'
}

log.info "Finished... " + "${GetTCName}" + " @ " + "${GetTime}" + " with status " + "${GetStatus}"
log.info "****************************************************"