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

  • JKolosova's avatar
    JKolosova
    SmartBear Alumni (Retired)

    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

      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