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.