Calling a Stored Procedure From a Report Issue

Ignition
1

I have this Stored Procedure:

USE [IgnMESCustomDB]
GO

DECLARE	@return_value int,
   	@ReturnCode int,
   	@ReturnMessage varchar(max)

EXEC	@return_value = [dbo].[splocal_AFL_Schedule_GetDisplay]
   	@EquipLineName = N'BL07',
   	@ReturnCode = @ReturnCode OUTPUT,
   	@ReturnMessage = @ReturnMessage OUTPUT

SELECT	@ReturnCode as N'@ReturnCode',
   	@ReturnMessage as N'@ReturnMessage'

SELECT	'Return Value' = @return_value

GO

I am trying to call it in from a report. But, I am getting nowhere! I am not seeing any columns listed in the data source of my query. Can anyone see what I am doing wrong? It is supposed to be a simple call. I spent over two hours scratching my head -

image
image1258×549 99.5 KB

Here is my code for calling the Stored Proc:

call = system.db.createSProcCall("splocal_AFL_Schedule_GetDisplay")
call.registerInParam("EquipLineName", system.db.VARCHAR, "BL07")
call.registerInParam("ReturnCode", system.db.INTEGER,0)
call.registerOutParam("ReturnMessage", system.db.INTEGER)

system.db.execSProcCall(call)

dataSource = call.getResultSet()<a class="attachment" href="//cdck-file-uploads-us1.s3.dualstack.us-west-2.amazonaws.com/inductiveautomation/original/2X/9/93ef7546b0612a2c4208eb030222b3f9c32f09a5.doc">Calling Stored Procedure From a Report.doc</a> (204.5 KB)

Thanks.

2

You are trying to use scripting where Ignition is expecting a SQL Query. Use a scripting data source.

3

That was silly on my part. I corrected it by using the script but I am not getting the data keys. ANy idea if I have issue with my syntax?

image
image880×441 24.6 KB

4

Here is my procedure which does work when I run it in the SQL server:

image

5

Your syntax looks good. You should see data under “mykey” in the preview pane.

6

I am seeing nothing:

image
image842×282 11.7 KB

7

It’s very frustrating:frowning: (

8

I am getting this error:

WARN: Error invoking script.Traceback (most recent call last):
File “function:updateData”, line 5, in updateData
TypeError: registerReturnParam(): expected 1 args; got 2

9

You are mixing up return parameters and out parameters.

1 Like