testing stored procedure

[Sathi Chowdhury]

Hi Sujit and all,
I am on my way to build a test case that tests a stored procedure which does not return any result as such.it simply inserts a row in the database.

My code here(only pasting the <test> part
--------------
<test name="Add_Note">

     <call connection-id="1">
            <stmt>{call IDE_DOCUMENTS.ADD_NOTE(?,?,?,?)}</stmt>
            <param id="1" name="p_doc_id" type="NUMERIC" inout="in">100</param>
            <param id="1" name="p_issue_id" type="NUMERIC" inout="in">${myquery2.issue_id}></param>
            <param id="1" name="p_user_name" type="VARCHAR" inout="in">${myquery4.user_id)</param>
            <param id="1" name="p_note" type="VARCHAR" inout="in">QA is testing</param>
     </call>
     <result>
    <updatecount>1</updatecount>
    </result>    
     <sql connection-id="1">
     <stmt>select DOCUMENT_ID,ISSUE_ID,USER_ID,NOTES,to_char(trunc(RECORDED_ON),'MM/DD/YYYY') RECORDERD_ON rom Notes </stmt>
            
     </sql>
    <result>
               <resultset id="1">
                 <row id="1">
                     <col id="1" name="DOCUMENT_ID" type="INTEGER">100</col>
                     <col id="2" name="ISSUE_ID" type="INTEGER">${myquery2.issue_id}</col>
                     <col id="3" name="USER_ID" type="INTEGER">${myquery4.user_id)</col>        
                     <col id="4" name="NOTES" type="VARCHAR">QA is testing </col> 
                     <col id="5" name="RECORDED_ON" type="VARCHAR">$(datequery.today)</col> 
                   </row>
             </resultset>
      </result>
</test>

------------------

I am getting the error below

[sqlunit] sqlunit-ant: XML error (org.jdom.input.JDOMParseException): Error on line 98: The content of element type "test" must match "(skip?,classifiers?,match*,prepare?,((sql|call|methodinvoker|dynamicsql|sub),result)?)".
------------
what's wrong??any help is much appreciated

[Douglas Kvidera]

Sathi

A <test> can only have one <call> or <sql> tag.  You need to create a second <test> for the <sql> and second <results>.

The XML DTD file is docs/sqlunit.dtd.

-doug

[Douglas Kvidera]

Sathi,

I see your problem now.  You want to use a stored procedure to prepare data before the test.  However, the prepare element doesn't take a <call> as a child, only <sql> and you can't call a stored procedure in an <sql> element.  The setup element has the same restriction.  Test are processed in the order they appear in the files; so for now, you can put the "prepare" test before the "real" test.

This looks like a feature request to me.

-doug

[Josef Gmeineder]

I found that this is not correct:
  >>However, the prepare element doesn't take a <call> as a child, only <sql> and you can't call a stored procedure in an <sql> element.<<

You can call a stored procedure in an <sql> element, I use something like this:
<prepare>
  <sql>
    <stmt>{call storedProcedure()}</stmt>
  </sql>
</prepare>

Until now I did not try this with parameters, but I think this should also work.
Btw, I use version 4.9 and Oracle.

[Sathi Chowdhury]

I have done it this way ,to make the stored proc simply get executed and does not expect any result.
<test name="Add_Note" assert="resultsets-equal">

     <call connection-id="1">
            <stmt>{call IDE_DOCUMENTS.ADD_NOTE(?,?,?,?)}</stmt>
            <param id="1" name="p_doc_id" type="INTEGER" inout="in">100</param>
            <param id="2" name="p_sample_name" type="VARCHAR" inout="in">${myquery1.name}</param>
            <param id="3" name="p_user_name" type="VARCHAR" inout="in">${myquery3.name}</param>
            <param id="4" name="p_note" type="VARCHAR" inout="in">QA is testing</param>
     </call>
      <result echo="true" />
</test>

[Sujit Pal]

Yes, this is the approach I would use too. The decision was made to not allow stored procedures in the setup and prepare because sqlunit was going to test stored procedures, but I guess one could argue that it should support testing stored procedures too (which have been separately tested).

The fix should not be too hard, best case is just to open up the DTD to allow it, worst case is adding some logic to the setup and prepare handlers. If you would like to make a patch and send it, I will incorporate it.

Thanks
-sujit