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.