Forum Discussion

Terkon's avatar
Terkon
Contributor
4 years ago
Solved

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

  • Terkon's avatar
    Terkon
    4 years ago

    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

     

     

3 Replies

Sort By
  • JKolosova's avatar
    JKolosova
    SmartBear Alumni (Retired)
    4 years ago

    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-script-and-use-the-same-in-Groovy/td-p/169514

    • Terkon's avatar
      Terkon
      Contributor
      4 years ago

      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