How to return an output parameter from an iCommand Query

[Former Member]

I need to return an output parameter from a stored procedure that is doing an insert. Ultimately this needs to be done in an xMII Logic Editor Transaction. Can this be done with a SQL Query in Command mode? If, so how?

[jcgood25 · Accepted solution]

If your SQL query returns a dataset then you can used FixedQuery mode. Command mode does not expect a resultset (just the db acknowledgement that it either succeeded or failed).

Regards,

Jeremy

[Former Member · Accepted solution]

The stored proc I am calling performs an update and returns an error code in an output parameter. It does not return a dataset. The system I am interfacing to uses stored procs w/output params for all functions. I believe I need to use iCommands because of the update. Here is the procedure and parameters.

CREATE PROCEDURE [dbo].[stub_auto_pman]

(

@i_src_loc_num char(10),

@i_lpn char(20),

@i_item_num char(30),

@i_qty numeric(10),

@o_ret_status numeric OUTPUT

)

Thanks

[jcgood25 · Accepted solution]

Looks like you want Command mode for your query - have you built and tested your SQLQuery template?

[Former Member · Accepted solution]

I have created the iCommand and successfully executed it, however I want to retreive data from the output parameter and don't know how to do that.

I can call the SP with an iCommand as:

EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=Param.5

But I cannot retreive the value from the @o_ret_status parameter.

I tried to capture the output parameter to a variable and return it:

DECLARE @ret_status as numeric

EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=@ret_status

SELECT @ret_status

But I get the following error:

Fatal Error

A SQL Error has occurred on query, TDS DriverA ResultSet was produced by 'DECLARE @ret_status as numeric EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='a', @i_lpn='b', @i_item_num='c', @i_qty=4, @o_ret_status=@ret_status SELECT @ret_status'.

Any ideas?

Edited by: Michael Janssen on Mar 14, 2008 7:14 PM : Items in brackets were converted to links, removed brackets

[HariCS23 · Accepted solution]

HI,

I guess u can try this.

docuemt.<appletname>.getFirstValue("<Outputparameter name>");

The above statment worked for me when i encasulated the sql query template in the xACUTE query.and executed with i command. ( SQL query template is in fixed query mode).

Thanks

Hari

[Former Member · Accepted solution]

Hari,

Thanks for the idea, but can I do it completely in a xMII Business Logic transaction. I won't have a web page to use the javascript and DOM to get at the value. I just want a scheduled transaction reading one system calling a stored proc in another.

Thanks, I think I am getting close to the answer.

[jcgood25 · Accepted solution]

Your FatalError is telling you that a resultset is being produced, so you need to use FixedQuery in your SQLQuery template. This way you will get the result from your procedure.

In an SQLQuery template:

FixedQuery Mode expects results.

Command Mode does not expect results.

In both modes, the query request string is passed to the JDBC driver, but it all depends upon how it reacts to the results/response.

Regards,

Jeremy

[Former Member · Accepted solution]

Thanks Jeremy,

I was able to execute the following:

DECLARE @ret_status as numeric

EXEC dbo.xMII_stub_auto_pman @i_src_loc_num='Param.1', @i_lpn='Param.2', @i_item_num='Param.3', @i_qty=Param.4, @o_ret_status=@ret_status OUTPUT

SELECT @ret_status

in a FixedQuery and it INSERTED the data and returned the output parameter (as a dataset).

Edited by: Michael Janssen on Mar 14, 2008 7:16 PM : Items in brackets were converted to links, removed brackets