SQL Database Integration

Re: Get OUTPUT parameter from Stored Procedure

brandt — Tue, 07 Mar 2006 19:35:13 GMT

OK..here is what I had to do:
1) create a sql table to hold my value that I want - used for this sp ONLY!
2) change the sp to do the calcs...truncate the tbl that has my value...insert the value into the tbl...select the value
3) create a 2ndairy ds to 'xml mate'with my new tbl
4) issue the sp query from the 2ndairy ds adapter
5) grab the data from the 2ndairy ds and post to the form fields/main ds

Re: Get OUTPUT parameter from Stored Procedure

brandt — Tue, 07 Mar 2006 13:40:07 GMT

Lets look at this differently...I can construct the sp to select the output parameter-therby giving me the result I want in the return set as opposed to the output parameter...I've tried that with the following code:

function btnReproc::OnClick(eventObj)
{
// Write your code here
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var sPiece = getNodeValue("/dfs:myFields/dfs:dataFields/d:tblRAHeader/d:tblRADetail/@Piece");
XDocument.UI.Alert(sPiece);

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.

var strSQL = 'execute "prGetPieceRPCost" sPiece';
XDocument.UI.Alert(strSQL);

XDocument.QueryAdapter.Command = strSQL;

//Query the Data Source.
XDocument.Query();
XDocument.UI.Alert("sp query completed");
}

and get this error:

InfoPath cannot run the specified query.
The XML data returned from the data source does not match the XML Schema for this form. This discrepancy might be caused by the data source returning multiple rows when InfoPath expects only one.
Element 'http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}row' is unexpected according to content model of parent element 'http://schemas.microsoft.com/office/infopathe/2003/dataFormSolution}dataFields'.
Expecting: http://schemas.microsoft.com/office/infopath/2003/ado/dataFields}tblRAHeader.

The tblRAHeader is part of the main data source.
I guess the ??? now is which adapter to use (and how) to retrieve the return set? Or do I need to try to make the sp return set appear like the main data source return sset...is that possible? Here is the sp:

ALTER PROCEDURE dbo.prGetPieceRPCost (
@Piece char(7)
)
AS

declare @ret int, @RPCost int, @Cost Int

set @Cost = (select ReturnsFixedCostPerPiece from vwReturnsFixedCostPerPiece)

exec @ret = prCalcRPVarCost @Piece, @RPCost output

set @Cost = @Cost + @RPCost
select @Cost as ReProccessedDollars
return

the sp runs fine and returns 17 as the ReProcessedDollars...which I (hoped to ) matched to one of the fields in my main data source \d:tblRAHeader\d:tblRADetail\@ReProcessedDollars..which is also a form field

Re: Get OUTPUT parameter from Stored Procedure

Matt Faus — Mon, 06 Mar 2006 20:07:02 GMT

Hi Brandt,

I'm not sure what is causing that error, but I think it has something to do with not having the proper libraries installed. Like I said, it worked for me so maybe it was packed with my installation of Visual Studio. Maybe someone else has had the same problem, I would consult Google.

Re: Get OUTPUT parameter from Stored Procedure

brandt — Mon, 06 Mar 2006 19:04:55 GMT

Matt-I dnld & instlled the MDSCSDK2.8 and now I get

"Automation server can't create onject"

Re: Get OUTPUT parameter from Stored Procedure

Matt Faus — Mon, 06 Mar 2006 14:23:21 GMT

Sounds like you don't have something installed. I'm not sure what your development environment is, but the ADO stuff installed itself for me.

Try downloading and installing this:
http://www.microsoft.com/downloads/details.aspx?familyid=5067faf8-0db4-429a-b502-de4329c8c850&displaylang=en

Re: Get OUTPUT parameter from Stored Procedure

brandt — Mon, 06 Mar 2006 14:00:10 GMT

Hi Matt...I get the following error on:
var oCommand = new ActiveXObject("ADODB.Command");

"Microsoft JScript runtime error: Automation server cam't create object"

tnx

Re: Get OUTPUT parameter from Stored Procedure

Matt Faus — Mon, 06 Mar 2006 01:56:44 GMT

Hi Brandt,

In exploring this issue I used the AdventureWorks database that comes as an example with most installations of SQL. I called the following stored procedure to retrieve two output parameters:

USE AdventureWorks;
GO
IF OBJECT_ID ( 'Production.usp_GetList', 'P' ) IS NOT NULL 
    DROP PROCEDURE Production.usp_GetList;
GO
CREATE PROCEDURE Production.usp_GetList 
	@product varchar(40) -- Index 1 in Command.Parameters collection
    , @maxprice money -- Index 2
    , @compareprice money OUTPUT -- Index 3, Note that this simply roundtrips the @maxprice variables
    , @listprice money OUTPUT -- Index 4
