pgibbo - 2007-11-08

Hi,
I am new to SQLUnit. I have managed to successfully write some test case for functions that return simple data types. I have managed to get tests to work also for functions that return cursors. However, one of my cursors contains a nested cursor - a cursor within a cursor. I am unable to get this to work with SQLUnit. Is this functionality supported? I am attaching the example and the result set below. As you will see below, there looks to be no difference between the expected and the received. Any help would be greatly appreciated. Thanking you in advance.

Regards,
Paul.

Test:
*****************************************************************
<?xml version="1.0"?>
<!DOCTYPE sqlunit SYSTEM "file:docs/sqlunit.dtd">
<sqlunit>
  <connection connection-id="1">
    <driver>oracle.jdbc.driver.OracleDriver</driver>
    <url>removed</url>
    <user>removed</user>
    <password>removed</password>
  </connection>

  <test name="ITGi_Test" failure-message="Failed ITGi">
    <call connection-id="1">
      <stmt>{? = call coe_itgi_req_access_pkg.coe_retrieveproject_fbct(?,?,?,?,?,?,?,?,?,?)}</stmt>
      <param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
      <param id="2" name="v_msgparam1" type="VARCHAR" inout="in">1101806</param>
      <param id="3" name="v_msgparam2" type="VARCHAR" inout="in">NULL</param>
      <param id="4" name="v_msgparam3" type="VARCHAR" inout="in">NULL</param>
      <param id="5" name="v_msgparam4" type="VARCHAR" inout="in">NULL</param>
      <param id="6" name="v_msgparam5" type="VARCHAR" inout="in">NULL</param>
      <param id="7" name="v_msgparam6" type="VARCHAR" inout="in">NULL</param>
      <param id="8" name="v_msgparam7" type="VARCHAR" inout="in">NULL</param>
      <param id="9" name="v_msgparam8" type="VARCHAR" inout="in">NULL</param>
      <param id="10" name="v_msgparam9" type="VARCHAR" inout="in">NULL</param>
      <param id="11" name="v_msgparam10" type="VARCHAR" inout="in">NULL</param>
    </call>
    <result>
      <outparam id="1" name="p_list" type="oracle.CURSOR">
        <resultset id="1">
        <row id="1">
          <col id="1" name="SPONSORS" type="oracle.CURSOR">&lt;resultset id="1"&gt;&#xD;
            &lt;row id="1"&gt;&#xD;
            &lt;col id="1" name="SPONSORSHIPCOSTCENTER" type="VARCHAR"&gt;10823&lt;/col&gt;&#xD;
            &lt;col id="2" name="SPONSORSHIPPERCENTAGE" type="VARCHAR"&gt;100&lt;/col&gt;&#xD;
            &lt;/row&gt;&#xD;
&lt;/resultset&gt;</col>
          <col id="2" name="REFERENCECODE" type="NUMERIC">1101806</col>
          <col id="3" name="TECHNOLOGYINITIATIVE" type="VARCHAR">1</col>
          <col id="4" name="PROJECTNAME" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
          <col id="5" name="PROJECTDESCRIPTION" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
          <col id="6" name="PROJECTCREATOR" type="VARCHAR">a443312</col>
          <col id="7" name="PROJECTSTATUS" type="VARCHAR">ACTIVE</col>
          <col id="8" name="PROJECTOWNER" type="VARCHAR">a443312</col>
          <col id="9" name="PROJECTBUSINESSUNIT" type="VARCHAR">FBCT</col>
          <col id="10" name="CUSTOMERCOSTOBJECTVALUE" type="VARCHAR">AX000018</col>
          <col id="11" name="CUSTOMERCOSTOBJECTHIERARCHY" type="VARCHAR">COAP</col>
          <col id="12" name="PROJECTGROUPOWNER" type="VARCHAR">TPS</col>
          <col id="13" name="PROJECTCATEGORYCODE" type="VARCHAR">1236</col>
          <col id="14" name="SOURCESYSTEM" type="CHAR">ITG</col>
        </row>
        </resultset>
      </outparam>
    </result>
  </test>
<teardown/>
</sqlunit>
*****************************************************************

