Get reusable DB connection

Refer to:
1. http://onebyteatatime.wordpress.com/2009/02/17/reusable-sql-connection-in-soapui/

2. http://groovyinsoapui.wordpress.com/2008/09/08/getting-db-connection-with-groovy/

3. http://www.soapui.org/userguide/functional/groovystep.html#soapUI_GroovyUtils

4.http://groovy.codehaus.org/Database+features

....

Reusable SQL Connection in soapUI

No matter how modern the systems get, we always often have some level of interaction with databases whether for reporting purposes or for recording transactions. In typical web services testing, one would imagine testing xml response to a given xml payload. What if, you need to connect to a database and say, verify, if a particular record was inserted by firing a select query? OR if, you need to create a log of your testing right inside the database? If not you may need to fire a select query after each SOAP Request, right in the Groovy Assertion Step of that request.

Now, quick and dirty way would be to create a SQL connection each time you need a connection and close it once you are done with it. Creating and closing a resource connection is very expensive. Hence, this could prove very costly when you are testing thousands of SOAP Requests. Imagine for each request you fire if there is a connection created whether in Groovy Assertion step or otherwise! I shall try to go over a simple mechanism to create a common reusable connection right at the beginning of your soapUI TestCase and then recycle it once you don’t need it.

The example I am trying to illustrate shall be useful when you have a single soapUI test case that, say, is iterating over number of rows from a datasource, carrying out some number of test steps and at each test step, before the step or after that step you need to connect to the database and carry out some operation.

To begin for any RDBMS you need four things: Driver, URL, Username and Password.
1. You can store these as TestCase properties, say, “dbUrl”, “dbDriverClassName”, “dbUser”, “dbPassword”

    For example: dbUrl:jdbc:microsoft:sqlserver://mssqlops1.prn-corp.com

                           dbDriverClassName:  com.microsoft.jdbc.sqlserver.SQLServerDriver

                           .......
2. Each soapUI test case has two scripts: Setup Script and TearDown Script. We shall make use of these two scripts in order to illustrate our example. Inside Setup Script we shall obtain handle to our common connection as shown below:

view plain copy to clipboard print ?

1.   
2. //In a Setup Script   
3. import groovy.sql.Sql   
4.   
5. //try to create connection to database, if available. load this connection on context   
6. //if not, log error and continue   
7. //In order for this to work, you need to have jdbc driver jar file in $SOAPUI_HOME/bin/ext folder   
8. def url = context.expand( '${#TestCase#dbUrl}' )   
9. def driver = context.expand( '${#TestCase#dbDriverClassName}' )   
10. def user = context.expand( '${#TestCase#dbUser}' )   
11. def password = context.expand( '${#TestCase#dbPassword}' )   
12.   
13. if ( (null != url) && (null != driver) && (null != user) && (null != password) )   
14. {   
15.   try {   
16.     connection = Sql.newInstance(url, user, password, driver)   
17.     context.setProperty("dbConn", connection)   
18.   } catch (Exception e) {   
19.     log.error "Could not establish connection to the database."  
20.   }   
21. }  

//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 url = context.expand( '${#TestCase#dbUrl}' )
def driver = context.expand( '${#TestCase#dbDriverClassName}' )
def user = context.expand( '${#TestCase#dbUser}' )
def password = context.expand( '${#TestCase#dbPassword}' )

if ( (null != url) && (null != driver) && (null != user) && (null != password) )
{
  try {
    connection = Sql.newInstance(url, user, password, driver)
    context.setProperty("dbConn", connection)
  } catch (Exception e) {
    log.error "Could not establish connection to the database."
  }
}

3. Now wherever we need access to this connection in any of our test steps: whether in Groovy Assertion or in Groovy Script, we simply check for existence of this property on the soapUI context, for example, say following Groovy Assertion verifies if a customer account was created in the database right after SOAP Request CreateCustomer.

view plain copy to clipboard print ?

