Running a stored procedure on a button with python

Ignition
,
1

Good afternoon, I have a button that I need to run a stored procedure on. Currently my script looks like this:

call = system.db.createSProcCall("dbo.ReserveBuggyInCorral", [IGV_Prd])
system.db.execSProcCall(call)

Is this the correct way of doing it?
I am very new to stored procedures.
I need to pass 2 variables in the stored procedure, how do I go about doing that?

2

You'll need to register your parameters before you execute the stored procedure.

call.registerInParam(index OR name, typeCode, value)
call.registerOutParam(index OR name, typeCode)
call.registerReturnParam(typeCode)
3

These are the 2 from the SP on the DB:

@BUGGY_ID nvarchar(8),
@TSFO_ID nvarchar(256)

I can get those from the .props stuff on my project:

Buggy_ID = self.getSibling("Table_0").props.selection.data[0].Buggy_ID
TSFO_ID = self.getSibling("Table").props.selection.data[0].ID

So would it be something like this:

call = system.db.createSProcCall("dbo.ReserveBuggyInCorral", [IGV_Prd])
	call.registerInParam(@BUGGY_ID, system.db.NVARCHAR, Buggy_ID)
	call.registerInParam(@TSFO_ID, system.db.NVARCHAR, TSFO_ID)
	system.db.execSProcCall(call)
4

That looks right, but is [IGV_Prd] what the database connection in ignition is actually named and if it is i think it needs quotes around it?

Oh, i think you need to change this too:

call.registerInParam("BUGGY_ID", system.db.NVARCHAR, Buggy_ID)
call.registerInParam("TSFO_ID", system.db.NVARCHAR, TSFO_ID)
5

Yeah it should look like this:

 call = system.db.createSProcCall("dbo.ReserveBuggyInCorral", 'IGV_Prd')
 call.registerReturnParam(system.db.INTEGER)
 call.registerInParam("BUGGY_ID", system.db.NVARCHAR, Buggy_ID)
 call.registerInParam("TSFO_ID", system.db.NVARCHAR, TSFO_ID)
 system.db.execSProcCall(call)

Thanks for that

That looks like its running, I get no errors. I return a "0" which isnt a good or a bad thing. Ill need to put some test data into that DB to make sure. I should return a 1 if its all good and 0 if not.

In the SQL server, when it runs it does have an error handler on it. In this case returns:

Wrong Order ID or unable to retrieve order informations
Msg 50000, Level 1, State 1

Can I get this to return that also?

6

I am not sure about that, I've use a returnParamater to get a record id before, but that's about the extent of my experience.