Trouble calling a stored procedure from a tag value change script - why?

I am using a tag value change to trigger calling a stored procedure that does some database maintenence.

Here’s what I have -

if currentValue.value == 7:
    import system
    system.db.runUpdateQuery("INSERT INTO test(i) VALUES (11)", "AntiDLocale")
    call = system.db.createSProcCall("[sp_performarchive]", "AntiDLocale")    
    system.db.runUpdateQuery("INSERT INTO test(i) VALUES (12)", "AntiDLocale")

The insert into test(i) was just to make sure I was able to run SOME queries and the first one works (11), the second one is never reached (12). I’ve also tried this as system.db.createSProcCall("sp_performarchive", "AntiDLocale") and system.db.createSProcCall("sp_performarchive()", "AntiDLocale"), any idea what is going wrong here?

Add try/except blocks to your code and use a logger in the except clauses to send the errors to your gateway log.

Can you elaborate on sending the logs? What function is that?

I usually use something like this:

import java.lang.Exception
import traceback
logger = system.util.getLogger('')

	# some code you are having trouble with
except java.lang.Exception, e:
	logger.warn("Java Exception trying to do whatever", e)
except Exception, e:
	tb = traceback.format_exc()
	logger.warn("Python Exception trying to do whatever\n"+tb)
1 Like

Ok I set that up but for some reason I am having a very hard time actually finding the logs in my Ignition gateway for some reason. Sorry to keep bothering you about this is probably pretty trivial but I’ve never used the logger before. Was there any preamble I was supposed to do prior to this script to set up a logger? Where in the gateway would I see the messages?

Status ==> Logs. Warning log entries are shown by default.

Does sp_performarchive return a value? If it does then you must use registerReturnParam to indicate the type of the return value.

I’m not sure what DB you’re using (SQL, MYSQL, ORACLE, etc…), depending on that you may need to use a type code to indicate the type of the return param.

All of this information is in the documentation for system.db.createSProcCall. My best guess is one of these is causing the hang.

Also this is the correct syntax:

system.db.createSProcCall("sp_performarchive", "AntiDLocale")