getting DB connection with Groovy
Posted by: devakara on: September 8, 2008
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′” );
(Please feel free to get back if u have any trouble…as I’m just a mail away…leave a comment otherwise)
26 Responses to "getting DB connection with Groovy"
September 10, 2008 at 5:28 pm
Ravi,
Have you added the jar locations to CLASSPATH variable present in soapui.bat file of \bin folder?
After adding them to the CLASSPATH, restart the SOAP UI so that changes can take effect!
Good luck,
Devakara.
September 16, 2008 at 6:12 pm
Hi!
Fantastic site!
In regard to the initial comment; the jars should be put in the soapui\bin\ext folder to be automatically picked up by soapUI, not in the soapui\lib folder (which requires classpath modification as commented)..
regards!
/Ole
eviware.com
September 16, 2008 at 6:36 pm
Thank you very much Ole!
What you said is absolute right, and thats why I immediately posted regarding the ‘ext’ folder and its usage.
Please have a look on it, and provide your feedback.
Thanks again,
Devakara.
October 16, 2008 at 6:47 pm
Cen somebody please help me HOW CAN I MAKE a very single connection to database and fetch just one result as example. I am new in soapUI and I only need ONE working example. (Still wrong jdbc driver or what).
This is example of my database> (false usr/psw)
Server> mysql.webzdarma.cz
Databáze> myDatabase
username> usr
password> psw
——————- simple groovy code in SOAP UI to connect to one table in myDatabase and get just something.????
i dont have any jar files added in my instaled new soapUI PRO beta directory
October 23, 2008 at 7:59 pm
Hi,
Sorry for delay!
For getting DB connection you need to place the DB connectivity jars in ext folder present in lib folder of SOAPUI, and restart the SOAP UI for changes to take effet.
And I have explained, how to retrieve/manipulate records from DB using the sql instance, at http://groovyinsoapui.wordpress.com/2008/10/17/operations-with-sql-instance/
Regards,
Devakara.
November 17, 2008 at 5:33 pm
Hi Ole , Devakara
I am unaable to connect the SOAP UI PRO with MySQL I have placed .jar file in bin\ext directory and restarted the tool .
And i tried this script in my machine
import groovy.sql.Sql
sql = Sql.newInstance(“jdbc:mysql://localhost:3306/eviware_demo_datainsert”, “evidemo”, “evipass”, “com.mysql.jdbc.Driver”)
Error mess was thrown :
Mon Nov 17 17:30:42 GMT+05:30 2008:ERROR:java.sql.SQLException: Access denied for user ‘evidemo’@'localhost’ (using password: YES)
Can please tell me what i am suppose to do to over this problem
November 18, 2008 at 1:18 pm
Hi,
I’m not sure whether you have enabled access for the user ‘evidemo’ (as quoted). And appropriately granted permissions as required.
(If so, you should be able to access the tables from any data base client also)
Please check on that…and revert if more help is needed.
Thanks,
Devakara.
November 18, 2008 at 4:02 pm
Hi Devakara ,
First i need to Thank you for your response .
Well I am not sure whether i have enabled access to the user ‘Evidemo’ … can you just describe the steps to create the connection I think it will be very useful for me …and also tell me how to grant permission to the users .
Waiting for your reply
November 18, 2008 at 5:31 pm
Hi Devakara,
Waiting for your reply…. I am testing the connection between SOAP UI and Mysql with the string
import groovy.sql.Sql
sql = Sql.newInstance(”jdbc:mysql://localhost:3306/eviware_demo_datainsert”, “evidemo”, “evipass”, “com.mysql.jdbc.Driver”)
still in i am getting the same error…. can you please help me in this …. If you can briefly send the procedure can you mail it to my id i.e anandkirang@yahoo.com
Thanks a lot
November 21, 2008 at 9:35 am
Hi friends ,
I got a solution finally the problem which i was facing with the user id of MYSQL initially when we install MYSQL it wont prompt for any any new root id creation. So by default our root id will be ROOT and the password is– user defined …. Now i am able to store the values in the database and retrieve
The string which worked for me is
import groovy.sql.Sql
sql = Sql.newInstance(”jdbc:mysql://localhost:3306/eviware_demo_datainsert”, “root”, “evipass”, “com.mysql.jdbc.Driver”)
hope this is clear
Thanks
November 22, 2008 at 2:06 pm
Thanks for getting back with the fix.
Nevertheless, we can create our own profiles (root ids) and grant permissions accordingly in MySql.
Regards,
Devakara.
December 3, 2008 at 4:34 pm
Hi devakara ,
this blog is really helping a lot , let me check weather my problems also get clear here,
comes to my problem
1. i have 170 request parameters in request xml for that i need to do 170 times property transwer its taking most of my time is there any groovy script for transwer the properties with out property transwer one more thing i know the replacement of property with{property} in xml ,but i want original value to be replaced
2.i have again 170 assertions like
assert localvarible=dbfetchedvalue
.
.like 170 lines
if any of the assert is failing entire thing is comming out of execution is there any coding like listing the failured statements and passing statements
3. i have seen the coding like if a text file contains 100 properties we have groovy code to import all in properties step in the same way how can i retreive all properties from properties to local varibles
iknow the step like
def var = propeties….(propertiesname);
i want like a for loop in single loop i want to access all the properties
please help me out
December 8, 2008 at 10:48 pm
Sorry for the delay Suman,
1. You can just have Groovy Script Step for doing this transfer –
def groovyUtils = new com.eviware.soapui.support.GroovyUtils( context )
def requsetHolder = groovyUtils.getXmlHolder( messageExchange.requestContent )
requestHolder.setNodeValue(“//ns1:RequestParameter…”, “parameter_value”)
If you want all the 170 node values to be changed, you might need to explicitly write so many setNodeValue(…) statements; or if they are following some pattern you could simply use looping to do it.
2. You could do simple String(Object) comparison under an if clause, instead of asserting on the values.
if(localVariable_value == dbFetched_value) log.info(….)
else log.info(….)
3. To access the properties in Groovy script as you said, refer to http://groovyinsoapui.wordpress.com/2008/09/04/properties-in-soapui/
Please get back if more help is needed.
Thanks,
Devakara.
December 4, 2008 at 4:05 pm
I’m trying to do the same, but using Maven2 Plugin,
but I’m getting a java.lang.ClassNotFoundException
for my jdbc driver, but I don’t know where I need to configure the driver.
Thanks
December 8, 2008 at 10:57 pm
Hi
Sorry for the delay.
Could you please send some more details (error details or stact trace) corresponding to this error.
(Or else) Please let me know the status on this.
Thanks,
Devakara.
January 20, 2009 at 6:07 pm
Hi dev thanks for the reply its really working a lot , i have one more question to ask you , i am using soap ui pro trail version in that for datasource step the parameters requiredis driver name and connection string
i have mysql1.5 driver is installed in my machine and dsn name is suman
as you said in the previous i have jar files in ext folder
is there any differnce b/w jdbc driver and odbc driver if so
how can i work with soapui pro
please send me the sample data
i want
driver name=======? what need to be enter
i have entered mysql odbc 1.5 and com.jdbc:odbc
connection string=====?
please send reply as soon as possible
once again appreciating your participation
January 28, 2009 at 5:40 pm
Hi Deva,
we have progressed a little bit in soapui testing now we want to do data driven testing from excel and we need to update the same result back to the excel
the problem here is once we have one row of data the one iteration is completed how to proceed with the next row of data
could you please send me any codings you have that will read data from an excel and place in a request xml and read data from response xml and update in the same excel
if i get this i will be out of mess
please!!! i am waiting for your response
Thanks,
Suman.
January 28, 2009 at 5:42 pm
sorry to inform you about the above post , we can do that using soapui pro ,, the client asked us to do in SOAPUI only that is where we are struckking
February 2, 2009 at 1:26 pm
Hi Deva , i have created a java class which have methode implemantaions of all connection string to all possible Dbs and created a jar file for that ,, and i palced in ext folder
then i have used
import “the project package name”
def drivername=”xxxxx”
def usr=”xxxx”
def pwd=”xxxx”
connect c1 = new connect();
c1.setMSaccess(dbname,usr,pwd);
but the above coding is not working
i have a code that have the class name connect and class have implemantations to all set methodes to all db types
please help me out
Thanks & Regards
Suman.
February 2, 2009 at 3:02 pm
Hi Suman,
After placing the jar in ext folder, restart the SOAP UI once.
On the startup it loads all the jars (by default) present in ext folder. (You could verify the sysouts printed on console)
Try to run the same above snippet now, it should work.
Hope this helps.
Thanks,
Devakara
February 2, 2009 at 6:22 pm
we have restarted the soapui and we are getting unable to resolve the class.(class file is not loading, please help to access the jar into saopui). after restarting we observe the startup of the soapui the jar has been loaded, but the thing is we are not anle to access the jar file in soapui
Our Java Code: made as jar file.
package New_Connection;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.Statement;
public class newcon {
public void getgetConnectionMysql(){
}
public void getConnectionOracle(){
}
public void getConnecttoExcel(){}
public boolean getConnectionMsaccess ( String Filename, String Query){
Connection c;
String filenam =Filename;
String url = “jdbc:odbc:Driver={Microsoft Access Driver (*.mdb)};DBQ=”;
url+=filenam.trim()+”;DriverID=22;READONLY=true}”;
String query = Query;
String query_result = “”;
int col;
int pos;
try
{
System.out.println(“connecting to database”);
Class.forName(“sun.jdbc.odbc.JdbcOdbcDriver”);
c = DriverManager.getConnection(url);
Statement s = c.createStatement();
ResultSet rs = s.executeQuery(query);
col = (rs.getMetaData()).getColumnCount();
while(rs.next()){
for(pos =1;pos<=col;pos++){
query_result+= rs.getString(pos)+"";
}
System.out.println(query_result);
}
}
catch(Exception x){
x.printStackTrace();
}
return true;
}
}
and the Groovy code we did like this:
package soapui.newcon;
import com eviware .soapui.newcon;
newcon c = new newccon();
c." here we have call the actual method with arguments" but we are getting an error message, please reply.
February 2, 2009 at 6:59 pm
you may have this questions also in your mind
wether the jar file is correct or not ?
for that we have reloaded the jar file in eclipse using java build path and external jars that way it was working fine
we donno y its not working groovy
May 19, 2009 at 10:47 pm
I have putted the follwoing jar files in C:\Java\soapUI-2.0.2\lib
- msbase.jar
- mssqlserver.jar
- msutil.jar
added the jar locations to CLASSPATH variable present in soapui.bat file of \bin folder
checked the environment variables and the log :
[SoapUI] Added [file:/C:/progra%20Files/eviware/soapUI-Pro-2.5.1/bin/ext/msbase.jar\ to classpath
[SoapUI] Added [file:/C:/progra%20Files/eviware/soapUI-Pro-2.5.1/bin/ext/mssqlserver.jar\ to classpath
[SoapUI] Added [file:/C:/progra%20Files/eviware/soapUI-Pro-2.5.1/bin/ext/msutil.jar\ to classpath
but still getting the following error where trying to connect to SQL Express 2005 i am facing the follwoing problem when i try to connect with SQL Server 2005 Express Edition
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver
May 19, 2009 at 10:50 pm
Sorry, regarding the previous reply, I used the /ext directory, as explained on the log I put for, both, the .jar files and the CLASSPATH
Could you provide me please with some suggestions as to what else to do?
I’d really appreciate it.
Best regards,
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
Thanks anyway!
Igor
Leave a Reply
Recent Comments
 | George on Property Transfer in SOAP… |
 | Kathy on Property Transfer in SOAP… |
 | Arno on accessing properties in SOAP… |
 | Chrissy on Property Transfer in SOAP… |
 | devakara on Property Transfer in SOAP… |
Join me at…
September 9, 2008 at 7:14 pm
I have putted the follwoing jar files in C:\Java\soapUI-2.0.2\lib
- msbase.jar
- mssqlserver.jar
- msutil.jar
but i am facing the follwoing problem when i try to connect with SQL Server 2000
java.lang.ClassNotFoundException: com.microsoft.jdbc.sqlserver.SQLServerDriver