asked on

Calling a Stored Procedure with Output Parameters

I have the following Stored Procedure on a client's AS/400 that I have created and that I am trying to access via an ASP page. My problem is that I am not sure how to properly format the command to retreive the output parameters of the stored procedure. Can someone help me format my ADO request properly so that I can get the appropriate output values? If you can help me ensure that the AS/400 procedure is correct, then that's great. If not, then any help on just ADO commands would be greatly appreciated!

------------------------------------- AS 400 Stored Procedure -------------------------------------

CREATE PROCEDURE SHSWEB.SP_CUSTPRICE (
IN CUSTNO DECIMAL(5, 0),
IN ITEM# CHAR(15),
IN QTY DECIMAL(10, 3),
INOUT BASE$ DECIMAL(15, 3),
INOUT SELL$ DECIMAL(15, 3),
INOUT NET$ DECIMAL(15, 3))
LANGUAGE RPG
SPECIFIC SHSWEB.SP_CUSTPRICE
NOT DETERMINISTIC
MODIFIES SQL DATA
CALLED ON NULL INPUT
EXTERNAL NAME 'SHSWEB/CUSTPRICE'
PARAMETER STYLE GENERAL

------------------------------------- VB6 ADO Request -------------------------------------

Dim objConn, objCmd
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open "Provider=IBMDA400;Data Source=192.168.11.1;User ID=johndoe;PASSWORD=password;"
Set objCmd = Server.CreateObject("ADODB.Command")
objCmd.ActiveConnection = objConn
objCmd.CommandText = "SHSWEB.CUSTPRICE"
With objCmd
.Parameters.Append .CreateParameter("CUSTNO", adDecimal, adParamInput, 5, 10200)"
.Parameters.Append .CreateParameter("ITEM#", adChar, adParamInput, 15, 'AL173')"
.Parameters.Append .CreateParameter("QTY", adDecimal, adParamInput, 10, 1)"
.Parameters.Append .CreateParameter("BASE$", adDecimal, adParamInputOutput, 15)"
.Parameters.Append .CreateParameter("SELL$", adDecimal, adParamInputOutput, 15)"
.Parameters.Append .CreateParameter("NET$", adDecimal, adParamInputOutput, 15)"
End With
objCmd.Execute
objConn.Close

ASKER CERTIFIED SOLUTION

AlexFM

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

AlexFM

I think you should add also this:

objCmd.CommandType = adCmdStoredProc

AlexFM

And the last problem I see: you don't initialize input/output parameters:

.Parameters.Append .CreateParameter("BASE$", adDecimal, adParamInputOutput, 15, n1)
.Parameters.Append .CreateParameter("SELL$", adDecimal, adParamInputOutput, 15, n2)
.Parameters.Append .CreateParameter("NET$", adDecimal, adParamInputOutput, 15, n3)

assuming that n1, n2, n3 are initial values of these parameters.
And remove " from the end of each .Parameters.Append line.

bmccleary

ASKER

Thanks Alex,
Do I need to specify the adDecimal precision and scale, and if so... how?

bmccleary

ASKER

Thanks Alex,
Do I need to specify the adDecimal precision and scale, and if so... how?

AlexFM

CreateParameter Method

Set parameter = command.CreateParameter (Name, Type, Direction, Size, Value)

There is no place for precision, only size.

SOLUTION

Anthony Perkins

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Anthony Perkins

Three other points:
1. Check your documentation, but I suspect DECIMAL(15, 3) has a size less than 15. If I had to guess I would say 8

2. You can query the output parameters directly as follows:
objCmd.Execute
Debug.Print objCmd.Parameters("BASE$").Value
Debug.Print objCmd.Parameters("SELL$").Value
Debug.Print objCmd.Parameters("NET$").Value
Set objCmd = Nothing
objConn.Close

3. If BASE$, SELL$ and NET$ are output only parameters, than there is no need to assign a value before executing the stored procedure.

bmccleary

ASKER

Found out that the problem was caused by a problem with the AS400 RPG program. Thanks for your help though on ensuring that I can do the output variables correctly!