When I try to call a SP in DB2 using script snap with python language which has total 7 parameters out of it 5 are input and 2 are output parameters. I am getting below error even though I am registering 2 output parameters.
Error message:
The number of parameter values set or registered does not match the number of parameters., Reason: Script failed with the following error: The number of parameter values set or registered does not match the number of parameters.
Below is the code which I am using:
Import the interface required by the Script snap.
from com.snaplogic.scripting.language import ScriptHook
import java.util
import java.sql.DriverManager
import java.sql.Connection
import java.sql.Types
import java.sql.DatabaseMetaData
import sys
class TransformScript(ScriptHook):
def init(self, input, output, error, log):
self.input = input
self.output = output
self.error = error
self.log = log
# The "execute()" method is called once when the pipeline is started
# and allowed to process its inputs or just send data to its outputs.
def execute(self):
# self.log.info("Executing Transform script")
while self.input.hasNext():
try:
# Read the next input document, store it in a new dictionary, and write this as an output document.
inDoc = self.input.next()
conDB2 = java.sql.DriverManager.getConnection()
db2Query = conDB2.prepareCall(CALL SP('"+inDoc['input1']+"','"+inDoc['input2']+"','"+inDoc['input3']+"','"+inDoc['input4']+"','"+inDoc['input5']+"',?,?)
self.output.write("SP OUT")
db2Query.registerOutParameter(1, java.sql.Types.INTEGER)
self.output.write("OUT 1")
db2Query.registerOutParameter(2, java.sql.Types.CHAR)
self.output.write("OUT 2")
isRanSuccessfully = db2Query.execute()
returnCode = db2Query.getString(1)
otherMIS = db2Query.getString(2)
outDoc = {
'original' : inDoc['Stored_Proc']
#"CALL SP('"+inDoc['input1']+"','"+inDoc['input2']+"','"+inDoc['input3']+"','"+inDoc['input4']+"','"+inDoc['input5']+"',?,?)"
}
self.output.write(inDoc, outDoc)
except Exception as e:
errDoc = {
'error' : str(e)
}
self.log.error("Error in python script")
self.error.write(errDoc)
self.log.info("Script executed")
# The "cleanup()" method is called after the snap has exited the execute() method
def cleanup(self):
self.log.info("Cleaning up")
The Script Snap will look for a ScriptHook object in the “hook”
variable. The snap will then call the hook’s “execute” method.
hook = TransformScript(input, output, error, log)