I think it could be a bug in your test specification. Note that your sproc returns no resultset from it, however you have a resultset element specified. You probably meant to do something like this on line 6?
select @Out_intval = 10
select @Out_intval
The first one is an assignment, the second one is the actual resultset coming out of the sproc. In this case, the result would be similar to what you specified.
Also notice that you are not returning anything in outparam id=1 (ie no return <somenumber> at the end of the sproc). Sybase apparently does the logical thing by returning a default int value in its output, ie 0. I tried setting it to VARCHAR as well, and the result came back as "0". So when you are not specifying a return value, Sybase will send back an int of 0 (which can be stringified to "0" if required). So a good rule of thumb would be to define the return value in your <call> and your <result> and set to int 0.
My TestNullOutParam sproc is identical to what you have posted. Here is the test specification that works for me.
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<outparam id="2" type="INTEGER">10</outparam>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
Let me know if this answers your question.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Oh, I see your point now. Even though you specify a resultset in your test, it gives you this strange error when your stored procedure does not send out a resultset. The correct behavior should be to report an error. Ok will fix this now.
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
Ok, now with my stored procedure identical to yours (no return), and my test case defined thus:
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<resultset id="1">
<row id="1">
<col id="1" type="INTEGER">${1}</col>
</row>
</resultset>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
My result looks like this:
[sqlunit] Running test[7]: running sp_TestNullOutParam (40ms)
[sqlunit] No match on #-variables,
[sqlunit] *** expected:
[sqlunit] <result>
[sqlunit] <outparam id="2" type="INTEGER">10</outparam>
[sqlunit] <outparam id="1" type="INTEGER">0</outparam>
[sqlunit] <outparam id="3" type="VARCHAR">NULL</outparam>
[sqlunit] </result>
[sqlunit] *** but got:
[sqlunit] <result>
[sqlunit] <outparam id="1" type="INTEGER">0</outparam>
[sqlunit] <outparam id="3" type="VARCHAR">NULL</outparam>
[sqlunit] <resultset id="1">
[sqlunit] <row id="1">
[sqlunit] <col id="1" type="INTEGER">${1}</col>
[sqlunit] </row>
[sqlunit] </resultset>
[sqlunit] </result>
which is what it should be, IMO. With the test defined like this:
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<outparam id="2" type="INTEGER">10</outparam>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
it works fine, again as expected. This was a bug. I am checking it into CVS, changes are in:
M src/net/sourceforge/sqlunit/DatabaseResult.java
M src/net/sourceforge/sqlunit/SymbolTable.java
M src/net/sourceforge/sqlunit/TestHandler.java
I will do a release as soon as I get the other reported bugs fixed. Meanwhile, would appreciate it if you could check out the code and give me feedback.
Thanks
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
-- Posted by Lakshmi via email --
hi
now null output is fine in 2.2
but the problem is with the output param being specified in a resultset..not yet fixed in 2.2
1> CREATE PROCEDURE sp_test
2> @Out_intval int OUTPUT,
3> @Out_varcharval varchar(100) OUTPUT
4> AS
5> BEGIN
6> select @Out_intval=10
7> END
8> go
<test name="sp_test">
<call>
<stmt>{?=call Test..sp_test(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errdesc}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<resultset id="1">
<row id="1">
<col id="1" type="INTEGER">${1}</col>
</row>
</resultset>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
throws this trace------but as u notice the assignment is proper...
--------------------------------------------------------------------------------------
Running test[1]: sp_testSymbol table dump
[sqlunit] -----------------
[sqlunit] __FailureMessage__=>null
[sqlunit] ${errdesc}=>NULL
[sqlunit] ${errcode}=>10
[sqlunit] __JavaObjectSupport__=>off
[sqlunit] ${rc}=>0
[sqlunit] -----------------
[sqlunit] (71ms)
[sqlunit] Tearing down test...
[sqlunit] SQLUnit Tests Failed: In file: ../../test/sybase/SS/test.xml, tests: 1, failures: 1, errors = 0
[sqlunit] net.sourceforge.sqlunit.SQLUnitException: SQLUnit Tests Failed: In file: ../../test/sybase/SS/test.xml, tests: 1, failures: 1, errors = 0
[sqlunit] at net.sourceforge.sqlunit.SQLUnit.testWrapper(SQLUnit.java:145)
[sqlunit] at net.sourceforge.sqlunit.SQLUnit.runTest(SQLUnit.java:131)
[sqlunit] at net.sourceforge.antsqlunittask.SqlunitTask.execute(SqlunitTask.java:101)
[sqlunit] at org.apache.tools.ant.UnknownElement.execute(UnknownElement.java:193)
[sqlunit] at org.apache.tools.ant.Task.perform(Task.java:341)
[sqlunit] at org.apache.tools.ant.Target.execute(Target.java:309)
[sqlunit] at org.apache.tools.ant.Target.performTasks(Target.java:336)
[sqlunit] at org.apache.tools.ant.Project.executeTarget(Project.java:1339)
[sqlunit] at org.apache.tools.ant.Project.executeTargets(Project.java:1255)
[sqlunit] at org.apache.tools.ant.Main.runBuild(Main.java:609)
[sqlunit] at org.apache.tools.ant.Main.start(Main.java:196)
[sqlunit] at org.apache.tools.ant.Main.main(Main.java:235)
[sqlunit] Caused by: net.sourceforge.sqlunit.SQLUnitException: Undefined symbol: ${1}
[sqlunit] at net.sourceforge.sqlunit.SymbolTable.update(SymbolTable.java:106)
[sqlunit] at net.sourceforge.sqlunit.TestHandler.process(TestHandler.java:84)
[sqlunit] at net.sourceforge.sqlunit.SQLUnit.processDoc(SQLUnit.java:202)
[sqlunit] at net.sourceforge.sqlunit.SQLUnit.testWrapper(SQLUnit.java:142)
[sqlunit] ... 11 more
[sqlunit] SQLUnit Tests Failed: In file: ../../test/sybase/SS/test.xml, tests: 1, failures: 1, errors = 0
BUILD FAILED
I think it could be a bug in your test specification. Note that your sproc returns no resultset from it, however you have a resultset element specified. You probably meant to do something like this on line 6?
select @Out_intval = 10
select @Out_intval
The first one is an assignment, the second one is the actual resultset coming out of the sproc. In this case, the result would be similar to what you specified.
Also notice that you are not returning anything in outparam id=1 (ie no return <somenumber> at the end of the sproc). Sybase apparently does the logical thing by returning a default int value in its output, ie 0. I tried setting it to VARCHAR as well, and the result came back as "0". So when you are not specifying a return value, Sybase will send back an int of 0 (which can be stringified to "0" if required). So a good rule of thumb would be to define the return value in your <call> and your <result> and set to int 0.
My TestNullOutParam sproc is identical to what you have posted. Here is the test specification that works for me.
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<outparam id="2" type="INTEGER">10</outparam>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
Let me know if this answers your question.
Oh, I see your point now. Even though you specify a resultset in your test, it gives you this strange error when your stored procedure does not send out a resultset. The correct behavior should be to report an error. Ok will fix this now.
-sujit
Ok, now with my stored procedure identical to yours (no return), and my test case defined thus:
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<resultset id="1">
<row id="1">
<col id="1" type="INTEGER">${1}</col>
</row>
</resultset>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
My result looks like this:
[sqlunit] Running test[7]: running sp_TestNullOutParam (40ms)
[sqlunit] No match on #-variables,
[sqlunit] *** expected:
[sqlunit] <result>
[sqlunit] <outparam id="2" type="INTEGER">10</outparam>
[sqlunit] <outparam id="1" type="INTEGER">0</outparam>
[sqlunit] <outparam id="3" type="VARCHAR">NULL</outparam>
[sqlunit] </result>
[sqlunit] *** but got:
[sqlunit] <result>
[sqlunit] <outparam id="1" type="INTEGER">0</outparam>
[sqlunit] <outparam id="3" type="VARCHAR">NULL</outparam>
[sqlunit] <resultset id="1">
[sqlunit] <row id="1">
[sqlunit] <col id="1" type="INTEGER">${1}</col>
[sqlunit] </row>
[sqlunit] </resultset>
[sqlunit] </result>
which is what it should be, IMO. With the test defined like this:
<test name="running sp_TestNullOutParam">
<call>
<stmt>{? = call TestNullOutParam(?,?)}</stmt>
<param id="1" type="INTEGER" inout="out">${rc1}</param>
<param id="2" type="INTEGER" inout="out">${errcode}</param>
<param id="3" type="VARCHAR" inout="out">${errmsg}</param>
</call>
<result>
<outparam id="1" type="INTEGER">0</outparam>
<outparam id="2" type="INTEGER">10</outparam>
<outparam id="3" type="VARCHAR">NULL</outparam>
</result>
</test>
it works fine, again as expected. This was a bug. I am checking it into CVS, changes are in:
M src/net/sourceforge/sqlunit/DatabaseResult.java
M src/net/sourceforge/sqlunit/SymbolTable.java
M src/net/sourceforge/sqlunit/TestHandler.java
I will do a release as soon as I get the other reported bugs fixed. Meanwhile, would appreciate it if you could check out the code and give me feedback.
Thanks
-sujit