On 05-Apr-2012 22:52 , Birgitta Hauser wrote:
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:
select current_date, current_time
into :date1 , :time1
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".