I guess you people are tired/bored of reading the last series of Groovy script related blog post (Actually i am, if not you ;-). So with this blog post we will focus on a feature which doesn’t require groovy script knowledge however it is one of the most grooviest feature provided by soapUI.
i am talking about the JDBC teststep in soapUI using which you can interact with your Database and perform quick validation of your teststep response. JDBC teststep uses the jdbc driver/connection to setup a connection with your DB and then it allows you(the tester) to pass/execute the SQL query or stored procedure. Then so executed jdbc teststep will provide the response in XML format (and can be viewed in tabular format with Pro version).
Adding a JDBC request teststep is the same old process of adding any regular teststep or groovy script. Attached snapshot highlighting the same.
Open the newly created teststep and configure the JDBC driver/connection string by clicking the configure button. Provide the required details in the “Database Configuration Wizard” :
Driver : select the respective driver for the connection (like SQL, Oracle, SAP, Sybase and so on)
Host : Name/IP address of the server where your database is residing (in my case it is at my localmachine, so localhost)
Port : let it remain the default port of communication or change if you know the open port on your DB server
User : username having the required permission to login & execute SQL queries/stored procedures.
Password : respective password for the entered username
Database : Your DB server may/may not have more than 1 databases. To execute your queries against specific Database provide the name in this field.
Clear properties : will clear all the entered data – somewhat similar to Reset feature.
TestConnection : click the Green colored icon to perform the check where your connection is running fine or not. You will get the “Connection Successful” message or an error message based on the parameters provided.
Now enter the SQL query/Stored Proc in the JDBC teststep (with Pro version you can use Build Query window) and execute the JDBC test step. The response returned by the DB will be displayed in the XML format (and tabular format – Pro version only) which can be asserted using simple assertion or advanced assertion (like xPath, xQuery, Script and so on).
NOTE : If you want to restrict your response size (i.e., number of rows) to any specific value, then use the TestStep property MaxRows as visible on bottom left (in above screenshot).
And the fetch size property is to “The fetch size is the number of rows physically retrieved from the database at one time by the JDBC driver as you scroll through a query ResultSet.” as mentioned in eviware forum [http://www.soapui.org/forum/viewtopic.php?f=1&t=2962]
Learn soapUI - Open Source 
hello Pb, excellent!!! I have successfully configure the JDBC connection with database.
thanks a lot,
Aaron
hi ,
i have a doubt,can the response fields obtained in the above jdbc steps be stored into properties too like how we do for normal response?
also can the same jdbc steps be incorporated by using a normal datasource step in soap pro and selecting jdbc from the drop down ther?if yes wat is the difference between the two?
hi,
yes, it can be. Just add a script assertion & write a code into the same to perform the needed operation. Also, each jdbc (like other test step) do store it’s response in a standard property value “ResponseAsXml”.
Yes, it is possible to use the same jdbc step as datasource. However, i didn’t had worked on this so can’t comment.
/pradeep bishnoi
Hi,
I am getting below error, when i test the connection.Could you suggest me on this!!
Can’t get the Connection for specified properties; java.sql.SQLException: Listener refused the connection with the following error: ORA-12505,
Hi,
No idea about the Oracle JDBC connection 😦 However a quick searched pointed me to this thread [http://www.soapui.org/forum/viewtopic.php?f=2&t=3962] might be of some help for you.
regards,
/pradeep bishnoi
Thanks for the information provided..
hi …can the same configuration be done for normal Soap ui(non PRO version)?
Yes, it is possible. NOTE: All the content shared over this blog are relevant to soapUI OPEN SOURCE version only.
Hi Pradeep,
I want to connect to database as sysdba. How to configure it using JDBC in soapui.
Same like what I am doing on shell prompt:
sqlplus / as sysdba
Thanks in advance,
Tim
Hi Tim or Sandeep?
Never worked on sysdba, so not sure if something extra is needed.
However, going by normal understanding if the driver for sysdba exists in soapui driver list then everything would be same and it should work like the other. Yes, one thing you might want to confirm would be lookout for the sysdba.jar file and place it in /bin/ext folder.
Regards,
{Pradeep Bishnoi}
I have made the JDBC connection successfully… how to match up the response of query present in JDBC request (in xml format)to response of SOAP ui request? You said we can use assertions…I have used assertion for just normal excel file …how to use it for JDBC request? Also I am using pro version but still am getting data in xml and not in tabular format…
Can you please help me for these questions?
Hi Pradeep,
Can you please help me for assertion of jdbc to soap response?
I have connected database and have that database response in xml and table format.
I have my soap response.
I wanted to know how to assert both of these?
Can you Please help me …its very urgent….
Hi,
Well both the teststep supports the Assertion and you can use them based on your needs.
Now if you want to match something between these 2 different teststeps( jdbc & xml request), you can use property transfer.
Property transfer would help you to pull the required data and store them a location (say Project level property).
Then simple Groovy script can be used to match the same value in both the properties.
regards,
{Pradeep Bishnoi}
Hi
As per my project need, I want to store the response of the xml in the DB (As the request parameter is given and the same value coming out as response).
How it can be done?
Hi,
Create the following sequence of the teststep in your testsuite/testCase.
– Input Property : which will have input value
– Test Request Step : actual request
– Groovy : Script to read the response from above teststep response and update into next property test step
– Output property Test Step : to store the out /response data in properties
– JDBC test step : which will contain the INSERT query which is parameterized to take input from above property test step.
Execute all of them in sequence and it is done. Else, other simple way is put a groovy assertion with code to setup the DB connection and perform insert using the same.
Hope this will be of some help.
{Pradeep Bishnoi}
Hi Pradeep,
In my tests I have to log in to the DB & check for logging in DB for every Request/Response Pair.
I query the DB based on a unique ID which I send in the SOAP Request.
In SOAP UI :
I’ve created a Test Suite in which the Test case has the following Test Steps :
1. Properties – which need to be passed to the SOAP Request
2. Groovy Script – which assigns Properties to the elements in the XML SOAP request based on XPath [Ex : holder(“ns1://Txn ID)=TransactionID]
3. SOAP Request
4. Loop – which loops for ‘n’ number of iterations.
For each iteration, the Groovy script assigns the properties from the Properties step
for ex : Properties Step has the following Properties :
(TransactionID1=111
Amount1=120
Item1=Notebook
TransactionID2=222
Amount2=100
Item2=Pen)
Groovy Script Step has the following line which assigns Properties to the SOAP Script :
def TxnID = testRunner.testCase.getTestStepByName(“Properties”).getPropertyValue(“TransactionID”+context.loopIndex)
And each time the loopIndex will iterate :
For 1st iteration – Transaction ID1, Amount1 & Item1 are passed & for 2nd Iteration – Transaction ID2, Amount2 & Item2)
Now I want to add a JDBC Step after the SOAP Request step which automatically pulls the TransactionID automatically for every iteration.
My SQL query would look something like :
select * from table_name where transaction = ” ”
I want to pass TransactionID1(=111) in first iteration & in the 2nd iteration TransactionID2(=222)
Manually : for every iteration i am able to execute the following query & get the results :
select * from table_name where transaction = $Properties#TransactionID1
I want to automate this step too.
Please advise,
Many Thanks
Hi,
I am not very clear about the steps & approach used by you.
Just wondering, why can’t you use the following :
select * from table_name where transaction = TxnID ( i.e., the variable from the Groovy Steps, you can store the value of the same in another Prop file and use that prop, it will work for sure)
Or
select * from table_name where transaction = “‘” + testRunner.testCase.getTestStepByName(“Properties”).getPropertyValue(“TransactionID”+context.loopIndex) + “‘”
Hope this will help.
Regards,
{Pradeep Bishnoi}
Thanks very Much. Your information also help me .
nice..
Hi,could you help me to create a webpage contains name,age,gender,mobile no.,email,address, submit,clear.Once we submit the above details,that details stored in the database.pls send me the entire code for that.
Hi PB, I am having windows authentication only and dont have separate sql authentication, so while connecting jdbc connection, it is showing invalid user id, how i can resolve this issue?
Hi Pradeep – can you please let me know what assertions can we have on a JDBC test step – I want to fail the test step if the SQL query did not retrieve any rows from DB. Thanks
Hello, I have a JDBC step setup already , and i could run a query sucessfully , but I want to use the JDBC Response in a groovy script ( which is my next step in my test case) how can i reference the JDBC Response in another step ??
Thank you!