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