Using SQLUnit and TDD our stored procedures

Pain Points

Q: When I update SP code, I don't know if the new code breaks the existing logic. What can help me to find the bugs introducing by the added code easily?

A: TDD

Q: It's difficult to unit test SP and UDF. Is there any useful tool to help me to do that?

A: SQLUnit and EU4DB

SQLUnit --- Get Started

<?xml version="1.0"?>
<!DOCTYPE sqlunit SYSTEM "../lib/sqlunit.dtd" [
]>


<sqlunit>

  <connection server-name="ibmdb2" extern="duke" />

  <setup>  </setup>

  <test name="Testing Hello World ">
    <call>
      <stmt>{call HELLO_WORLD (?, ?)}</stmt>
		<param id="1" name="piName" type="CHAR" inout="in">Will</param>
		<param id="2" name="poMsg" type="CHAR" inout="out">${poMsg}</param>
	  </call>
	  <result>
		<outparam id="1" name="poMsg" type="CHAR">Hello, Will!</outparam>
      </result>
  </test>
  
  <teardown>  </teardown>

</sqlunit>

SQLUnit --- Connections

Some customization to make SQLUnit to support DB2. <#+BEGIN_SRC >

##############################################################################

############################################################################## ibmdb2.SMALLINT.class = net.sourceforge.sqlunit.types.IntegerType ibmdb2.SMALLINT.type = 5

#+END_SRC

Using connection tag to define a connection

<#+BEGIN_SRC >

Importing a xml file to define a connection

<?xml version="1.0"?>
 <!DOCTYPE sqlunit SYSTEM "file:sqlunit/lib/sqlunit.dtd" [
  <!ENTITY connection SYSTEM "file:@CONF_DIR@/sqlunitConnectionConfig.xml">
 ]>
 
 <sqlunit>

	&connection;
	<setup></setup>  

  ...

<#+BEGIN_SRC >

sqlunit.driver = COM.ibm.db2.__my_company_1__bc.app.DB2Driver sqlunit.url = __my_company_1__bc:db2:sample

#+END_SRC

SQLUnit --- Setup & Teardown

Set variable

<#+BEGIN_SRC >

Setup test data

<sql>
     <stmt>INSERT INTO sods2.ibm_emp_dlgtn (
      	   IBM_EMP_NUM, EMP_CNTRY_CODE, PROG_CODE, MIGRTN_CODE, REVN_STREAM_CODE,
		   SAP_SALES_ORG_CODE, PAYER_CNTRY_CODE, PROG_MIGRTN_CODE,
		   DLGTN_PCT, VALID_TO_DATE, VALID_FROM_DATE, EMP_NAME_FULL,
		   INTL_PHN_NUM_FULL, INTL_FAX_NUM_FULL, EMAIL_ADR,
		   INACT_FLAG, MOD_DATE, MOD_BY_HRCL_NAME)
		   VALUES(?, ?, ?, ?, ?, ?,  	
				  ?, ?, ?, ?, ?, ?,
				  ?, ?, ?, ?, ?, ?
		   )
     </stmt>
	   <param id="1" type="CHAR">${vIBM_EMP_NUM}</param>
	   <param id="2" type="CHAR">${vEMP_CNTRY_CODE}</param>
	   <param id="3" type="CHAR">${vPROG_CODE}</param>
	   <param id="4" type="CHAR">${vMIGRTN_CODE}</param>
	   <param id="5" type="CHAR">${vREVN_STREAM_CODE1}</param>
	   <param id="6" type="CHAR">${vSAP_SALES_ORG_CODE}</param>
	   <param id="7" type="CHAR">${vPAYER_CNTRY_CODE}</param>
	   <param id="8" type="CHAR">${vPROG_MIGRTN_CODE}</param>
	   <param id="9" type="FLOAT">${vDLGTN_PCT1}</param>			
	   <param id="10" type="DATE">${vVALID_TO_DATE}</param>
	   <param id="11" type="DATE">${vVALID_FROM_DATE}</param>
	   <param id="12" type="VARCHAR">${vEMP_NAME_FULL}</param>
	   <param id="13" type="VARCHAR">${vINTL_PHN_NUM_FULL}</param>
	   <param id="14" type="VARCHAR">${vINTL_FAX_NUM_FULL}</param>
	   <param id="15" type="VARCHAR">${vEMAIL_ADR}</param>
	   <param id="16" type="SMALLINT">${vINACT_FLAG}</param>
	   <param id="17" type="TIMESTAMP">${vMOD_DATE}</param>
	   <param id="18" type="VARCHAR">${vMOD_BY_HRCL_NAME}</param>
   </sql>

