Running SQL Stored Procedures in Script

Hello all,

So I have a power table that shows all of the relevant data from whatever table is being queried. One of my requirements is to make that data editable in the power table and write the new value back to the SQL database. This part works fine, but I also have to call a stored procedure that takes the updated value and returns a matching UUID from an external SQL database. For some reason, I cannot get the stored procedure to execute. I confirmed this by digging through my database services to see what all had been called in my testing time frame. Below is my script, which is consistent with the Ignition Documentation. This script lives in the “onCellEdited” part of extension functions for Power Tables.

#update path (not really important for the question)
tableName = ‘SiteLocal’
id = self.data.getValueAt(rowIndex, ‘SiteLocalID’)
query = 'UPDATE SiteLocal SET ’ +colName + ‘= ? WHERE SiteLocalID = ?’
args = [newValue, id]
system.db.runPrepUpdate(query,args, ‘MasterData_R2’)
system.db.refresh(self, ‘data’)
#run stored procedure (below is where my problem lies)
proc = system.db.createSProcCall(“dbo.local_UpdateLocalElementUUIDs”)
proc.registerInParam(‘TableName’, system.db.VARCHAR, tableName)
proc.registerInParam(‘LocalId’, system.db.VARCHAR, id)
system.db.execSProcCall(proc)

If you need any more information I can try to supply it… Thanks in advance!

Best,

Jarred

Are you running them inline in the script?
If so, the system.db.refresh call will Null your id variable before it gets to the stored procedure call.