|
Yes, I am aware of the parameter matching as well as overloading. In this case there are no parameters involved.
Mitch
________________________________
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> on behalf of
Peter Dow<petercdow@xxxxxxxxx>
Sent: Tuesday, November 3, 2020 3:48:05 PM
To: midrange-l@xxxxxxxxxxxxxxxxxx<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] [SUSPECTED SPAM] Re: SQL stored procedure calling
another stored procedure with sqlstate 42704 not found
Hi Mitch,
The other thing that can cause a not found error is parameter matching
failure. That is, not only does the procedure have to exist, but the
parameter(s) you pass to it must match in number and type what the
procedure expects.
--
*Peter Dow* /
Dow Software Services, Inc.
909 793-9050
petercdow@xxxxxxxxx<mailto:petercdow@xxxxxxxxx>
pdow@xxxxxxxxxxxxxx<mailto:pdow@xxxxxxxxxxxxxx> /
On 11/3/2020 12:26 PM, Mitch Gallman wrote:
Thanks for the feedback Rob and Birgitta.
I've changed my procedures to have the MODIFIES SQL DATA clause and I've recreated them with naming(*SQL) and DFTRDBCOL(TEST010818).
In my Run SQL session setting both schema& path.
If I call unqualified or qualified I still get the same message about a failure on the call to the second procedure.
[ 11/03/2020, 02:56:04 PM ] Run Selected...
call pricegrid_export()
SQL State: 42704
Vendor Code: -204
Message: [SQL0204] PRICEGRID_REFRESH in TEST010818 type *N not found. Cause . . . . . : PRICEGRID_REFRESH in TEST010818 type *N was not found. If the member name is *ALL, the table is not partitioned. If this is an ALTER TABLE statement and the type is *N, a constraint or partition was not found. If this is not an ALTER TABLE statement and the type is *N, a function, procedure, trigger or sequence object was not found. If a function was not found, PRICEGRID_REFRESH is the service program that contains the function. The function will not be found unless the external name and usage name match exactly. Examine the job log for a message that gives more details on which function name is being searched for and the name that did not match. Recovery . . . : Change the name and try the request again. If the object is a node group, ensure that the DB2 Multisystem product is installed on your system and create a nodegroup with the CRTNODGRP CL command. If an external function was not found, be sure that the case of the EXTERNAL NAME on the CREATE FUNCTION statement exactly matches the case of the name exported by the service program.
Changing it to be qualified on the primary call has the same result.
Mitch
-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Rob Berendt
Sent: Tuesday, November 3, 2020 1:26 PM
To: Midrange Systems Technical
Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: SQL stored procedure calling another stored
procedure with sqlstate 42704 not found
You're right and I did correct myself.
It seems to me that only certain interfaces respect SET PATH. For example if I do a Values current path In Run SQL scripts and in STRSQL they both show the library list.
However if I use an unqualified name in run SQL scripts it finds the file but STRSQL does not.
Both are using *SQL naming convention.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Birgitta Hauser
Sent: Tuesday, November 3, 2020 1:18 PM
To: 'Midrange Systems Technical
Discussion'<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL stored procedure calling another stored procedure
with sqlstate 42704 not found
CAUTION: This email originated from outside of the organization. Do not click links or open attachments unless you recognize the sender and know the content is safe.
With SET SCHEMA the current schema is set when using SQL Naming Conventions.
But the current schema is only searched for Tables, Views and Indexes.
Stored Procedure and user defined functions are searched in the SQL PATH.
So the SQL PATH must be set correctly for an unqualified call.
I suspect more, that your stored procedures are generated with READS
SQL DATA, but if you perform a REFRESH, you need to create BOTH
procedure with MODIFIES SQL DATA
Mit freundlichen Grüßen / Best regards
Birgitta Hauser
"Shoot for the moon, even if you miss, you'll land among the stars."
(Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)
"What is worse than training your staff and losing them? Not training
them and keeping them!"
"Train people well enough so they can leave, treat them well enough
so they don't want to." (Richard Branson)
-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Rob Berendt
Sent: Dienstag, 3. November 2020 19:00
To: Midrange Systems Technical
Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: SQL stored procedure calling another stored procedure
with sqlstate 42704 not found
The big clue is qualified vs unqualified.
Instead of trying
Call test010818.pricegrid_refresh()
If you were to try
Call pricegrid_refresh()
Would it work? Probably not. I suspect that
test010818.pricegrid_export(); does not do a qualified call to
pricegrid_refresh
See also
SET SCHEMA
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600
to: 7310 Innovation Blvd, Suite 104
Ft. Wayne, IN 46818
Ship to: 7310 Innovation Blvd, Dock 9C
Ft. Wayne, IN 46818
http://www.dekko.com
-----Original Message-----
From: MIDRANGE-L<midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of
Mitch Gallman
Sent: Tuesday, November 3, 2020 12:17 PM
To: Midrange Systems Technical
Discussion<midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: SQL stored procedure calling another stored procedure with
sqlstate
42704 not found
CAUTION: This email originated from outside of the organization. Do
not click links or open attachments unless you recognize the sender
and know the content is safe.
I have a SQL stored procedure calling another SQL stored procedure.
In the main procedure pricegrid_export it does a CALL pricegrid_refresh().
In Run SQL Scripts I've done the following...
Set schema = TEST010818;
Set path = TEST010818;
Call test010818.pricegrid_export();
The call fails with SQL State 42704 PRICEGRID_REFRESH in TEST010818
type *N not found.
If I do the following it works...
Call test010818.pricegrid_refresh()
Why is my stored procedure having a problem finding another stored
procedure in the same location?
Thanks.
Mitch Gallman
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
