Call MSSQL Stored Procedure from Gateway Event Script

[israel.jordan]

Hello I am trying to call a Stored Procedure from a Gateway Event Script and I keep getting the following:

(<type 'java.lang.Exception'>, java.lang.Exception: Error executing system.db.execSProcCall(), <traceback object at 0x1f1>)

I am trying to call my Stored Procedure using the following:

call = system.db.createSProcCall("my_stored_proc", "my_db")
call.registerInParam("in_param", system.db.VARCHAR, the_value)
call.registerOutParam("out_param1", system.db.TIMESTAMP)
call.registerOutParam("out_param2", system.db.VARCHAR)
call.registerOutParam("out_param3", system.db.VARCHAR)  
call.registerOutParam("out_param4", system.db.VARCHAR)
call.registerOutParam("out_param5", system.db.VARCHAR)
call.registerOutParam("out_param6", system.db.VARCHAR)
call.registerOutParam("out_param7", system.db.TIMESTAMP)
call.registerOutParam("out_param8", system.db.VARCHAR)
system.db.execSProcCall(call)

Is is possible to call the Stored Procedure from a Gateway Even Script? I have considered using a named query but I will need to use SQL variables and I believe that is not allowed in Named Queries. Please correct me if I am wrong. I have verified that my Stored Procedure runs properly using SQL Management Studio but I cannot get it to work in my script. Can someone please help me out?

~Kind regards

[bkarabinchak.psi]

I just tested it and yes you can put a stored procedure into a named query. I’m using MySQL so it was like
CALL myStoredProecdure(:input1, :input2, ...)

You would pass in the parameters for the named query as a data dictionary. So
system.db.runNamedquery("myQueryName", {"input1":1, "input2":2, ...})

I don’t see any reason why you could not do this in a gateway event script.

Try it with the named query methodolgy. I am not sure how you will get results since you have 8 out paramters, I would guess it would be a row with 8 columns.

Try it in the named query development section first. see how it works. Once you set up the IN parameter, go to the testing section and try running it and make sure your results are what you expect. If everythign is fine, then yes I don’t see any reason you could not use system.db.runNamedQuery in a gateway event script.

[josborn]

I've only done this one time but i think its because you are trying to type the named query in where the stored procedure would go.

call = system.db.createSProcCall("TEST.dbo.somax_sp_wwr_add", "WDEVSQL2012")

[mdemaris]

Well, my last two posts here are under the wrong thread!
Here is my thread, and I will copy and paste these two posts to that thread.

Calling Stored Procedure

I am attempting to call an SP using a button script event, and testing using a Named Query. Server: MSSQL Button Script: def runAction(self, event): item = self.parent.parent.getChild("Spacer_1").getChild("Label_0").props.text qtyLeft = self.parent.parent.getChild("FlexContainer_2").getChild("QtyLeft_1").props.value userName = self.parent.parent.getChild("FlexContainer_2").getChild("Name_1").props.text comments = self.parent.parent.getChild("FlexContainer_2").getChild("Comments_1").props.…

[mdemaris]

If you are responding to my posts, no, I used the name of the procedure, with default DB. I should see if including the DB name would work - I assumed (!) the default is the one I'm working with.