Operations with sql instance

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.

(Please feel free to get back if u have any trouble…as i’m just a mail away…otherwise leave a comment)