SQLUnit --- Test

<#+BEGIN_SRC >

<test name="Test S_Q0_CNTRY_SLSORG"> <call connection-id="duke"> <stmt>{call EBIZ1.S_Q0_CNTRY_SLSORG (?, ?, ?)}</stmt> <param id="1" name="poGenStatus" type="INTEGER" inout="out">${poGenStatus}</param> <param id="2" name="piPartnerCntry" type="VARCHAR" inout="in">${v_partnerCntry}</param> <param id="3" name="piLOBCode" type="VARCHAR" inout="in">${v_LOBCode}</param> </call> <result> <outparam id="1" name="poGenStatus" type="INTEGER">0</outparam> <resultset id="1" partial="true"> <row id="1"> <col id="1" name="CNTRY_CODE" type="CHAR">ARM</col> </row> </resultset> </result> </test> <diff name="Diffing different resultset\/multiple matchers" failure-message="Diff test #3 failed"> <match col-id="1" matcher="net.sourceforge.sqlunit.matchers.AllOrNothingMatcher"> <arg name="match" value="true" /> </match> <match col-id="2" matcher="net.sourceforge.sqlunit.matchers.RangeMatcher"> <arg name="tolerance" value="50" /> </match> <match col-id="3" matcher="net.sourceforge.sqlunit.matchers.PercentageRangeMatcher"> <arg name="pc-tolerance" value="10" /> </match>

<call connection-id="duke"> <stmt>{call EBIZ1.S_Q0_CNTRY_SLSORG (?, ?, ?)}</stmt> <param id="1" name="poGenStatus" type="INTEGER" inout="out">${poGenStatus}</param> <param id="2" name="piPartnerCntry" type="VARCHAR" inout="in">${v_partnerCntry}</param> <param id="3" name="piLOBCode" type="VARCHAR" inout="in">${v_LOBCode}</param> </call> <call connection-id="duke"> <stmt>{call EBIZ1.S_Q0_CNTRY_SLSORG (?, ?, ?)}</stmt> <param id="1" name="poGenStatus" type="INTEGER" inout="out">${poGenStatus}</param> <param id="2" name="piPartnerCntry" type="VARCHAR" inout="in">${v_partnerCntry}</param> <param id="3" name="piLOBCode" type="VARCHAR" inout="in">${v_LOBCode}</param> </call> </diff>

#+END_SRC

DB2 development pain points

How to use SQLUnit

TDD stored procedures

Pain Points Q: When I update SP code, I don't know if the new code breaks the existing logic. What can help me to find the bugs introducing by the added code easily?

A: TDD

Pain Points Q: It's difficult to unit test SP and UDF. Is there any useful tool to help me to do that?

A: SQLUnit: http://sqlunit.sourceforge.net/book1.html

SQLUnit SQLUnit is a regression and unit testing harness for testing database stored procedures. The test suite is written as an XML file. The SQLUnit harness itself is written in Java and uses the JUnit unit testing framework to convert the XML test specifications to JDBC calls and compare the results generated from the calls with the specified results. SQLUnit Get Started <?xml version="1.0"?> <!DOCTYPE sqlunit SYSTEM "../lib/sqlunit.dtd" [ ]> <sqlunit> <connection server-name="ibmdb2" extern="duke" /> <setup> </setup> <test name="Testing Hello World "> <call> <stmt>{call HELLO_WORLD (?, ?)}</stmt> <param id="1" name="piName" type="CHAR" inout="in">Will</param> <param id="2" name="poMsg" type="CHAR" inout="out">${poMsg}</param> </call> <result> <outparam id="1" name="poMsg" type="CHAR">Hello, Will!</outparam> </result> </test> <teardown> </teardown> </sqlunit>