1. //In a Groovy Assertion Step of a SOAP Request   
2. def MSG_CUSTOMER_NOT_FOUND = "Customer not found!"  
3. // Obtain customer number from response of a SOAP Request that creates a customer   
4. def customerNumber = <... groovy code retrieving customer number from response here ...>    
5.   
6. //Check if connection to database is available   
7. if (context.dbConn)   
8. {   
9.   //connection to the database   
10.   def sql = context.dbConn   
11.   
12.   row = sql.firstRow("select count(*) as numOfRecords from customers where customer_number = ? ", [customerNumber])   
13.   
14.   //Verify that customer record exists in Customer Table in the database   
15.   assert ( 1 == row.numOfRecords ):MSG_CUSTOMER_NOT_FOUND   
16. }  

  //In a Groovy Assertion Step of a SOAP Request
  def MSG_CUSTOMER_NOT_FOUND = "Customer not found!"
  // Obtain customer number from response of a SOAP Request that creates a customer
  def customerNumber = <... groovy code retrieving customer number from response here ...> 

  //Check if connection to database is available
  if (context.dbConn)
  {
    //connection to the database
    def sql = context.dbConn

    row = sql.firstRow("select count(*) as numOfRecords from customers where customer_number = ? ", [customerNumber])

    //Verify that customer record exists in Customer Table in the database
    assert ( 1 == row.numOfRecords ):MSG_CUSTOMER_NOT_FOUND
  }

4. Finally, in TearDown Script, we close the connection:

view plain copy to clipboard print ?

1.   
2. //In a TearDown Script   
3. //Close db connection   
4. if (context.dbConn)   
5. {   
6.   context.dbConn.close()   
7.   log.info "Closed Database Connection."  
8. }  

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

Ain’t that simple and reusable? Of course, you can extend this idea to create a connection pool or any other advanced set of objects, map of objects that you can hold onto soapUI context and use whenever needed. However, do not forget to clear up these resources though. That is what the TearDown Script is for!

~srs

Posted in Groovy, Programming. Tags: groovy, SOAP, soapUI, Web Services. 2 Comments »

2 Responses to “Reusable SQL Connection in soapUI”

1. Tom Says:
   February 19, 2009 at 11:59 am

   Hi Sachin,
   I tried this connection below and ran into a problem.

   If I am using an Oracle Thin driver what should the
   dbDriverClassName property value be?

   I am using oracle.jdbc.driver.OracleDriver and also tried jdbc:oracle:thin

   Any ideas?

   Thanks,

   Reply
   • onebyteatatime Says:
     February 19, 2009 at 12:14 pm

     You can check the Oracle driver documentation in order to figure out exact driver class name. But the class name you just provided seems correct. Have you made sure that you have the driver “jar” in <soapui_home>/bin/ext directory? That is very important. You have to have the driver jar in this directory. Once you start soapUI you shall see this jar file being loaded in console log of soapUI. Once this jar file is loaded at soapUI start up, you should be fine. I think I have documented this point as a comment in my sample script.

     hope that helps.

     ~srs

     Reply
     

     getting DB connection with Groovy

     Posted by: devakara on: September 8, 2008

     • In: Groovy
     • Comment!

     Connecting to DB inside Test Steps for various processes is required most often in any Test Suite. So how do we get a connection of DB2 database (for that matter any database) using Groovy in SOAP UI ?

     Its simple, basically we will get an sql instance first using parameters like JDBC connection URL, driver class name etc..

     This link refers to the Sql class’s API which is used to get the connection

     Say if we use the generic method getInstance(url, user, pwd, driverName) to get the instance and thence execute some SQL queries, we could proceed this way:

     The pre-setup for this would be placing the required jars for DB connectivity in lib folder of SOAP UI and setting their paths to the CLASSPATH variable in bin/soapui.bat file. (In case of DB2 connection, we need to place db2jcc.jar and db2jcc_license_cu.jar jars inside lib directory and set their paths to CLASSPATH variable in soapui.bat file of bin directory). Then,

     1) Import groovy.sql.Sql in the Groovy Step, by including

     import groovy.sql.Sql

     2) Then get the required arguments for Sql.getInstance() method, and fetch the sql instance by

     def  sql = Sql.newInstance(dbPath, dbUserName, dbPassword, dbDriverName);

     Choice of newInstance() method could be as per the options developer has.

     3) Use this sql object for executing the queries to perform operation on DB, for example

     res = sql.execute( “SELECT * FROM TABLE1 WHERE COL1=’123′” );

     Tags: DB, DB Connection, DB2, execute, Sql

     26 Responses to "getting DB connection with Groovy"

   • 23 | Igor
     May 19, 2009 at 10:56 pm

     Got it,

     For 2005, the driver changes from
     com.microsoft.sqlserver.jdbc.SQLServerDriver (sql 2000)

     To

     com.microsoft.jdbc.sqlserver.SQLServerDriver

     See: http://blogs.msdn.com/jdbcteam/archive/2007/06/15/java-lang-classnotfoundexception-com-microsoft-jdbc-sqlserver-sqlserverdriver.aspx

     Thanks anyway!
     Igor

     Reply