Ask a Question

How to keep database connection open or how to use one connection per testcase

SOLVED
Terkon
Occasional Contributor

How to keep database connection open or how to use one connection per testcase

Hi community,

 

For reasons I want to open a database connection to Oracle manually in groovy, not with the JDBC teststep.

And I want to keep this connection open the whole testcase or maybe even the whole load test.

 

I found some groovy example which look very promising.

if you are interested:

https://onebyteatatime.wordpress.com/2009/02/17/reusable-sql-connection-in-soapui/

 

And it works, as far as as open the DB connection.

Then in the snipped you see that this guy then sets a context porperty "dbConn".

connection = Sql.newInstance(dbString, dbUserName, dbPassword, dbDriver)
context.setProperty("dbConn", connection)

 

But in the next groovy script where I actually want to use the connectio to query and update a table, I cannot use this property how it suggested it should work:

 

if (context.getProperty("dbConn"))
{
...

This expression returns nothing... (checked with log.info ....)

 

So the question:

does anyone know how to do this?

Is it ieven possible? 

 

Using ReadyAPI 3.5.1

 

Thx for any hint 🙂

Terkon

1 ACCEPTED SOLUTION

Accepted Solutions
Terkon
Occasional Contributor

Re: How to keep database connection open or how to use one connection per testcase

Hi @JKolosova ,

 

thanks for the reply. Actually I was able to keep the connection as described in that link in my first post.

 

I put up the following script as "setup script" in the testcase level

//In a Setup Script
import groovy.sql.Sql
 
//try to create connection to database, if available. load this connection on context
//if not, log error and continue
//In order for this to work, you need to have jdbc driver jar file in $SOAPUI_HOME/bin/ext folder
def dbString = context.expand( '${#Project#dbString}' )
def dbDriver = context.expand( '${#Project#dbDriver}' )
def dbUserName = context.expand( '${#Project#dbUserName}' )
def dbPassword = context.expand( '${#Project#dbPassword}' )


if ( (null != dbString) && (null != dbDriver) && (null != dbUserName) && (null != dbPassword) ) 
{
  try {
    connection = Sql.newInstance(dbString, dbUserName, dbPassword, dbDriver)
    context.setProperty("dbConn", connection)    
    log.info "Hello DB"
  } catch (Exception e) { 	
  
    log.error "Could not establish connection to the database. " + e.toString()       
  }
}

 

The following I set up as a tear down script in the Testcase level

 

//In a TearDown Script
//Close db connection
if (context.dbConn)
{
  context.dbConn.close()
  log.info "Byebye DB Connection."
}

 

 

Then in a groovy teststep I am able to use this context.dbConn until the testcase is finished. Like this:

The query result are bit strange in format, thats why I put the log.info, so everybody who uses this, will see right away.

 

if (context.dbConn)
{
	try{		
  		log.info "Hello -connection available"
  
  		//connection to the database
  		def sql = context.dbConn
  		  
		
  		rowSerienNr = sql.firstRow("select nSerienNr from TableSerialNr where product = 'Screen'")  		
  		log.info "QueryResult " + rowSerienNr.toString()

		

	}
	catch(Exception e)
	{
		log.error e.toString()		
	}
}
else {log.error "Error"}

 

With these scripts I was able to just open 200 DB connections on Oracle instead of serveral 10.000 in a load test using the default JDBC request.

 

All kudos should go to the guy who posted the info in the link I copied in my first post

 

 

View solution in original post

3 REPLIES 3
JKolosova
Staff

Re: How to keep database connection open or how to use one connection per testcase

Hi @Terkon,

 

You cannot reuse a single connection between multiple Groovy script steps as the context can only store simple data types (like strings or numbers), not complex objects. However, you can create a script library, which will allow you to create a new DB connection with a single line of code every time when you need it and reuse the library script across all your Groovy test steps.

Some details on the approach are available in the following thread:
https://community.smartbear.com/t5/API-Functional-Security-Testing/DataBase-Connection-in-SetUp-scri...


Julia

Customer Care Team

Did my reply answer your question? Please give Kudos or Accept it as a Solution to help others.
Terkon
Occasional Contributor

Re: How to keep database connection open or how to use one connection per testcase

Hi @JKolosova ,

 

thanks for the reply. Actually I was able to keep the connection as described in that link in my first post.

 

I put up the following script as "setup script" in the testcase level

//In a Setup Script
import groovy.sql.Sql
 
//try to create connection to database, if available. load this connection on context
//if not, log error and continue
//In order for this to work, you need to have jdbc driver jar file in $SOAPUI_HOME/bin/ext folder
def dbString = context.expand( '${#Project#dbString}' )
def dbDriver = context.expand( '${#Project#dbDriver}' )
def dbUserName = context.expand( '${#Project#dbUserName}' )
def dbPassword = context.expand( '${#Project#dbPassword}' )


if ( (null != dbString) && (null != dbDriver) && (null != dbUserName) && (null != dbPassword) ) 
{
  try {
    connection = Sql.newInstance(dbString, dbUserName, dbPassword, dbDriver)
    context.setProperty("dbConn", connection)    
    log.info "Hello DB"
  } catch (Exception e) { 	
  
    log.error "Could not establish connection to the database. " + e.toString()       
  }
}

 

The following I set up as a tear down script in the Testcase level

 

//In a TearDown Script
//Close db connection
if (context.dbConn)
{
  context.dbConn.close()
  log.info "Byebye DB Connection."
}

 

 

Then in a groovy teststep I am able to use this context.dbConn until the testcase is finished. Like this:

The query result are bit strange in format, thats why I put the log.info, so everybody who uses this, will see right away.

 

if (context.dbConn)
{
	try{		
  		log.info "Hello -connection available"
  
  		//connection to the database
  		def sql = context.dbConn
  		  
		
  		rowSerienNr = sql.firstRow("select nSerienNr from TableSerialNr where product = 'Screen'")  		
  		log.info "QueryResult " + rowSerienNr.toString()

		

	}
	catch(Exception e)
	{
		log.error e.toString()		
	}
}
else {log.error "Error"}

 

With these scripts I was able to just open 200 DB connections on Oracle instead of serveral 10.000 in a load test using the default JDBC request.

 

All kudos should go to the guy who posted the info in the link I copied in my first post

 

 

View solution in original post

sonya_m
Community Manager

Re: How to keep database connection open or how to use one connection per testcase

Thank you for sharing! @Terkon 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
cancel
Showing results for 
Search instead for 
Did you mean: