Action script to loop through table and execute sproc

Hi All: I have a table that the user can input values in a couple of columns, for some or all rows. When the click a button I’d like to loop through the rows of the table and execute a stored procedure for any rows that have new inputs in one or both of said columns. Here is the code I’m using:

import time
localtime = time.localtime(time.time())
table = event.source.parent.getComponent('Table') 	
pyTable = fpmi.dataset.toPyDataSet(
Distid=event.source.parent.getComponent('Distributor').selectedValue				###########  Validate Data  ############
for row in range(len(pyTable)):
	EndingInvValue =, 4)
	SalesValue =, 5)
	BrandValue =, 0)
	PacksizeidValue =, 3)
#	CurYear=fpmi.db.dateFormat(now(),yyyy)
#	CurMonth=fpmi.db.dateFormat(now(),M)
	if EndingInvValue.isdigit()  <> 0 or SalesValue.isdigit()  <> 0:  #Validate data
		cr = fpmi.db.runPrepStmt("EXEC dbo.usp_PMIDepletionsInsert (?,?,?,?,?,?,?,?)", [Distid,Dateid,CurYear,CurMonth,BrandValue,PacksizeidValue,EndingInvValue,SalesValue])

The error that pmi throws is
Gateway error 301:
SQL error for “Exec dbo.usp_PMIDepletionsInsert(?,?,?,?,?,?,?,?)”: Incorrect syntax near ‘@PO’.
For Query EXEC dbo.usp_PMIDepletionsINsert(?,?,?,?,?,?,?,?)
Error 1 out of 7

And finally, the sql server sproc is:

ALTER PROCEDURE usp_PMIDepletionsInsert
@distid VARCHAR(10)
,@Dateid VARCHAR(10)
,@CurYear VARCHAR(10)
,@CurMonth VARCHAR(10)
,@Brand CHAR(3)
,@Packsizeid VARCHAR(10)
,@EndingInv VARCHAR(10)= NULL
,@Sales VARCHAR(10) = NULL


SELECT @PRODID=PRODID FROM dimPRoducts WHERE Productbrandcode=@Brand AND Packagesizeid=@packsizeid

IF LEN(@EndingInv)=0  SET @EndingInv='0'
IF LEN(@Sales)=0  SET @Sales='0'
		INSERT INTO tblDepletions_test

tia. d. lewis

That error message is just like the one Ron got in his recent post. Maybe they are related? I’m thinking this is on your database’s side, not on FactoryPMI’s side.

That’s what Ron just said… I’m willing to consider the possibility, but there is nothing like an “@PO” in the sproc (which is why I included that code in the first post). Also, if I run the following from within management studio, it will insert correctly:

EXEC usp_PMIDepletionsInsert ‘1’,‘110’,‘2009’,‘2’,‘SFB’,‘1’,‘10’,‘11’

and that is what I am hoping is being passed from pmi.
It is a little odd (to me at least) that we are both getting the same error when he is executing an “INSERT INTO …” sql statement, and I am trying to execute a sproc.
I can’t find any hint of a problem on the db side…

The most recent EXEC did not match the call from FactoryPMI. One has parenthesis around the arguments, the other does not, one also specifies dbo. I can guarantee that FactoryPMI is not inserting any @PO into your calls. It is conceivable that the JDBC driver is, but seems unlikely.

FWIW, also realize also that FactoryPMI is issuing this command as a query, which can be different than some sort of T/SQL mode in the management studio, but I think that MS SQL Server does accept EXEC calls as queries.

hi carl: You likely know all this, but just to be complete, from within management studio you can execute a stored procedure as follows:

EXEC nameofprocedure ‘’,’’,’’,’’,’’ – with all the parameters in the order they are expected.
nameofprocedure @xyz=‘33’,@jjj=’’, etc. – (no EXEC keyword) with all the parameters explicitly named and given values (in this case they don’t need to be in the order they are declared in the sproc).

One can call the procedure with its full name databasename.owner.procedure or as owner.procedure or as procedure.

You can also execute it as a t-sql string (at least I’m pretty sure about this), as in
EXEC (string variable representing the procedure name with parameters).
Is this how pmi is passing things to sql server?

I can set up a trace on Monday to track what the db is receiving. Also, I’m not wedded to this method. All I want to do is loop through each row of a table, examine the contents of some of the cells, and under certain conditions fire off a sproc. If you have any other suggestions of how to do this, please let me know. thx. d lewis

Hi all: I cleaned up the approach a bit (hopefully simplified it) to:

[code]import time
localtime = time.localtime(time.time())
table = event.source.parent.getComponent(‘Table’) #Displayed dataset table name
pyTable = fpmi.dataset.toPyDataSet(
print Dateid
Distid=event.source.parent.getComponent(‘Distributor’).selectedValue #Database key column
print Distid
########### Validate Data ############
for row in range(len(pyTable)):
EndingInvValue =, 4)
SalesValue =, 5)
BrandValue =, 0)
PacksizeidValue =, 3)



if EndingInvValue.isdigit()  <> 0 or SalesValue.isdigit()  <> 0:  #Validate data
	strSQL="EXEC dbo.usp_PMIDepletionsInsert '" + str(Distid) + "','"
	strSQL=strSQL+str(Dateid) + "','"
	strSQL=strSQL+str(CurYear) + "','"
	strSQL=strSQL+str(CurMonth) + "','"
	strSQL=strSQL+str(BrandValue) + "','"
	strSQL=strSQL+str(PacksizeidValue) + "','"
	strSQL=strSQL+str(EndingInvValue) + "','"
	strSQL=strSQL+str(SalesValue) + "'"
	print strSQL
	cr = fpmi.db.runPrepStmt(strSQL)

The error generated by this was:

EXEC dbo.usp_PMIDepletionsInsert ‘144’,‘98’,‘2009’,‘3’,‘PAL’,‘5’,‘2’,’’
ERROR [ActionAdapter-AWT-EventQueue-0] Error executing script for event: actionPerformed
on component: RefreshButton.
Traceback (innermost last):
File “event:actionPerformed”, line 29, in ?
TypeError: runPrepStmt(): expected 2-3 args; got 1

at org.python.core.Py.TypeError(
at org.python.core.PyReflectedFunction.throwError(
at org.python.core.PyReflectedFunction.throwArgCountError(
at org.python.core.PyReflectedFunction.throwError(
at org.python.core.PyReflectedFunction.__call__(
at org.python.core.PyReflectedFunction.__call__(
at org.python.core.PyObject.__call__(
at org.python.core.PyObject.invoke(
at org.python.pycode._pyx63.f$0(<event:actionPerformed>:29)
at org.python.pycode._pyx63.call_function(<event:actionPerformed>)
at org.python.core.Py.runCode(
at com.inductiveautomation.factorypmi.application.script.ScriptManager.runCode(
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.runActions(
at com.inductiveautomation.factorypmi.application.binding.action.ActionAdapter.invoke(
at com.inductiveautomation.factorypmi.application.binding.action.RelayInvocationHandler.invoke(
at $Proxy0.actionPerformed(Unknown Source)
at javax.swing.AbstractButton.fireActionPerformed(Unknown Source)
at javax.swing.AbstractButton$Handler.actionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.fireActionPerformed(Unknown Source)
at javax.swing.DefaultButtonModel.setPressed(Unknown Source)
at javax.swing.plaf.basic.BasicButtonListener.mouseReleased(Unknown Source)
at java.awt.AWTEventMulticaster.mouseReleased(Unknown Source)
at java.awt.Component.processMouseEvent(Unknown Source)
at javax.swing.JComponent.processMouseEvent(Unknown Source)
at java.awt.Component.processEvent(Unknown Source)
at java.awt.Container.processEvent(Unknown Source)
at java.awt.Component.dispatchEventImpl(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.LightweightDispatcher.retargetMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.processMouseEvent(Unknown Source)
at java.awt.LightweightDispatcher.dispatchEvent(Unknown Source)
at java.awt.Container.dispatchEventImpl(Unknown Source)
at java.awt.Window.dispatchEventImpl(Unknown Source)
at java.awt.Component.dispatchEvent(Unknown Source)
at java.awt.EventQueue.dispatchEvent(Unknown Source)
at java.awt.EventDispatchThread.pumpOneEventForFilters(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForFilter(Unknown Source)
at java.awt.EventDispatchThread.pumpEventsForHierarchy(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at java.awt.EventDispatchThread.pumpEvents(Unknown Source)
at Source)


The string generated by the action script –
EXEC dbo.usp_PMIDepletionsInsert ‘144’,‘98’,‘2009’,‘3’,‘PAL’,‘5’,‘2’,’’

for the first row in the table it was looping through ran without error when I cut and pasted it into management studio. I don’t understand why pmi tells me runPrepStmt requires two or three parameters. The help files state that the query is required, while the datasource and parameters are optional…
Any thoughts would be appreciated. thx. d. lewis

David, the parameters are not optional, only the datasource. Furthermore, if you don’t have any parameters, you don’t need to use a prepared statement - just use fpmi.db.runQuery or fpmi.db.runUpdateQuery if your sproc doesn’t return results.

Bueno. runUpdateQuery works perfect. thx.