We help IT Professionals succeed at work.

Attempt to call a UDB DB2 stored procedure with multiple parameters from an MS Access pass-through query

1wadd
1wadd asked
on
Medium Priority
1,697 Views
Last Modified: 2008-03-17
Hi,

I am attempting to call a UDB DB2 stored procedure with multiple parameters from an MS Access pass-through query.  When I call stored procedures with no parameters or with one parameter, records are returned, but not when I call an SP with more than one parameter.

Here is the call I am attempting to execute:
       {call EMDPROD.RTV_DISR_TTLSLS('JC1', 'Album',2004,'2003-11-01','2003-11-30')}
        In the SP, the parameters are defined as follows:
        - 1st parameter defined as char(03)
        - 2nd parameter defined as char(05)
        - 3rd parameter defined as smallint
        - 4th parameter defined as date
        - 5th parameter defined as date
When I run this pass-through query, I receive this error:
[IBM][CLI Driver][DB2/6000] SQL0171N  The data type, length or value of argument "2" of routine "RTV_DISR_TTLSLS" is incorrect.  SQLSTATE=42815
(#-171)

I am able to call this procedure using Crystal Reports and get records back.

I have been wrestling with this one for a while now.  This is a high priority for me, as we want to replace all of our cumbersome hardcoded SQL with SPs.  

Any assistance would be greatly appreciated.

Thanks,
1wadd
Comment
Watch Question

Commented:
Hi there,

I've had a similar problem with VBA in Excel, solved by building and executing the command like this:

'*****
'work vars:
Dim cmdSp As New ADODB.Command
Dim strTemp As String
Dim prms As Variant
Dim i As Integer

'set up cmd:
With cmdSp
    .ActiveConnection = cnn400 'ADODB.Connection - instantiated & opened
    .CommandType = adCmdText
    .CommandText = "{call lib.pgm (?,?,?,?,?,?)}"
    .Parameters.Refresh
    .Prepared = True
End With

prms = Array("1", 2, 3.0, "4", "5", "6")
cmdSp.Execute , prms

'display results:
strTemp = cmdDlr.Parameters(0).Value
For i = 1 To 5
    strTemp = Trim(strTemp) + ", " + cmdSp.Parameters(i).Value
Next i

MsgBox strTemp
'*****

- vagaries are that you have to pass the "prms" array (defined as Variant) as input parameters to the command, but you retrieve output values through its Parameters collection.

Hope it's useful.

Author

Commented:
Hi sixeyed,

Thanks for responding.  I have successfully called the SP using VBA and ADO.  

We were looking at setting up numerous SPs and allowing the business users to call them using MS Access pass-through queries.  Based on your response, and the responses I received in the MS Access technology channel, this is not a viable path if multiple parameters are involved.

Oh well, we'll have to look at other avenues.

Thanks...
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Hi Andrea,

We'll try that and let you know.  Thanks for responding.

1wadd
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.