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