cancel
Showing results for 
Search instead for 
Did you mean: 

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

SOLVED
sonya_m
Community Manager

[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:

ReadyAPI-TechCorner-Takeoff.pngReadyAPI-TechCorner-Contributor.png

 

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: Star_Gold.pngStar_Gold.pngStar_gray.png

 

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!


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
Tags (1)
1 ACCEPTED SOLUTION

Accepted Solutions
ChrisA
Community Leader

Call Database Connection Using Groovy Script

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.

View solution in original post

Tags (1)
6 REPLIES 6
HimanshuTayal
Community Hero

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

@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()
}

 

 

404.png


Click "Accept as Solution" if my answer has helped,
Remember to give "Kudos" 🙂 ↓↓↓↓↓



Thanks and Regards,
Himanshu Tayal
sonya_m
Community Manager

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

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 Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
sonya_m
Community Manager

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

@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?🙂


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
sonya_m
Community Manager

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

@richie @nmrao @avidCoder @aaronpliu 

Would you like to give this task a try?🙂


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
ChrisA
Community Leader

Call Database Connection Using Groovy Script

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.

View solution in original post

Tags (1)
sonya_m
Community Manager

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

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

 


Sonya Mihaljova
Community and Education Specialist

Did my reply answer your question? Give Kudos or Accept it as a Solution to help others. ⬇️⬇️⬇️
New Here?
Join us and watch the welcome video:
Top Kudoed Authors