Help with calling a stored procedure with a button in perspective

Ignition
, ,
1

I am trying to use this script

def runAction(self, event):
    SPcall = system.db.createSProcCall("BadgeAccess","Ignition")      
    SPcall.registerReturnParam(system.db.INTEGER)
    input1 = self.view.custom.testAccess.user
    input2 = self.view.custom.testAccess.machine
    SPcall.registerInParam("user", system.db.INTEGER, input1)
    SPcall.registerInParam("machine", system.db.INTEGER, input2)
    system.db.execSProcCall(SPcall)
    accessLevel = SPcall.getReturnValue()
    displayString = "User {self.view.custom.testAccess.user} logged into machine {self.view.custom.testAccess.machine} with level {accessLevel} access"
    self.view.custom.testAccess.displayString = displayString
    # Print the result to the console
    print(displayString)

To call a SP I have in my database. The SP runs fine on the database and has 2 inputs as INT's and an output as an INT. I would rather not have to hard type the query in Ignition and would rather run it as a SP since there is also logging occurring for this.

This is the error I am getting.
Error running action 'component.onActionPerformed' on MachineAccess/main@D/root/FlexContainer/FlexContainer/Button: Traceback (most recent call last): File "function:runAction", line 8, in runAction at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.error(AbstractDBUtilities.java:392) at com.inductiveautomation.ignition.common.script.builtin.AbstractDBUtilities.execSProcCall(AbstractDBUtilities.java:530) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at java.base/jdk.internal.reflect.NativeMethodAccessorImpl.invoke(Unknown Source) at java.base/jdk.internal.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source) at java.base/java.lang.reflect.Method.invoke(Unknown Source) java.lang.Exception: java.lang.Exception: Error executing system.db.execSProcCall()

3

What database are you using (e.g. MySQL, MSSQL, Postgres)? Usually, what we found works well enough is to create a named query and then do something like

EXEC dbo.myBadgeAccess :user, :machine

and if you're expecting a single value as a return, set the Query Type to Scalar Query.