AS
    SELECT p.name AS Product, p.ListPrice AS 'List Price'
    FROM Production.Product p
    JOIN Production.ProductSubcategory s 
      ON p.ProductSubcategoryID = s.ProductSubcategoryID
    WHERE s.name LIKE @product AND p.ListPrice < @maxprice;
-- Populate the output variable @listprice.
SET @listprice = (SELECT MAX(p.ListPrice)
        FROM Production.Product p
        JOIN  Production.ProductSubcategory s 
          ON p.ProductSubcategoryID = s.ProductSubcategoryID
        WHERE s.name LIKE @product AND p.ListPrice < @maxprice);
-- Populate the output variable @compareprice.
SET @compareprice = @maxprice;
GO

This is the JScript code that I used to call the stored procedure and then retrieve the two output parameters:

// Create the command object	 
var oCommand = new ActiveXObject("ADODB.Command");

// Set the connection to be the same as the main DOM
oCommand.ActiveConnection = XDocument.QueryAdapter.Connection;

// Set the command type to adCmdStoredProc
// Ref:http://msdn.microsoft.com/library/default.asp?url=/library/en-us/ado270/htm/mdcstcommandtypeenum.asp
oCommand.CommandType = 4;

// Name of the stored procedure
oCommand.CommandText = "Production.usp_GetList" ;

// Set the values of the parameters
// Start at index 1, because index 0 is @RETURN_VALUE
oCommand.Parameters(1) = "%Bikes%";
oCommand.Parameters.Item(2) = 700;

// Execute the stored procedure
oCommand.Execute();	

// Retrieve the output parameters	
XDocument.UI.Alert( oCommand.Parameters(3) + "      " + oCommand.Parameters(4) );

Hopefully from here you should be able to modify to work in your solution. If you have any questions, let me know! :)

Re: Get OUTPUT parameter from Stored Procedure

brandt — Sun, 05 Mar 2006 14:17:29 GMT

many thnx!

Re: Get OUTPUT parameter from Stored Procedure

Patrick Halstead — Sat, 04 Mar 2006 23:19:25 GMT

Hi Brandt,
We have written a lot of code to interface with DBs using ADO.NET. I will ask one of our developers to respond to you this weekend.

Re: Get OUTPUT parameter from Stored Procedure

brandt — Sat, 04 Mar 2006 21:30:08 GMT

I have a sp that has one input parameter (Piece char(7)) and one output
parameter (Cost )...the sp works fine but I can't figure out how to:
a) get the sp to run in ip
b) I really need the OUTPUT parameter

sp: PROCEDURE dbo.prGetPieceReprocCost (
@Piece char(7),
@Cost char(7) null output
)

Pretty complex sp to calc the cost.
Both Piece & Cost are in the main data source.

My code so far is:
function btnReproc::OnClick(eventObj)
{
//Retrieve the value of the field that you want to use as a parameter
//to the stored procedure.
var sPiece =
getNodeValue("/dfs:myFields/dfs:dataFields/d:tblRAHeader/d:tblRADetail/@Piece");
XDocument.UI.Alert(sPiece);

//Set the Command for the Query Adapter of the Data Source. Incorporate the
//parameter values that you want to use.
var retCost = 0
var strSQL = 'execute "prGetPieceReprocCost" ' + '"' + sPiece + '"' + '
,retCost output';
XDocument.UI.Alert(strSQL);

XDocument.QueryAdapter.Command = strSQL;

//Query the Data Source.
XDocument.Query();

and I get : Cannot use the OUTPUT option when passing a constant to a stored
procedure
_______________________________________________________

Patrick-after more research I understand that this is not possinle w/o using ADO...I still need an example of the ADO code...
_____________________________________________________

Here is the response I got on mSDN:
The Command property of the QueryAdapter is a string, not an object, which is
why you are getting the error, since the OUTPUT parameter cannot be returned.
You will not be able to retrieve an output value returned by a stored
procedure by using this Command property. You need to use the Command object
of ADODB (seehttp://support.microsoft.com/?kbid=185125).

For JScript, you would have to instantiate a Command object like this:
var objCommand = new ActiveXObject("ADODB.Command");

Then set the ActiveConnection and fill the Parameters collection on the
Command object, call Execute on the Command object, close the connection,
refresh the Parameters collection, and then retrieve the value of the output
parameter.
---
___________________________________________________________________

I'm going to need a JScript example of this for sure...thanks
Brandt

Re: Get OUTPUT parameter from Stored Procedure

Patrick Halstead — Sat, 04 Mar 2006 21:16:37 GMT

Hi Brandt,
Can you please explain your scenario a bit more?

Get OUTPUT parameter from Stored Procedure

brandt — Sat, 04 Mar 2006 17:19:45 GMT

How do you get a/the output parameter from a sp using javascript?