Result:
*****************************************************************
run-postgres-test:
  [sqlunit] *** Running SQLUnit file: test/oracle/itgi.xml
  [sqlunit] Getting connection(1)
  [sqlunit] Setting up test...
  [sqlunit] Running test[1]: ITGi_Test (0ms)
  [sqlunit] Assertion "outparams-equal" failed (1101806,1,FBCT 11.5 testing pls ignore 11,FBCT 11.5 testing pls ign
ore 11,a443312,ACTIVE,a443312,FBCT,AX000018,COAP,TPS,1236,ITG
  [sqlunit] (oracle.CURSOR) != <resultset id="1">
  [sqlunit]   <row id="1">
  [sqlunit]     <col id="1" name="SPONSORSHIPCOSTCENTER" type="VARCHAR">10823</col>
  [sqlunit]     <col id="2" name="SPONSORSHIPPERCENTAGE" type="VARCHAR">100</col>
  [sqlunit]   </row>
  [sqlunit] </resultset>,1101806,1,FBCT 11.5 testing pls ignore 11,FBCT 11.5 testing pls ignore 11,a443312,ACTIVE,a
443312,FBCT,AX000018,COAP,TPS,1236,ITG
  [sqlunit] (oracle.CURSOR) at outparams[0])
  [sqlunit] *** expected:
  [sqlunit] <result>
  [sqlunit]   <outparam id="1" name="p_list" type="oracle.CURSOR">
  [sqlunit]     <resultset id="1">
  [sqlunit]       <row id="1">
  [sqlunit]         <col id="2" name="REFERENCECODE" type="NUMERIC">1101806</col>
  [sqlunit]         <col id="3" name="TECHNOLOGYINITIATIVE" type="VARCHAR">1</col>
  [sqlunit]         <col id="4" name="PROJECTNAME" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
  [sqlunit]         <col id="5" name="PROJECTDESCRIPTION" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
  [sqlunit]         <col id="6" name="PROJECTCREATOR" type="VARCHAR">a443312</col>
  [sqlunit]         <col id="7" name="PROJECTSTATUS" type="VARCHAR">ACTIVE</col>
  [sqlunit]         <col id="8" name="PROJECTOWNER" type="VARCHAR">a443312</col>
  [sqlunit]         <col id="9" name="PROJECTBUSINESSUNIT" type="VARCHAR">FBCT</col>
  [sqlunit]         <col id="10" name="CUSTOMERCOSTOBJECTVALUE" type="VARCHAR">AX000018</col>
  [sqlunit]         <col id="11" name="CUSTOMERCOSTOBJECTHIERARCHY" type="VARCHAR">COAP</col>
  [sqlunit]         <col id="12" name="PROJECTGROUPOWNER" type="VARCHAR">TPS</col>
  [sqlunit]         <col id="13" name="PROJECTCATEGORYCODE" type="VARCHAR">1236</col>
  [sqlunit]         <col id="14" name="SOURCESYSTEM" type="CHAR">ITG</col>
  [sqlunit]       </row>
  [sqlunit]     </resultset>
  [sqlunit]   </outparam>
  [sqlunit] </result>
  [sqlunit] *** but got:
  [sqlunit] <result>
  [sqlunit]   <outparam id="1" name="p_list" type="oracle.CURSOR">
  [sqlunit]     <resultset id="1">
  [sqlunit]       <row id="1">
  [sqlunit]         <col id="1" name="SPONSORS" type="oracle.CURSOR">&lt;resultset id="1"&gt;&#xD;
  [sqlunit]   &lt;row id="1"&gt;&#xD;
  [sqlunit]     &lt;col id="1" name="SPONSORSHIPCOSTCENTER" type="VARCHAR"&gt;10823&lt;/col&gt;&#xD;
  [sqlunit]     &lt;col id="2" name="SPONSORSHIPPERCENTAGE" type="VARCHAR"&gt;100&lt;/col&gt;&#xD;
  [sqlunit]   &lt;/row&gt;&#xD;
  [sqlunit] &lt;/resultset&gt;</col>
  [sqlunit]         <col id="2" name="REFERENCECODE" type="NUMERIC">1101806</col>
  [sqlunit]         <col id="3" name="TECHNOLOGYINITIATIVE" type="VARCHAR">1</col>
  [sqlunit]         <col id="4" name="PROJECTNAME" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
  [sqlunit]         <col id="5" name="PROJECTDESCRIPTION" type="VARCHAR">FBCT 11.5 testing pls ignore 11</col>
  [sqlunit]         <col id="6" name="PROJECTCREATOR" type="VARCHAR">a443312</col>
  [sqlunit]         <col id="7" name="PROJECTSTATUS" type="VARCHAR">ACTIVE</col>
  [sqlunit]         <col id="8" name="PROJECTOWNER" type="VARCHAR">a443312</col>
  [sqlunit]         <col id="9" name="PROJECTBUSINESSUNIT" type="VARCHAR">FBCT</col>
  [sqlunit]         <col id="10" name="CUSTOMERCOSTOBJECTVALUE" type="VARCHAR">AX000018</col>
  [sqlunit]         <col id="11" name="CUSTOMERCOSTOBJECTHIERARCHY" type="VARCHAR">COAP</col>
  [sqlunit]         <col id="12" name="PROJECTGROUPOWNER" type="VARCHAR">TPS</col>
  [sqlunit]         <col id="13" name="PROJECTCATEGORYCODE" type="VARCHAR">1236</col>
  [sqlunit]         <col id="14" name="SOURCESYSTEM" type="CHAR">ITG</col>
  [sqlunit]       </row>
  [sqlunit]     </resultset>
  [sqlunit]   </outparam>
  [sqlunit] </result>
  [sqlunit] Failed ITGi
  [sqlunit] Tearing down test...
  [sqlunit] sqlunit-ant: SQLUnit Tests Failed: In file: test/oracle/itgi.xml, tests: 1, failures: 1, errors = 0
  [sqlunit] One or more SQLUnit Tests failed, see the console for details

BUILD SUCCESSFUL
Total time: 4 seconds
*****************************************************************