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:

Exec SQL
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".

Regards, Chuck

This thread ...


Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].