Omer actually asked a question sometime back on this forum which pointed me to the answer. You could do something like this:
<call>
..<stmt>{call MyTestProc(@alpha=?,@gamma=?)}</stmt>
..<param id="1" name="alpha" type="INTEGER">1</param>
..<param id="2" name="gamma" type="INTEGER">2</param>
</call>
Notice that I do not specify the value of @beta which is defaulted to NULL. I havent actually tested this myself though. It would be nice if you tried this and posted your findings here (for posterity :-)).
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
As an additional note, if you move all your default parameters to the end of the parameter list (like most people probably will), you can just omit the last "n" parameters. Of course, you are back to the original problem if you want to specify values for some, but not all, of the default parameters.
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I tried a test against the sybase ASE installation I have access to, with a procedure which has 3 parameters, two of which (at the end) are default parameters:
create procedure MyProc (
@p1 integer,
@p2 integer=null,
@p3=0)
as
begin
...
and called the code in the SQLUnit test like so:
<call>
..<stmt>{call MyProc(@p1=?)}</stmt>
..<param id="1" type="INTEGER">10171838</param>
</call>
and both worked without problems. I did find a bug in the code where the statement object is being closed twice, which I also fixed.
I also tested with the TUITool (after fixing the bug), and was able to get the result that I expected.
I am thinking that the problem is the bug I found which was causing the same problem that you identify in your stack trace. Thank you for catching it. Its already fixed in CVS, the file that changed is SqlHandler.java.
It will be in tomorrow's release (3.9).
-sujit
If you would like to refer to this comment somewhere else in this project, copy and paste the following link:
I'm using MS SQL Server that has a stored procedure with default values.
Is there a way to use SQLUnit to test a stored procedure with default values.
For example, if I have a stored procedure
CREATE PROC MyTestProc
....@Alpha INTEGER,
....@Beta INTEGER = NULL,
....@Gamma INTEGER
AS
BEGIN
....-- Do some stuff, etc. --
END
How could I test this within SQLUnit so that I do not have to provide a value for @Beta?
Omer actually asked a question sometime back on this forum which pointed me to the answer. You could do something like this:
<call>
..<stmt>{call MyTestProc(@alpha=?,@gamma=?)}</stmt>
..<param id="1" name="alpha" type="INTEGER">1</param>
..<param id="2" name="gamma" type="INTEGER">2</param>
</call>
Notice that I do not specify the value of @beta which is defaulted to NULL. I havent actually tested this myself though. It would be nice if you tried this and posted your findings here (for posterity :-)).
-sujit
I have tried to use the calling convention you suggested and this does not appear to work.
Using the TUI tool I configured the properties to be:
__test!name = Test
__test!call!stmt = {call MyTestProc(@alpha=?, @gamma=?)}
___test!call!param[1]!type = integer
___test!call!param[1]!inout = in
___test!call!param[1]!is-null = false
___test!call!param[1]!value = 1
___test!call!param[2]!type = integer
___test!call!param[2]!inout = in
___test!call!param[2]!is-null = false
___test!call!param[2]!value = 2
and received the following.
[java] SQLUnit TUI Tool
[java] Copyright(c) 2003 The SQLUnit Team
[java] SQLUnitTUITool:_captureFile> /sqlunit_38/output/test.xml
[java] SQLUnitTUITool:_connection!driver> com.microsoft.jdbc.sqlserver.SQLServerDriver
[java] SQLUnitTUITool:_connection!url> jdbc:microsoft:sqlserver://GRANITE\SQL2K:4086;databaseName=James_217
[java] SQLUnitTUITool:_connection!user> sqlunit
[java] SQLUnitTUITool:_connection!password> sqlunit
[java] SQLUnitTUITool:__test!name> Test
[java] SQLUnitTUITool:__test!call!stmt> {call MyTestProc(@alpha=?, @gamma=?)}
[java] SQLUnitTUITool:___test!call!param[1]!type> integer
[java] SQLUnitTUITool:___test!call!param[1]!inout> in
[java] SQLUnitTUITool:___test!call!param[1]!is-null> false
[java] SQLUnitTUITool:___test!call!param[1]!value> 1
[java] SQLUnitTUITool:___test!call!param[2]!type> integer
[java] SQLUnitTUITool:___test!call!param[2]!inout> in
[java] SQLUnitTUITool:___test!call!param[2]!is-null> false
[java] SQLUnitTUITool:___test!call!param[2]!value> 2
[java] DEBUG [main] (HandlerFactory.java:49) - >> getInstance(connection)
[java] DEBUG [main] (ConnectionHandler.java:77) - >> process()
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(off)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(off)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(com.microsoft.jdbc.sqlserver.SQLServerDriver)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(com.microsoft.jdbc.sqlserver.SQLServerDriver)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(jdbc:microsoft:sqlserver://GRANITE\SQL2K:4086;databaseName=James_217)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(jdbc:microsoft:sqlserver://GRANITE\SQL2K:4086;databaseName=James_217)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(sqlunit)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(sqlunit)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(sqlunit)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(sqlunit)
[java] DEBUG [main] (ConnectionRegistry.java:93) - >> setConnectionById(null)
[java] DEBUG [main] (ConnectionFactory.java:56) - >> getInstance(props)
[java] DEBUG [main] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [main] (HandlerFactory.java:49) - >> getInstance(call)
[java] DEBUG [main] (CallHandler.java:60) - >> process()
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(null)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(null)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue({call MyTestProc(@alpha=?, @gamma=?)})
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName({call MyTestProc(@alpha=?, @gamma=?)})
[java] DEBUG [main] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [main] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(1)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(1)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(integer)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(integer)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(1)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(1)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(in)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(in)
[java] DEBUG [main] (HandlerFactory.java:49) - >> getInstance(param)
[java] DEBUG [main] (ParamHandler.java:51) - >> process(elParam)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(2)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(2)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(integer)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(integer)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(false)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(false)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(2)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(2)
[java] DEBUG [main] (SymbolTable.java:86) - >> getValue(in)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(in)
[java] DEBUG [main] (ConnectionRegistry.java:54) - >> getConnectionById(null)
[java] DEBUG [main] (SqlTypeUtils.java:133) - >> convertToObject(1,integer)
[java] DEBUG [main] (SymbolTable.java:197) - >> isVariableName(1)
[java] DEBUG [main] (SqlTypeUtils.java:110) - >> getSqlTypeFromXmlType(integer)
[java] DEBUG [main] (SqlTypeUtils.java:404) - >> createFieldMap()
[java] DEBUG [main] (ConnectionRegistry.java:108) - >> invalidate(null)
[java] DEBUG [main] (SymbolTable.java:74) - >> setObject(${__FailureMessage__},net.sourceforge.sqlunit.SQLUnitException
[java] Error: null
[java] java.lang.NullPointerException
[java] at net.sourceforge.tuisqlunittool.SQLUnitTUITool.generate(SQLUnitTUITool.java:265)
[java] at net.sourceforge.tuisqlunittool.SQLUnitTUITool.run(SQLUnitTUITool.java:190)
[java] at net.sourceforge.tuisqlunittool.SQLUnitTUITool.main(SQLUnitTUITool.java:119)
As an additional note, if you move all your default parameters to the end of the parameter list (like most people probably will), you can just omit the last "n" parameters. Of course, you are back to the original problem if you want to specify values for some, but not all, of the default parameters.
I tried a test against the sybase ASE installation I have access to, with a procedure which has 3 parameters, two of which (at the end) are default parameters:
create procedure MyProc (
@p1 integer,
@p2 integer=null,
@p3=0)
as
begin
...
and called the code in the SQLUnit test like so:
<call>
..<stmt>{call MyProc(@p1=?)}</stmt>
..<param id="1" type="INTEGER">10171838</param>
</call>
and:
<call>
..<stmt>{call GetProductReviewsForProduct(@p1=?,@p3=?)}</stm
t>
..<param id="1" type="INTEGER">10171838</param>
..<param id="2" type="INTEGER">1</param>
</call>
and both worked without problems. I did find a bug in the code where the statement object is being closed twice, which I also fixed.
I also tested with the TUITool (after fixing the bug), and was able to get the result that I expected.
I am thinking that the problem is the bug I found which was causing the same problem that you identify in your stack trace. Thank you for catching it. Its already fixed in CVS, the file that changed is SqlHandler.java.
It will be in tomorrow's release (3.9).
-sujit