How things work - no feedback from stored procedure?

Ignition
1

hi
I am using stored procedure to send data from Ignition to other system. I didn't get my head around how to capture the feedback from the executed stored procedure - there must be a way -right? Stored procedure itself i was told to use does not provide any value and from what i read in docs SProcCall also does not provide any feedback.

So if stored procedure itself does not provide any return value is using it like fire and forget :face_with_raised_eyebrow: ?
My code wrapped in the function looks following :

def TriggerStoredProcedure(message):
	call = system.db.createSProcCall("dbo.StoredProcedureName", "DatabaseConnectionName")
	call.registerInParam(1, system.db.INTEGER, 1)
	call.registerInParam(2, system.db.VARCHAR,message)
	call.registerInParam(3, system.db.INTEGER, 2)
	call.registerReturnParam(system.db.INTEGER)
	system.db.execSProcCall(call)

i believe there is no need for call.registerReturnParam(system.db.INTEGER) line.
Thanks for any help
@pturmel any chance for a comment ?

2

Hmm. Not sure. I would think system.db.execSProcCall() itself might throw an exception if the proc is rejected up front.

1 Like
3

yes i do get exception that parameters were missing

4

but if stored procedure did not execute for any other reason like permission, connection etc i could use some logic to resend the data

5

error message below when i removed 1 parameter

image
image1717×26 4.43 KB

6

You're blazing a trail here. I don't recall ever deploying a stored procedure call that didn't return anything at all.

1 Like
7

i agree it sounds strange but i was given this stored procedure and i was told that it does not return anything for legacy code purposes. So i guess my sanity check has passed and all i can do is use try: except wrapper which would catch lack of parameter passed.

8

Could you create a "wrapper" stored procedure on the same server that:

  1. Calls the existing stored procedure
  2. Checks that the the task was accomplished
  3. Returns some value based on step 2?
2 Likes
9

Stored procedures always return a value. If no return statement is provided then the default return is 0.

Due to the way that system.db.execSProcCall() works you must register a return param. However, much like system.tag.writeBlocking() and similar functions providing a return value, that doesn't mean you have to use it.

The quirkiness of SProcCall and its helpers is why I have moved away from SP when Ignition is involved, and if I want to execute a SP, I will just create a Named Query to execute it and move on.

4 Likes
10

Thanks for help. I read somewhere in docs that preferred way to call stored procedure is with system.db.execSProcCall(). Can you please elaborate on the benefit of using named query - what steps are needed and what benefits are gained doing this?

11

Sure.

You would create a named query that looked like this:

EXECUTE dbo.StoredProcedureName :param1 , :message , :param2

You would call it from script like this:

def triggerStoredProcedure(message):
    system.db.runNamedQuery('YourNamedQuery',{'param1':1,'message':message,'param2':2})

And...You're done, much simpler. It's truly fire and forget in this case as you're unconcerned with the return value, though it will also work with stored procedures that return result sets.

You get all of the advantages of a named query, and you don't run into the eccentricities that come with creating a SPCall. Back in the days prior to named queries this was the only way to call stored procedures, IMO named queries have made the method mostly obsolete.

All of that out of the way, unless you're doing some really advanced things in the stored procedure that isn't handled so well by the JDBC driver, and those kinds of things do exist, or you have other third party software that is also using the stored procedure, I would recommend just putting the SQL directly in the named query itself. Makes things much more manageable IMO.

2 Likes
12

You answered my question really well so many thanks. I am curious type - any chance you give me a gist on :

unless you're doing some really advanced things in the stored procedure that isn't handled so well by the JDBC driver, and those kinds of things do exist,

What sort of fancy things i need to be aware of - i am working mostly with Microsoft SQL server - is it worth reading JDBC specification for microsoft server jdbc driver if time permits?

13

Batching operations, Cursors, lots of work with creation of and dropping of temporary tables. Anything where the query optimization is really important is most likely going to be better handled in a Stored Procedure.

For most things that you're going to do with Ignition, Named Queries will work just fine. Its the types of things that you find your self googling "How to do X" with a purpose other than a reminder of the needed syntax.

1 Like