Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2004-04-12
6
Medium Priority
?
1,582 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
0
Comment
Question by:1wadd
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
6 Comments
 

Expert Comment

by:sixeyed
ID: 10815313
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.
0
 

Author Comment

by:1wadd
ID: 10818643
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...
0
 
LVL 1

Accepted Solution

by:
andrew348 earned 2000 total points
ID: 10830892
Hi,

this is a common issue when DB2 cannot convert parameters.
As far as I can remember DB2 counts parameters started from 1, than it is 'Album' value.
SP expects CHAR parameters, but withing your CALL they are probably treated as VARCHAR, and it seems that the problem is here.
If it is possible recreate the SP with VARCHAR instead of CHAR parameters.

Regards.
0
 

Author Comment

by:1wadd
ID: 10865017
Hi Andrea,

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

1wadd
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

722 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question