Problem with createSProcCall and MS SQL Procedure parameters

Ignition
1

I have come across an issue with createSProcCall and calling MS-SQL stored procedures that have defaulted parameters. When passing in named or indexed parameters with registerInParam, it seems that you have to explicitly pass all of the parameters, even if some are defaulted in the stored procedure definition.

There is a thread from a few months back that addresses the same issue, but I didn’t see a resolution in there so I figured I’d start a new one.
http://www.inductiveautomation.com/forum/viewtopic.php?f=70&t=9594&p=33261&hilit=execSProcCall&sid=bc824704595a6480d990ac5f8e750d14&sid=33802ee05b4f768c8c38976ecbeb953e#p33261

SQL Procedure definition, with defaulted parameters:

CREATE PROCEDURE TestProcedure (
  @parm1 varchar(10) = null
, @parm2 varchar(10) = null
, @parm3 varchar(10) = null
)
AS 
SELECT @parm1 AS Parm1, @parm2 AS Parm2, @parm3 AS Parm3

--Test run in sql, should return dataset [null, null, 'test 3']
--EXECUTE TestProcedure @parm3 = 'test 3'

Python code to generate the error:

call = system.db.createSProcCall("TestProcedure")
call.registerInParam("parm3", system.db.VARCHAR, "test 3")

#Index error 
system.db.execSProcCall(call)

results = call.getResultSet()
for row in range(results.rowCount):
	for col in range(results.columnCount):
		print results.getValueAt(row, col)

I receive the following error:
java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()
caused by Exception: Error executing system.db.execSProcCall()
caused by GatewayException: The index 3 is out of range.
caused by SQLServerException: The index 3 is out of range.

If I instead pass in just parm2, I get same error referencing index 2. If you go ahead an pass in all the parameters, it will work just fine however. You just have to remember to use None (as opposed to system.db.Null), which will pass NULL value to the parameters.

Working code:

[code]call = system.db.createSProcCall(“TestProcedure”)

#Do not use system.db.NULL as it will pass in ‘0’, use None to pass NULL to sql instead
call.registerInParam(“parm1”, system.db.VARCHAR, system.db.NULL)
call.registerInParam(“parm2”, system.db.VARCHAR, None)
call.registerInParam(“parm3”, system.db.VARCHAR, “test 3”)

#No error
system.db.execSProcCall(call)

results = call.getResultSet()
for row in range(results.rowCount):
for col in range(results.columnCount):
print results.getValueAt(row, col)[/code]

This is a small issue and not that big of a deal, but I figured I would at least report it.