Creating a dynamic SQL statement

[David Fishburn]

I am using todays CVS version.

I have a test which does:
  <test name="Retrieve Remotes Agent ID">
    <sql connection-id="remote" >
      <stmt>
        SELECT TOP 1 agy_id
          FROM T0PTY
         ORDER BY agy_id
      </stmt>
    </sql>
    <result>
      <resultset id="1">
        <row id="1">
          <col id="1" name="agy_id" type="INTEGER">${AgentID}</col>
        </row>
      </resultset>
    </result>
  </test>

When I attempt to use ${AgentID} later on, it appears to be null:

    <diff name="Diffing different resultset/multiple matchers"
      failure-message="Diff test #1 failed">
      <sql connection-id="cons">
        <stmt>
          SELECT adr_id, adr_ln_1
            FROM T0ADR
           WHERE ? IN (
                        SELECT AGY_ID
                          FROM T0PTY_ADR_LNK l
                         WHERE l.ADR_ID = T0ADR.ADR_ID
                      )
           ORDER BY adr_id
        </stmt>
        <param id="1" type="INTEGER">${AgentID}</param>
      </sql>
      <sql connection-id="remote">
        <stmt>
          SELECT adr_id, adr_ln_1
            FROM T0ADR
           ORDER BY adr_id
        </stmt>
      </sql>
    </diff>

[sqlunit] Assertion "resultset-equals" failed (0 != 1 at result[1].#rows)
[sqlunit] *** expected:
[sqlunit] <result>
[sqlunit]   <resultset id="1" />
[sqlunit] </result>
[sqlunit] *** but got: <result>
[sqlunit]   <resultset id="1">
[sqlunit]     <row id="1">
[sqlunit]       <col id="1" name="adr_id" type="INTEGER">902</col>
[sqlunit]       <col id="2" name="adr_ln_1" type="VARCHAR">Device 2</col>
[sqlunit]     </row>
[sqlunit]   </resultset>
[sqlunit] </result>

If I run the test with:
        <param id="1" type="INTEGER">102</param>

...
[sqlunit] Running test[6]: Display Remotes Agent ID (10ms)
[sqlunit] Running diff[7]: Diffing different resultset/multiple matchers (10ms)
[sqlunit] Tearing down test...

You can see here it works fine.

Is there a way to get sqlunit to display what the value of ${AgentID} is set to?

Now to the reason I am making the post.  I was trying to verify what the value of ${AgentID} is set to.  I can do this via the database with a statement  like this:
      message 'Agent ID: '||string(what_ever_I_want);

The problem here is this canNOT be a prepared statement.  So I cannot do something like this:
      message 'Agent ID: '||string(?);

So I tried something like this (which does not work):
  <batchtest name="Display Remotes Agent ID">
    <batchsql connection-id="remote" >
      <stmt>message 'Agent ID: '+string(${AgentID})</stmt>
    </batchsql>
    <batchresult />
  </batchtest>

I read up on the DynamicSQL tag, but that appears to force me into implementing a Java class.  I was  just hoping for something that would allow me to simply build a SQL string.

Does anyone have any suggestions?

Thanks,
Dave

[Sujit Pal]

Hi Dave,

This is the same as the ${AgentId} problem, right? In that case, its fixed and checked into CVS this morning. Can you update and check if the problem goes away?

Thanks
Sujit