Connections Customization to make SQLUnit to support DB2.

ibmdb2.SMALLINT.class = net.sourceforge.sqlunit.types.IntegerType ibmdb2.SMALLINT.type = 5 Using connection tag to define a connection <connection server-name="ibmdb2" extern="duke" />

sqlunit.driver = COM.ibm.db2.__my_company_1__bc.app.DB2Driver sqlunit.url = __my_company_1__bc:db2:sample

Importing a xml file to define a connection <?xml version="1.0"?> <!DOCTYPE sqlunit SYSTEM "sqlunit/lib/sqlunit.dtd" [ <!ENTITY connection SYSTEM "@CONF_DIR@/sqlunitConnectionConfig.xml"> ]> <sqlunit> &connection; <setup></setup> ...

Setup Set variable

Setting a variable ${var} to 14 <set name="${var}" value="14" /> Setting variable ${myquery.col1} from SQL <set name="${myquery}"> <sql><stmt>select col1 from mytable where col2=45</stmt></sql> <result> <resultset id="1"> <row id="1"> <col id="1" name="c1" type="INTEGER">${col1}</col> </row> </resultset> </result> </set>

Setup Test data <sql> <stmt>INSERT INTO sods2.ibm_emp_dlgtn ( IBM_EMP_NUM, EMP_CNTRY_CODE, PROG_CODE, MIGRTN_CODE, REVN_STREAM_CODE, SAP_SALES_ORG_CODE, PAYER_CNTRY_CODE, PROG_MIGRTN_CODE, DLGTN_PCT, VALID_TO_DATE, VALID_FROM_DATE, EMP_NAME_FULL, INTL_PHN_NUM_FULL, INTL_FAX_NUM_FULL, EMAIL_ADR, INACT_FLAG, MOD_DATE, MOD_BY_HRCL_NAME) VALUES(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ? ) </stmt> <param id="1" type="CHAR">${vIBM_EMP_NUM}</param> <param id="2" type="CHAR">${vEMP_CNTRY_CODE}</param> <param id="3" type="CHAR">${vPROG_CODE}</param>

</sql> Test Tag Attributes for test name Specifies a human-readable name for the test, which will be printed to the log as part of SQLUnit's progress messages. Yes assert Specifies a single or comma-separated list of assertions that must be true for the test. No, defaults to equal

Nested Elements

skip Indicates whether the test should be skipped or not. No match Specifies zero or match elements that should be applied to match the result returned with that specified. No prepare Specifies SQL setup code that must be run on a per-test basis. No sql Specifies the SQL statement that must be run for this test. Either one of sql, call, methodinvoker, dynamicsql or sub call Specifies a stored procedure that must be run for the test. Either one of sql, call, methodinvoker, dynamicsql or sub result Specifies the expected result from the test. Yes

Test Tag <test name="Test S_Q0_CNTRY_SLSORG"> <call connection-id="duke"> <stmt>{call EBIZ1.S_Q0_CNTRY_SLSORG (?, ?, ?)}</stmt> <param id="1" name="poGenStatus" type="INTEGER" inout="out">${poGenStatus}</param> <param id="2" name="piPartnerCntry" type="VARCHAR" inout="in">${v_partnerCntry}</param> <param id="3" name="piLOBCode" type="VARCHAR" inout="in">${v_LOBCode}</param> </call> <result> <outparam id="1" name="poGenStatus" type="INTEGER">0</outparam> <resultset id="1" partial="true"> <row id="1"> <col id="1" name="CNTRY_CODE" type="CHAR">ARM</col> </row> </resultset> </result> </test>

Using SQLUnit and TDD our stored procedures

Pain Points

SQLUnit --- Get Started

SQLUnit --- Connections

SQLUnit --- Setup & Teardown

SQLUnit --- Test

Comments