× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



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 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 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
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives at
https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate link:
https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/midrange-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related questions.

Help support midrange.com by shopping at amazon.com with our affiliate link: https://amazon.midrange.com

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Follow-Ups:
Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.