Use lists with a For Loop

I am trying to consolidate some code and use three lists to populate the.registerInParam function. I am using it to call a stored procedure. I originally had left out lines 2-7, and used the next four lines in order to pass in four parameters into the stored procedure and it worked using lines 8-11. Using lines 2-7 I get the error below. Below is the code:

	call = system.db.createSProcCall("CreateLotRecords")
	ParamNum = [1,2,3,4]
	Type = ["system.db.VARCHAR","system.db.VARCHAR","system.db.VARCHAR","system.db.INT"]
	Value = ['L20','P20','H20',20]
	i = 0
	for i in range(4):
		call.registerInParam(ParamNum,Type,Value)
#	call.registerInParam(1, system.db.VARCHAR, 'L20')
#	call.registerInParam(2, system.db.VARCHAR, 'P20')
#	call.registerInParam(3, system.db.VARCHAR, 'H20')
#	call.registerInParam(4, system.db.INTEGER, 20)
	
	system.db.execSProcCall(call)

I am getting the following error when I try to run the script with line 8 being call.registerInParam(ParamNum,Type,Value):

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
File “function:runAction”, line 8, in runAction
TypeError: registerInParam(): 1st arg can’t be coerced to int, String

Add indexes to the list items:

call.registerInParam(ParamNum[i],Type[i],Value[i])

As posted above, your index i is not being used in the call; it’s passing in the entire lists each time.

You can also drop i = 0. range(4) = [0,1,2,3] regardless of what you set i to before that.

EDIT: I like @PGriffith’s solution below and missed the issue with referencing types as strings. Your original code should also work if edited like this:

	call = system.db.createSProcCall("CreateLotRecords")
	ParamNum = [1,2,3,4]
	Type = [system.db.VARCHAR,system.db.VARCHAR,system.db.VARCHAR,system.db.INT]
	Value = ['L20','P20','H20',20]
	for i in range(4):
		call.registerInParam(ParamNum[i],Type[i],Value[i])
	system.db.execSProcCall(call)

Or you could skip ParamNum and do it like this:

	call = system.db.createSProcCall("CreateLotRecords")
	Type = [system.db.VARCHAR,system.db.VARCHAR,system.db.VARCHAR,system.db.INT]
	Value = ['L20','P20','H20',20]
	for i in range(4):
		call.registerInParam(i+1,Type[i],Value[i])
	system.db.execSProcCall(call)
call = system.db.createSProcCall("CreateLotRecords")
params = [
	("L20", system.db.VARCHAR),
	("P20", system.db.VARCHAR),
	("H20", system.db.VARCHAR),
	(20, system.db.INT)
]
for index, (value, dbType) in enumerate(params):
	call.registerInParam(index + 1, value, dbType)

system.db.execSProcCall(call)

system.db.VARCHAR, INT etc are constants and must be referenced as such; your Type list in the initial example is trying to use literal strings.
Python has a built in function enumerate that gives you indexed access to an iterable (0 based, hence the + 1).
The parentheses in the for loop ‘unpack’ the (value, type) tuple into variable called value and type, respectively.

4 Likes

Copied your code verbatim and I am still getting error saying first arg can’t be coerced to int, String.

I did think there was a way to use the “index” value and create a list/dictionary for the other two.

Here is the code:

	#	call = system.db.createSProcCall("CreateLotRecords")
#	ParamNum = [1,2,3,4]
#	Type = ['system.db.VARCHAR','system.db.VARCHAR','system.db.VARCHAR','system.db.INT']
#	Value = ['L20','P20','H20',20]
#	for i in range(4):
#		call.registerInParam(ParamNum[i],Type[i],Value[i])
#	call.registerInParam(1, system.db.VARCHAR, 'L20')
#	call.registerInParam(2, system.db.VARCHAR, 'P20')
#	call.registerInParam(3, system.db.VARCHAR, 'H20')
#	call.registerInParam(4, system.db.INT, 20)
	
#	system.db.execSProcCall(call)

	call = system.db.createSProcCall("CreateLotRecords")
	params = [
		("L20", system.db.VARCHAR),
		("P20", system.db.VARCHAR),
		("H20", system.db.VARCHAR),
		(20, system.db.INTEGER)
	]
	for index, (value, dbType) in enumerate(params):
		call.registerInParam(index + 1, value, dbType)

	system.db.execSProcCall(call)

And the error still coming up:

Error running action ‘component.onActionPerformed’ on Playground@D/root/Button_7: Traceback (most recent call last): File " ", line 23, in runAction TypeError: registerInParam(): 1st arg can’t be coerced to int, String

com.inductiveautomation.ignition.common.script.JythonExecException
Traceback (most recent call last):
File “function:runAction”, line 23, in runAction
TypeError: registerInParam(): 1st arg can’t be coerced to int, String

You’re putting the parameters in the wrong order - it needs to be index, type, value;

for index, (value, dbType) in enumerate(params):
	call.registerInParam(index + 1, dbType, value)
1 Like

Figured it out. I saw you had the dbType and value backwards. The registerInParam needs the index number, then dbType, then value. Once I switched them it worked!

In reality I am going to be filling the values from tag values. Guessing I have to create a python dataset and then feed that into the values somehow. Here is edited code:

	call = system.db.createSProcCall("CreateLotRecords")
	params = [
		(system.db.VARCHAR,"L20"),
		(system.db.VARCHAR,"P20"),
		(system.db.VARCHAR,"H20"),
		(system.db.INTEGER,20)
	]
	for index, (dbType, value) in enumerate(params):
		call.registerInParam(index + 1, dbType, value)

	system.db.execSProcCall(call)

What you could do is read the tag and the tag's 'DataType' value, then build a mapping of data types to system.db types - so your input to this stored procedure call would be a list of tagpaths, and the code would 'figure out' the rest of what's required.

1 Like

Ah, pretty good idea! Definitely worth doing, thanks!