Forum Discussion

Cogenics's avatar
Cogenics
New Contributor
4 years ago

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

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)

2 Replies

    • Are you missing double quotes in your prepareCall? That is, "CALL SP('" and trailing single quote should be closed "'".