Forum Discussion

sonya_m's avatar
sonya_m
SmartBear Alumni (Retired)
5 years ago

[TechCorner Challenge #13] - Call Database Connection Using Groovy Script

Hello ReadyAPI Community!

 

We have returned with a task for you to attempt. 

See the participation rules and the TechCorner Leaderboard here.

 

Participate in the challenge and earn new unique badges! Check out some of these awesome rewards:

 

In this week’s task, we will be having you use scripting to connect to a database.

 

Task: Create a Groovy script that will connect to a database

Difficulty: 

 

Steps to follow:

 

1. Create a database connection using the ReadyAPI interface.

2. From your script, call the database connection, connect to the database, and return a success message on a successful connection to the database.

 

Good Luck!

  • Task: Create a Groovy script that will connect to a database

     

    This is a solution created for [TechCorner Challenge #13]

     

    Hi,

     

    I did this a while back and it might aid others...

     

    For context, I have several environments, e.g. Dev, Test and Pre-Prod.  Each environment is hooked up to its own database.

     

    Nothing new here, but I use the SoapUI environments functionality to define the separate root URLs for each environment as well as defining the db details.  This enables me to quickly switch between environments and know that where a db connection is required, I'm going to hit the right db.

     

    I found my self in a situation where I was using a Groovy script to connect to the db in a lot of tests.  This meant that if anything changed, I'd need to update the db connection snippet in a lot of places.  This was unmaintainable.  I extracted my snippet and created a Groovy class which lives 'outside' of SoapUI GUI.  The script is in ….\SoapUIRoot\bin\scripts\groovyScripts\org.  I use a lot of external scripts.  I like the maintainability and reuse aspect.

     

    Back to the challenge, here is my groovy class for connecting to a named db connection for an environment.

     

     

    package groovyScripts.org
    
    import groovy.sql.Sql
    
    class jdbcConnections
    {
        jdbcConnections()
        {
            // Empty constructor	
        }	
    	
        def getJdbcConnection(connName, log)
        {
            // Get the db connection details for the named connection 
            // in the active environment.  There's probably a more concise way to
            // get to this value.
            def connDetails = messageExchange.modelItem.testStep.testCase.testSuite.project.activeEnvironment.databaseConnectionContainer.getDatabaseConnectionByName(connName);
    
            def connString = conDetails.getConnectionString()
            def url = 'jdbc:oracle:thin:' +   connString.substring(connString.indexOf('@'),connString.size());
            def userName = connString.replace('jdbc:oracle:thin:', '');
            userName = userName.substring(0,userName.indexOf('/'));
            def password = connString.substring(connString.indexOf('/') + 1,connString.indexOf('@'));
    
            log.info('Connecting to database ' + conDetails.getName() + '.  Using account ' + userName + ' at URL ' + url );
    		
            return Sql.newInstance(url, userName, password, conDetails.getDriver());
    
        }
    }

     

     

     

    Using the above, I can get a named connection in any Groovy Test Step or in other external Groovy scripts I call from my SoapUI tests.

     

    Example:-

     

     

    // Instantiate an object
    def jdbcConnectionObj = new groovyScripts.org.jdbcConnections();
    def myDbConn = jdbcConnectionObj.getJdbcConnection("dbNameFromEnvironment", log);
    
    // You can now query...
    def sqlStatement = "select id, name from customer";
    def sqlRecord = myDbConn.rows(sqlStatement);
    
    def customerName = sqlRecord[0]["NAME"];
    log.info("First customer name " + customerName);
    
    // When finished, always close...
    myDbConn.close();

     

     

     

    There's room for improvement by adding error handling etc. but it should be enough to get you going.

  • Task: Create a Groovy script that will connect to a database

     

    This is a solution created for [TechCorner Challenge #13]

     

    Hi,

     

    I did this a while back and it might aid others...

     

    For context, I have several environments, e.g. Dev, Test and Pre-Prod.  Each environment is hooked up to its own database.

     

    Nothing new here, but I use the SoapUI environments functionality to define the separate root URLs for each environment as well as defining the db details.  This enables me to quickly switch between environments and know that where a db connection is required, I'm going to hit the right db.

     

    I found my self in a situation where I was using a Groovy script to connect to the db in a lot of tests.  This meant that if anything changed, I'd need to update the db connection snippet in a lot of places.  This was unmaintainable.  I extracted my snippet and created a Groovy class which lives 'outside' of SoapUI GUI.  The script is in ….\SoapUIRoot\bin\scripts\groovyScripts\org.  I use a lot of external scripts.  I like the maintainability and reuse aspect.

     

    Back to the challenge, here is my groovy class for connecting to a named db connection for an environment.

     

     

    package groovyScripts.org
    
    import groovy.sql.Sql
    
    class jdbcConnections
    {
        jdbcConnections()
        {
            // Empty constructor	
        }	
    	
        def getJdbcConnection(connName, log)
        {
            // Get the db connection details for the named connection 
            // in the active environment.  There's probably a more concise way to
            // get to this value.
            def connDetails = messageExchange.modelItem.testStep.testCase.testSuite.project.activeEnvironment.databaseConnectionContainer.getDatabaseConnectionByName(connName);
    
            def connString = conDetails.getConnectionString()
            def url = 'jdbc:oracle:thin:' +   connString.substring(connString.indexOf('@'),connString.size());
            def userName = connString.replace('jdbc:oracle:thin:', '');
            userName = userName.substring(0,userName.indexOf('/'));
            def password = connString.substring(connString.indexOf('/') + 1,connString.indexOf('@'));
    
            log.info('Connecting to database ' + conDetails.getName() + '.  Using account ' + userName + ' at URL ' + url );
    		
            return Sql.newInstance(url, userName, password, conDetails.getDriver());
    
        }
    }

     

     

     

    Using the above, I can get a named connection in any Groovy Test Step or in other external Groovy scripts I call from my SoapUI tests.

     

    Example:-

     

     

    // Instantiate an object
    def jdbcConnectionObj = new groovyScripts.org.jdbcConnections();
    def myDbConn = jdbcConnectionObj.getJdbcConnection("dbNameFromEnvironment", log);
    
    // You can now query...
    def sqlStatement = "select id, name from customer";
    def sqlRecord = myDbConn.rows(sqlStatement);
    
    def customerName = sqlRecord[0]["NAME"];
    log.info("First customer name " + customerName);
    
    // When finished, always close...
    myDbConn.close();

     

     

     

    There's room for improvement by adding error handling etc. but it should be enough to get you going.

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)

      ChrisA Thank you for providing the Community with a real-life use case and the script! Great job! 

       

  • sonya_m : Getting 404 error on the provided link refer attached screenshot, however below script i am using to connect to mongo database with groovy script and getting value according to my desired condition. Hope it will work 🙂

     

    1. Pre-requisite :  Need to add mongodb jar into soapUI/bin/ext folder

     

     

    import com.mongodb.BasicDBObject;
    import com.mongodb.DB;
    import com.mongodb.DBCollection;
    import com.mongodb.DBCursor;
    import com.mongodb.DBObject;
    import com.mongodb.MongoClient;
    import com.mongodb.MongoClientURI;
    
    
    //DB URL
    String url = "mongodb://USERNAME:PASSWORD@HOST:POST/?authSource=IF___ANY&authMechanism=IF___ANY"
    
    //passing URL into MongoClientURI
    MongoClientURI client = new MongoClientURI(url);
    //passing URI into MongoCLient
    MongoClient mongoClient = new MongoClient(client);
    try{
    	//Fetching data from DB
    	DB db = mongoClient.getDB("DB__NAME");
    	
    	DBCollection collec = db.getCollection("COLLECTION__NAME");
    	BasicDBObject query = new BasicDBObject();
    	//seraching into DB based to condition
    	query.append("CONDITION",CONDITION_VALUE);
    	DBCursor cursor = collec.find(query);
    	DBObject doc = cursor.next();
    	//fetching data from particular parameter
    	mongoID =  doc.get("PARAMETER__NAME").toString();
    }
    catch(Exception e){
    	log.info "Excelption Occured :: "+e.toString();
    }
    finally{
    	mongoClient.close()
    }

     

     

    • sonya_m's avatar
      sonya_m
      SmartBear Alumni (Retired)

      Thank you for letting me know about the link HimanshuTayal  - all fixed now🙂 

       

      And thank you for providing a script so quickly!👍 Allow us a bit of time to check it!

       

      • sonya_m's avatar
        sonya_m
        SmartBear Alumni (Retired)

        HimanshuTayal Thanks for the script one more time 🙂

        I see that your script is creating a new connection, however, to comply with the task, it should be using a connection already created using the ReadyAPI interface. 

         

        Would you like to try modifying it?🙂