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.text
	qtyTaken = self.parent.parent.getChild("FlexContainer_2").getChild("Taken_1").props.value
	transType = 1
	locId = self.view.custom.loc_id
	callsp = system.db.createSProcCall('usp_InsertSignOutTransactionTest01', tx, skipAudit)
	callsp.registerInParam(1, system.db.INTEGER, locid)
	callsp.registerInParam(2, system.db.INTEGER, transType)
	callsp.registerInParam(3, system.db.INTEGER, qtyTaken)
	callsp.registerInParam(4, system.db.VARCHAR, comments)
	callsp.registerInParam(5, system.db.INTEGER, qtyLeft)
	callsp.registerInParam(6, system.db.VARCHAR, userName)
	system.db.execSProcCall(callsp)

When run, the SP does not INSERT the data.

On the Named Query:
This is the Query:
CALL [usp_InsertSignOutTransactionTest01] (:p_locid, :transType, :quantity, :comments, :rptQty, :userName)

All numbers are Int8. All NOT NULL fields are assigned a value. I've tried using Query Type: Query, and Update.

The body of the SP:

	-- Add the parameters for the stored procedure here
	@locID INT, @typeID INT, @qty INT, @comments varchar(255), @reportedQty INT, @userName varchar(50)
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;

    -- Insert statements for procedure here
	INSERT INTO [inv].[Transaction] (LocID, TypeID, Quantity, Comments, ReportedQty, UserName)
	VALUES (@locID, @typeID, @qty, @comments, @reportedQty, @userName)
END

The parameters int the Named Query are in the same order as the SP parameters.

The Error, from testing the Named Query in the Designer:
com.microsoft.sqlserver.jdbc.SQLServerException: Incorrect syntax near '@P0'.

In the SSMS, this SP executes fine:


EXEC	@return_value = [inv].[usp_InsertSignOutTransactionTest01]
		@locID = 1,
		@typeID = 1,
		@qty = 1,
		@comments = N'none',
		@reportedQty = 1,
		@userName = N'mike'

SELECT	'Return Value' = @return_value

GO

Turns out, it helps to use the correct commands with SQL Server:
EXEC [inv].[usp_InsertSignOutTransactionTest01] :p_locid, :transType, :quantity, :comments, :rptQty, :userName

I had to play around with the schema and parentheses, before I got some errors other than syntax error.

Then, I found that granting the MSSQL User INSERT permissions does not cover calling SP, so I had to add the EXECUTE permission.

This solves the Named Query problem...

BUT, not the call from the button!

I ditched the Call to the SP in the button event, and used a call to the Named Query, which works fine.

However, I would like to understand why the system.db.execSProcCall(callsp) did not work.

Thanks

Was there any error anywhere; in the gateway logs or the designer logs or anything?

1 Like

Well, I'm glad you mentioned the gateway logs! I don't think I was really aware of that resource! After checking those logs, I found out why. My lack of knowledge, lol. I left the tx and skipaudit untouched, assuming I didn't need to do anything with them via Intellisense.

Calling the SP via the button script action works as intended, as does the SP through the Named Query.

1 Like