Comparing results from stored procedures

[Roopali]

Hi,

I wanted to figure out if there is a way to compare the results of a stored procedure with results from another stored procedure instead of specifying expected values.
So for example
<test name="Getting a named status">
    <call>
      <stmt>{? = call GetStatusList(?)}</stmt>
      <param id="1" type="INTEGER" inout="out">${rc}</param>
      <param id="2" type="INTEGER">1</param>
    </call>
    <result>
      <outparam id="1" type="INTEGER">0</outparam>
      <resultset id="1">
        <row id="1">
          <col id="1" name="status" type="VARCHAR">Disabled</col>
          <col id="2" name="statusId" type="INTEGER">2</col>
        </row>
      </resultset>
    </result>
  </test>

Instead of name="status" and type=disabled, if I can call another stored proc and see if the results are the same as that from the first procedure. The main reason for doing this is to run the same procedure on two different databases with different schemas but same data.

thanks
Roopali.

[Alec Swan]

I would also like to know how to validate the result set returned by one stored procedure against the result set returned by a different stored procedure.

I think a nice solution for this would be to support named result sets. This way a user can assign a name to the restult set returned by the target stored procedure, and then reference this result set by name when validating the result set returned by the original stored procedure.

Please let us know how to address this issue. Also, even knowing that this feature is not supported will also be valuable.

Thanks.

Alec

[David Fishburn]

Have you looked into the Diff tag?  This is very simple to use, let me grab one of my examples:

<sqlunit>
  <connection connection-id="cons" reconnect-on-failure="on">
    <driver>ianywhere.ml.jdbcodbc.IDriver</driver>
    <url>jdbc:odbc:dsn=cons</url>
    <user>DBA</user>
    <password>SQL</password>
  </connection>

  <connection connection-id="remote" reconnect-on-failure="on">
    <driver>ianywhere.ml.jdbcodbc.IDriver</driver>
    <url>jdbc:odbc:dsn=cons</url>
    <user>DBA</user>
    <password>SQL</password>
  </connection>

  <diff name="Diffing the ADDRESS table"
    failure-message="ADDRESS table match failed">
    <sql connection-id="cons">
      <stmt>
        SELECT adr_id, adr_ln_1
          FROM ADDRESSES
         WHERE ? IN (
                      SELECT AGY_ID
                        FROM PARTY_ADDRESS pa
                       WHERE pa.ADR_ID = ADDRESSES.ADR_ID
                    )
         ORDER BY adr_id
      </stmt>
      <param id="1" type="INTEGER">${SSUserID}</param>
    </sql>
    <sql connection-id="remote">
      <stmt>
        SELECT adr_id, adr_ln_1
          FROM ADDRESSES
         ORDER BY adr_id
      </stmt>
    </sql>
  </diff>

  <teardown />

</sqlunit>

In this case each database executes a different query (one could easily be a stored procedure call) and will automatically verify the results are the same.

HTH,
Dave