So, after some mucking about I think I got somewhere. I'm not convinced the set path is doing anything at runtime in this scenario. However, it seems to affect the creation of the procedure.
I'm creating my stored procedures from source members from the IBMi command line using RUNSQLSTM with DFTRDBCOL & *SQL naming. My library list set to include the TEST010818.
After I created the procedure I jump into Run SQL Scripts...fresh connection...
1) Path is: "QSYS","QSYS2","SYSPROC","SYSIBMADM","GALLMANM"
CALL TEST010818.PRICEGRID_EXPORT() -- results in the error about not finding it in TEST010818
2) Path set to: "QSYS","QSYS2","SYSPROC","SYSIBMADM","TEST010818"
CALL TEST010818.PRICEGRID_EXPORT() -- results in the error about not finding it in TEST010818
What makes it work:
Option 1) Run either of these before calling the stored procedure...
CL:addlible TEST010818
CL:chgcurlib TEST010818
Option 2) Setting the library list on the Run SQL Scripts connection to TEST010818
Option 3) Modify my SQL Source member and adding the SET PATH prior to the 'Create or replace procedure'.
SET PATH = TEST010818;
CREATE OR REPLACE PROCEDURE PRICEGRID_ EXPORT()
LANGUAGE SQL
MODIFIES SQL DATA
BEGIN
....
Once I do any of the above the call works.
Mitch
-----Original Message-----
From: MIDRANGE-L <midrange-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Mitch Gallman
Sent: Tuesday, November 3, 2020 3:27 PM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxxxxxxxx>
Subject: [EXTERNAL] RE: SQL stored procedure calling another stored procedure with sqlstate 42704 not found
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
--
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
midrange.com
