Groovy provides very robust methods with sql instance to retrieve and manipulate the ResultSet.
There are methods for retrieving the first row, the ResultSet of some sql query, directly executing insert/update/delete queries.
Prerequisite: Obtain sql instance using
import groovy.sql.Sql
def sql = Sql.newInstance(dbpath, dbusr, dbpwd, dbdriver)
Below are some comprehensive examples which use the above sql instance.
firstRow( sqlQuery ) : This method returns the first row entity out of the ResultSet that is obtained by executing the query passed to it as argument.
def res = sql.firstRow(“SELECT * FROM TEST_TABLE WHERE USERID=’12345′”)
Result can accessed from res as
println( res[0] ) OR
println( res.COLUMN_NAME )
eachRow( sqlQuery, {closure} ) : This method is generally used to retain/modify the whole ResultSet based on some condition. The second argument of this method i.e., clousure actually consists as set of statements to be executed for each of the result set’s entity. For example
sql.eachRow( “SELECT * FROM TEST_TABLE WHERE USERID=’12345′”,
{
println( it.COLUMN_1 );
println( it[2] );
} )
The println statements (present as a clousure) above are executed for each entity while iterating over the ResultSet, and it refers to the entity of ResultSet that is currently being iterated over. Clousure can have any number of statements.
execute( sqlQuery ) : Generally this method is used to INSERT/UPDATE/DELETE records, as it doesn’t return any ResultSet as such.
sql.execute( “DELETE FROM TEST_TABLE WHERE USERID=’12345′ & USERNAME=’SOMENAME’ ” )
OR
sql.execute( “DELETE FROM TEST_TABLE WHERE USERID = ? & USERNAME = ? “,
[ "12345", "SOMENAME" ] )
Second type of usage is to some extent similar to PreparedStatement right? And the query could also be a INSERT/UPDATE statement, if developer wants to log some value(s) to database during execution of the TestStep.
Comments on: "Operations with sql instance" (6)
Also worth noting that the returned resultsetobject exhibits case-sensitivity regarding table column names.
for example if the table TEST_TABLE had a column called “TestStatus”, that can be accessed by:
res.TestStatus
but NOT by
res.teststatus
i need how to do oracle connection with soapui
Hi Abbas,
I think it may be correct or wrong…first check it.
try {
connKPDO = Sql.newInstance(“jdbc:oracle:thin:@MyOracleHost:1521:MyDB(dbUrl)”, “TestUser(username)”,
“Test(Password)”, “oracle.jdbc.driver.OracleDriver(drivername)”);
context.setProperty(“dbKPDO”, connKPDO)
}
catch(Exception e)
{
log.info “$e”
}
log.info context.getProperty(“dbKPDO”);
Greetings! I too am embarking on the journey to learn Grooviness! The primary use of Groovy scripts for me will be executing sql select and update statements from within a test case. I went to the file–> preferences option in soap ui pro thinking that that is where I am to set up the DB connection string? but alas, the expected option was not being presented. Is this something I needed to select in my initial soap ui pro set up? Anyway if you could possible assist me with getting my soapui talking to my SQL server DB I would be greatly appreciative!!
once I get the DB connection solved..there is another question that I have: namely in the statement above:
def res = sql.firstRow(“SELECT * FROM TEST_TABLE WHERE USERID=’12345′”)
How would groovy handle the situation where the literal ’12345′ would be replaced with a soapui property variable?
Thanx!
Hi Joan,
As an example I quoted this statement:
sql.firstRow(“SELECT * FROM TEST_TABLE WHERE USERID=’12345′”)
If you want to use a script variable to be passed to the query, you could do this by:
def userId = 12345;
sql.firstRow(“SELECT * FROM TEST_TABLE WHERE USERID = ? “, [userId]);
In the same way, instead of userId provide the property variable.
Hope this helps.
Thanks,
Devakara.