I have a written a test where am setting more than one variables with the values set from a sql statement.
now,I have a stored function that returns a ref cursor..so to validate the result I plan to use the variales I set inside the prepare..
my code look like this
when I try to refer any variable inside outparam tag it simply doesn't work.what am I doing wrong?
will be patiently waiting for your reply...but please hurry :)
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Variable substitution should work within the outparam/resultset element exactly as it does in the resultset element, they are basically the same code. One thing I did not understand was why you were setting a result within the prepare. You can try running some debugging statements (there is an <echo> tag which can be used) to see the value of the variables at different points.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
sorry for the erroneous posting above...accidentaly I was halfway through and got posted.Anyways, here is the final posting,
<test name="ABC set" >
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1} </col>
</resultset>
</result>
</test>
<test name="call func that returns a ref crsor" >
<call connection-id="1">
<stmt>{?=call stored_func(?,?)}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
<param id="2" name="p_arg1" type="INTEGER" inout="in" >2</param>
<param id="3" name="p_arg2" type="VARCHAR" inout="in">abc</param>
</call>
<result >
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</outparam>
</result>
</test>
The variable binding of col1 does not work within the outparam...but the moment I remove outparam
tags it works.....
so if i write it like this it shows the variable col1 gets the value.
.....
but still I can' acheive a successful test as it is trying to match two set of the following contruct.
<result>
<outparam>
<resultset>
........
.....
</result>
</outparam>
</resultset>
So ,basically the problem will be solved if the binding worked within <outparam>
...
It would be great if you throw some light on this.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Sujit,
well, thinking that I can not hardcode any of the values within outparam, I have one <test> </test> block I am using to set the variables implicitly.
so the code looks like this
sorry for the erroneous posting above...accidentaly I was halfway through and got posted.Anyways, here is the final posting,
<test name="ABC set" >
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1} </col>
</resultset>
</result>
</test>
<test name="call func that returns a ref crsor" >
<call connection-id="1">
<stmt>{?=call stored_func(?,?)}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
<param id="2" name="p_arg1" type="INTEGER" inout="in" >2</param>
<param id="3" name="p_arg2" type="VARCHAR" inout="in">abc</param>
</call>
<result >
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</outparam>
</result>
</test>
The variable binding of col1 does not work within the outparam...but the moment I remove outparam
tags it works.....
so if i write it like this it shows the variable col1 gets the value.
.....
but still I can' acheive a successful test as it is trying to match two set of the following contruct.
<result>
<outparam>
<resultset>
........
.....
</result>
</outparam>
</resultset>
So ,basically the problem will be solved if the binding worked within <outparam>
...
It would be great if you throw some light on this.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I was having the above problem with binding only for the stored function that retrun a ref cursor.
I had to do it this way.
I wrote a wrapper function to be able to write a select statement from the stored function.
and then used diff to compare two sql results
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Chabatox found a problem with my fix which he has corrected. The corrected code is now in CVS. Sorry about that and thanks to Chabatox for the correct fix. Details below:
Checking in src/net/sourceforge/sqlunit/SymbolTable.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/SymbolTable.java,v <-- SymbolTable.java
new revision: 1.44; previous revision: 1.43
done
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I have a written a test where am setting more than one variables with the values set from a sql statement.
now,I have a stored function that returns a ref cursor..so to validate the result I plan to use the variales I set inside the prepare..
my code look like this
<test name="ABC" >
<prepare>
<set name="${query1}">
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</resultset>
</result>
</prepare>
<call connection-id="1">
<stmt>{?=call stored_func(?,?)}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
<param id="2" name="p_arg1" type="INTEGER" inout="in" >2</param>
<param id="3" name="p_arg2" type="VARCHAR" inout="in">abc</param>
</call>
<result >
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${query1.col1}</col>
<col id="2" type="INTEGER">${user_id}</col>
<!--the variable ${user_id} was set in th esetup section -->
</row>
</resultset>
</outparam>
</result>
</test>
when I try to refer any variable inside outparam tag it simply doesn't work.what am I doing wrong?
will be patiently waiting for your reply...but please hurry :)
Variable substitution should work within the outparam/resultset element exactly as it does in the resultset element, they are basically the same code. One thing I did not understand was why you were setting a result within the prepare. You can try running some debugging statements (there is an <echo> tag which can be used) to see the value of the variables at different points.
-sujit
sorry for the erroneous posting above...accidentaly I was halfway through and got posted.Anyways, here is the final posting,
<test name="ABC set" >
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1} </col>
</resultset>
</result>
</test>
<test name="call func that returns a ref crsor" >
<call connection-id="1">
<stmt>{?=call stored_func(?,?)}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
<param id="2" name="p_arg1" type="INTEGER" inout="in" >2</param>
<param id="3" name="p_arg2" type="VARCHAR" inout="in">abc</param>
</call>
<result >
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</outparam>
</result>
</test>
The variable binding of col1 does not work within the outparam...but the moment I remove outparam
tags it works.....
so if i write it like this it shows the variable col1 gets the value.
.....
<test name="call func that returns a ref crsor" >
...
...
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</result>
but still I can' acheive a successful test as it is trying to match two set of the following contruct.
<result>
<outparam>
<resultset>
........
.....
</result>
</outparam>
</resultset>
So ,basically the problem will be solved if the binding worked within <outparam>
...
It would be great if you throw some light on this.
Sujit,
well, thinking that I can not hardcode any of the values within outparam, I have one <test> </test> block I am using to set the variables implicitly.
so the code looks like this
<test name="ABC set" >
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1} </col>
</resultset>
</result>
</prepare>
sorry for the erroneous posting above...accidentaly I was halfway through and got posted.Anyways, here is the final posting,
<test name="ABC set" >
<sql connection-id="1">
<stmt>SELECT col1 FROM table1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1} </col>
</resultset>
</result>
</test>
<test name="call func that returns a ref crsor" >
<call connection-id="1">
<stmt>{?=call stored_func(?,?)}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
<param id="2" name="p_arg1" type="INTEGER" inout="in" >2</param>
<param id="3" name="p_arg2" type="VARCHAR" inout="in">abc</param>
</call>
<result >
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</outparam>
</result>
</test>
The variable binding of col1 does not work within the outparam...but the moment I remove outparam
tags it works.....
so if i write it like this it shows the variable col1 gets the value.
.....
<test name="call func that returns a ref crsor" >
...
...
<result >
<resultset id="1" >
<row id="1">
<col id="1" name="col1" type="INTEGER">${col1}</col>
</row>
</resultset>
</result>
but still I can' acheive a successful test as it is trying to match two set of the following contruct.
<result>
<outparam>
<resultset>
........
.....
</result>
</outparam>
</resultset>
So ,basically the problem will be solved if the binding worked within <outparam>
...
It would be great if you throw some light on this.
I was having the above problem with binding only for the stored function that retrun a ref cursor.
I had to do it this way.
I wrote a wrapper function to be able to write a select statement from the stored function.
and then used diff to compare two sql results
Hi Sathi,
This looks like a bug, there is another report similar to yours. I will look at this and get back to you.
-sujit
Fixed now:
Checking in src/net/sourceforge/sqlunit/SymbolTable.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/SymbolTable.java,v <-- SymbolTable.java
new revision: 1.43; previous revision: 1.42
done
Checking in src/net/sourceforge/sqlunit/handlers/CallHandler.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/handlers/CallHandler.java,v <-- CallHandler.java
new revision: 1.14; previous revision: 1.13
done
Please download these two files and recompile (or download the entire thing from CVS and recompile).
Thanks
Sujit
I still have problem in retreiving a variable inside out param specially when the resultset is of cursor type
Here is my code
<sqlunit>
<connection connection-id="1" extern="sqlunit-connection" />
<setup>
<set name="${query1}">
<sql connection-id="1">
<stmt>SELECT TYPES FROM DUMMYDOCTYPES WHERE rownum < 1</stmt>
</sql>
<result >
<resultset id="1" >
<row id="1">
<col id="1" type="VARCHAR">${res1}</col>
</row>
</resultset>
</result>
</set>
</setup>
<echo name="sat" text="${query1.res1}"/>
--This value is printed file (prints "DOC")
<test name="ABC" >
<call connection-id="1">
<stmt>{?=call IDE_DOCUMENTS.GET_DOCTYPES()}</stmt>
<param id="1" name="p_list" type="oracle.CURSOR" inout="out">${p_list}</param>
</call>
<result>
<outparam id="1" name="p_list" type="oracle.CURSOR" >
<resultset id="1" >
<row id="1">
<col id="1" name="DOCTYPE" type="VARCHAR">${query1.res1}</col>
</row>
<row id="2">
<col id="1" name="DOCTYPE" type="VARCHAR">JPG</col>
</row>
<row id="3">
<col id="1" name="DOCTYPE" type="VARCHAR">XLS</col>
</row>
</resultset>
</outparam>
</result>
</test>
</sqlunit>
---${query1.res1} is not able to get it's value that it is printing in the above echo.
I appreciate this forum so much and
any kind of help would be great
Hi Sathi,
Chabatox found a problem with my fix which he has corrected. The corrected code is now in CVS. Sorry about that and thanks to Chabatox for the correct fix. Details below:
Checking in src/net/sourceforge/sqlunit/SymbolTable.java;
/cvsroot/sqlunit/sqlunit/src/net/sourceforge/sqlunit/SymbolTable.java,v <-- SymbolTable.java
new revision: 1.44; previous revision: 1.43
done
-sujit