Sure static SQL in composition with overriding tables still can be used or
creating an alias.
I could also imagine other scenarios with SQL (compositions of views,
indexes and global variables) even though I'd have to test them because I'm
not sure about performance.
What I meant is, the following is not possible:
Exec SQL Select ... into :HostDSFile
From :MyHostFile
.....;
I've seen it quite often and get asked why it won't work.
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!"
-----Ursprüngliche Nachricht-----
Von: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] Im Auftrag von CRPence
Gesendet: Saturday, 07.4 2012 00:04
An: midrange-l@xxxxxxxxxxxx
Betreff: Re: AW: Question on updating some OLD programs
On 05-Apr-2012 22:52 , Birgitta Hauser wrote:
<<SNIP>>
Use dynamic SQL to access whatever table/file/view you want.
Static SQL is not possible, because setting tables/files and views
with host variables is NOT allowed.
Suggesting that the use of "static SQL is not possible" in the context of
the wider discussion is too absolute IMO; too specific to the comment about
the use of host variables. The two quoted statements seem, generally,
similar to having suggested that the use of RPG is not possible without the
use of the EXTFILE and EXTDESC given the same situation. So hoping to be
more clear for anyone less acquainted with the SQL, for whomever might
improperly infer the "not possible" comment to be a more general implication
about what can [not] be accomplished with static SQL...
While not possible to use Host Variables to replace TABLE references in a
[DML] SQL statement, dynamic reference to TABLEs different than those named
in a static DML SQL statement *is still possible* to achieve; just not by
using Host Variables. The dynamic effect is easily accomplished using
overrides, or possibly with library list manipulation, or [IMO less
desirable] SQL ALIAS, in the same way that dealing with the RPG F-Spec can
be accomplished with overrides or library list manipulation without the use
of EXTFILE and EXTDESC.
For example, the following static SQL can be compiled and can produce the
desired results at run-time, without the TABLE name ONEROWONLY existing
anywhere on the system:
Exec SQL
select current_date, current_time
into :date1 , :time1
from ONEROWONLY
;
The compile of that embedded SQL statement can function without any
overrides [not merely per no dependency on any column names]. But for the
run-time, all that is required for the statement to be functional when no
TABLE named ONEROWONLY exists is to have previously effected a properly
scoped override of the label\name ONEROWONLY, to any database file name
capable of being accessed via the SQL and according to the requirements of
the statement; e.g. OVRDBF ONEROWONLY
TOFILE(SYSIBM/SYSDUMMY1) Instead of overrides, a prior SQL request to
CREATE ALIAS QTEMP/ONEROWONLY FOR SYSIBM/SYSDUMMY1 would have sufficed as
well, to allow the statement to run without errors; though a permanent ALIAS
that existed since before compile-time would be better IMO... since
overrides are dynamic without creation of an "object".
Regards, Chuck
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe, unsubscribe,
or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
