Thanks Tommy, I thought about that, but for two things:
#1 The program is compiled with ACTGRP( *NEW ) and with parameter values that bind the service program to it that contains the procedure, and that service program is compiled with ACTGRP( *CALLER).
#2 Method #1 has worked with many other programs.
I'm puzzled as to what I did different in this program, if it was me.
For this particular program, using QCMDEXC in the code to run CHGCURLIB has worked without having to do overrides, and it's simpler, so I'll probably use that henceforth, or run the overrides with it. (I'm not sure I want to scope to the job, but I may do that or add an option to the procedure for that.
Thanks everybody! Enjoy the evening, the Friday, the weekend and the holiday!
Thanks
----- Original Message -----
From: "Tommy Holden" <Tommy.Holden@xxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, May 26, 2016 3:27:16 PM
Subject: RE: Results returned with interactive SQL, but not embedded
I believe your issue is a scoping issue on the OVRDBF using the code snippet. If you modified the OVRDBF in that proc if you added the OVRSCOPE(*JOB) to the OVRDBF it *should* in theory work as intended.
Thanks,
Tommy Holden
Please note new email address tommy.holden@xxxxxxxxx
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Alan
Sent: Thursday, May 26, 2016 2:01 PM
To: RPG programming on the IBM i (AS/400 and iSeries) <rpg400-l@xxxxxxxxxxxx>
Subject: Re: Results returned with interactive SQL, but not embedded
Oh well, such is life...
SET CURRENT SCHEMA did work interactively in Navigator.
It did not work for in the embedded RPGIV (or even the green-screen interactive).
So I used CHGCURLIB each time through to the next respective library and it is working, populating the output file.
So it works and I'll consider this thread solved, but remaining a bit puzzled....
Thanks for the help!
----- Original Message -----
From: "Alan" <cfuture@xxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, May 26, 2016 1:48:20 PM
Subject: Re: Results returned with interactive SQL, but not embedded
..and the overrides are done like this, using a subprocedure, in the bound service program that compiles with ACTGRP(*CALLER):
+===================================
UQCPUM_OVRDBF( 'REFNO' : DBLIB ) ;
+===================================
dcl-proc UQCPUM_OVRDBF export ;
dcl-pi UQCPUM_OVRDBF ind ;
fname char( 10 ) const ;
tolib char( 10 ) const ;
end-pi ;
dcl-s cmx varchar( 512 ) ;
dcl-s qname varchar( 21 ) ;
dcl-s xflag ind ;
//
qname = %trim( tolib ) + '/' + %trim( fname ) ; cmx = 'OVRDBF ' + fname + ' ' + qname ; xflag = excmd( cmx ) ; return xflag ; END-PROC ;
+===================================
----- Original Message -----
From: "CRPence" <crpbottle@xxxxxxxxx>
To: rpg400-l@xxxxxxxxxxxx
Sent: Thursday, May 26, 2016 12:58:45 PM
Subject: Re: Results returned with interactive SQL, but not embedded
On 26-May-2016 09:19 -0500, Alan wrote:
<<SNIP>>
I have a sequence of SQL statements that <<SNIP>>
Why not just offer the actual statements; both the iNav Run SQL
script, and the embedded code. IMO, the actual SQL is much easier to
understand [and to see possible errors], per the SQL being a much more
strictly structured language than English.
BUT! ?? When I run it embedded, I get nothing.
Seems a fup reply suggests that /nothing/ means sqlcode=100.
With the EXACT same statement and syntax, with two differences:
And the actual statements, as coded, would be much clearer. Per use
of :HV, the declaration of that variable from the HLL [and any DDL that
might be relevant] could also help clarify.
One, I’m running the interactive version over one database schema
(library) at a time, with no overrides, whereas the embedded version
runs an override first because this is going to run using the data
from a bunch of schemas.
And the actual OVRDBF, as coded [and any changed defaults noted, that
are assumed due to a custom environment], would be much clearer; to
correlate with the statement(s).
Two, Because of the above, I use the host-variable :Dtaset to
include the library name in the embedded run, but in the
one-library interactive version it’s CHAR(‘ABC’, 3) as Dtaset.
Addendum included here; from a fup reply to clarify the above:
|> My apologies, I misspoke. I should have said,
|> "I use the host-variable :Dtaset to include a library
|> prefix string as a column value in the embedded run,
|> but in the one-library interactive version
|> it’s CHAR(‘ABC’, 3) as Dtaset."
|> In other words, a column that is hard-coded when
|> run interactive, but it comes from a host variable
|> when run embedded.
While I think I understand what is alluded [an expression on the
column\expression-list of the SELECT is coded either as ":Dtaset as
Dtaset" or "CHAR('ABC', 3) as Dtaset", again, the actual statements
likely would elucidate; requires far less inference and imagination.
FWiW: The SQL CREATE VARIABLE can be used to enable the
non-embedded\dynamic interface(s) to specify a variable name, quite the
same as embedded does; using Dtaset as the variable name, just drop the
colon from the host-variable name in the dynamic version of the statement.
The overrides are done using a service program procedure. The
program is compiled with ACTGRP(*NEW), and the service program with
ACTGRP(*CALLER). So it runs in the same activation group, which is
the default for the OVRDBF command.
I’ve done this a lot, I don’t get what’s different now. Any ideas?
I find the SQL debug messages and a review of open files [wrkjob
option(*opnf)] and overrides [wrkjob option(*filovr)] at breakpoints can
be helpful [compare for each scenario; failing and functional], along
with review of the SQLSTATE and SQLCODE after each statement [if the
embedded code is quick&dirty so not yet doing error-checking].
With regard to overrides, I might have coded a CLLE module in a [the
same] service program with ACTGRP(*CALLER) to do Display Override
(DSPOVR), and call that as part of any logging; because as I recall, the
Display Job file-override information seems to be lacking when trying to
see activation-group-scoped overrides, and coding at the command-line
from debug to DSPOVR ACTGRP(whatever_*NEW_became) is a PITA.
midrange.com
