soapUI Pro has a built in test step named JDBC Request to directly establish a connection to the database and get the results back in XML format. Connection to a database can also be achieved using Groovy Script. Let us see an example on how can we do a database connection using groovy script and get the records back using soapUI.
To do this, first create a Groovy Script test step in your soapUI test case (This can also be used in Script Assertion) and use the below code.
import groovy.sql.Sql;
def con = Sql.newInstance("jdbc:oracle:thin:@<dbserver url>:<port>:<SID>", "<db user name>", "<db password>", "oracle.jdbc.driver.OracleDriver");
def res = con.rows("SELECT sysdate FROM dual")
log.info(res[0].sysdate.toString())
con.close()
The above lines of code is making use of the method newInstance in the package groovy.sql.Sql to establish a connection to the database. This select the system date from oracle table dual and print to the log.
Posts
Please Can you write the script to connect to SQL Server
Hey Thanks for your post.Was quite helpful
Could you pleas help me, how to connect mssql using groovy script ?
I've received an exception with the following message "java.lang.classNotFoundException oracle.jdbc.driver.oracledriver"
thanks for ur code
very helpful
Could you pleas help me, how to connect mysql using groovy script ?
java.sql.SQLException: No suitable driver found for. Tried using odbc6 or 14 none of them does not worked
Need help:
Upgraded my ReadyAPI to 2.2 version from 2.0. I have groovy script to connect to Databases defined at project level. It is throwing following error as below after upgrade.
Caused by: groovy.lang.MissingMethodException: No signature of method: com.eviware.soapui.impl.wsdl.support.connections.DefaultDatabaseConnectionContainer.getDatabaseConnectionByName() is applicable for argument types: (java.lang.String) values: [MyDB]
at org.codehaus.groovy.runtime.ScriptBytecodeAdapter.unwrap(ScriptBytecodeAdapter.java:58)
at org.codehaus.groovy.runtime.callsite.PojoMetaClassSite.call(PojoMetaClassSite.java:49)
at org.codehaus.groovy.runtime.callsite.CallSiteArray.defaultCall(CallSiteArray.java:48)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:113)
at org.codehaus.groovy.runtime.callsite.AbstractCallSite.call(AbstractCallSite.java:125)
at Script3.run(Script3.groovy:11)
at com.eviware.soapui.support.scripting.groovy.SoapUIGroovyScriptEngine.run(SoapUIGroovyScriptEngine.java:98)
at com.eviware.soapui.support.scripting.groovy.SoapUIProGroovyScriptEngineFactory$SoapUIProGroovyScriptEngine.run(SoapUIProGroovyScriptEngineFactory.java:82)
at com.eviware.soapui.impl.wsdl.testcase.WsdlTestCase.runScript(WsdlTestCase.java:984)
at com.eviware.soapui.impl.wsdl.testcase.WsdlTestCase.runSetupScript(WsdlTestCase.java:989)
... 9 more
My Script:
import groovy.sql.Sql
com.eviware.soapui.support.GroovyUtils.registerJdbcDriver("com.microsoft.sqlserver.jdbc.SQLServerDriver");
def rbConObj = context.testCase.testSuite.project.databaseConnectionContainer.getDatabaseConnectionByName("MyDB");
def rbconstring = rbConObj.getConnectionString();
def sql = Sql.newInstance(rbconstring);
sql.executeUpdate("UPDATE Table SET Column = 2 Where ID = 1505")