Re: File Overrides and STRSQL vs embedded SQL

On 26 Feb 2013 11:15, TheBorg wrote:

I issued the following command:
OVRDBF FILE(FAA0005) TOFILE(XXXXXXXXX/FAA00005)

The library XXXXXXXXXX is NOT in the library list for my interactive
job.

Now, using STRSQL to start the green-screen SQL interpreter, I can
do the following and get data:

Select * from FAA0005

However, If I try to run a QM query or a program which has embedded
SQL referencing FAA0005 *without* qualifying the file and library, I
get an SQL0204 message: "FAA0005 in *LIBL type *MEM not found."

Is it safe to conclude that QM Query and HLL programs containing
embedded SQL don't respect file overrides?

That error is consistent with file FAA0005 having no members.

If that is not the origin for the issue, then...

That is not a safe conclusion. The SQL honors an override to a database file [and member] by name. While there is a possibility that some non-SQL interface for which a name that would be passed to the SQL might be manifest [incorrectly] as a "not found" condition because that non-SQL interface attempted its own validation before invoking the SQL, that is not an issue for either of embedded SQL or QM queries. Some other overridden attributes [aside from TOFILE and MBR] are not supported however.

The QMQRY should not have issued that error for the given query; as explained below. There would either be a defect, or something about what was described is incorrect or lacking in some nuance\detail that is relevant but overlooked; e.g. as alluded first, the case of no members in the file.

Although the SQL honors an override, the override must be properly scoped for the SQL being requested. For the QMQRY, any of the overrides would work when requested from the Default Activation Group; i.e. any of the values *ACTGRPDFN, *CALLLVL, or *JOB for the Override Scope (OVRSCOPE) parameter would enable the SQL performed in the STRQMQRY request to /see/ the override. For the embedded SQL, the scoping is dependent upon both the ACTGRP of the xxxSQL program, and the ACTGRP in which the override is requested.