On 13 Dec 2012 18:50, Steve Richter wrote:
another place where the host variable restriction is a problem is
when creating an alias in order to access a member of a file. The
following code does not work:
exec sql
create alias qtemp/utl0040al for
qusrsys/qatmhinstc(:MBRNAME) ;
instead you have to prepare and execute.
The same concern is often expressed about the names of the SQL
objects being created by a SQL CREATE statement. Many people would like
embedded to be able to allow the following:
create alias :ALlib/ALname for :TBlib/:TBname(:MBname) ;
As a SQL user, I tend to agree. But I realize the function is not
available in embedded, and like with everything else, just deal with it
until the function becomes available. As a licensed or actual user of
the OS and SQL, or someone with any legitimate requirements rather than
some personal wish-list, I might submit a DCR or attempt to participate
in the standards. Given an open-source SQL was being used instead, one
could just code up their own solutions :-) ... though what I have seen
of other open software, most will just wait for someone else to provide
the features, just like they would wait for the feature from any other
software provider; and wait, and hope and wait....
Personally I would almost never waste the CPU cycles and permanent
addresses for using the SQL ALIAS capability to create [an object] in
QTEMP. Instead, choosing to use the OS Data Management capability of
overrides which merely adds an effective index entry to an existing
object addressed by the job, requested before the SQL; i.e.
OVRDBF utl0040al qusrsys/qatmhinstc mbr(&MBRNAME) ovrscope(...)
Of course the separate PREPARE and EXECUTE can be avoided with fully
dynamic statements:
IBM i 7.1 Information Center -> Database -> Reference -> SQL reference
-> Statements
_i EXECUTE IMMEDIATE i_
"EXECUTE IMMEDIATE combines the basic functions of the PREPARE and
EXECUTE statements. It can be used to prepare and execute SQL statements
that contain neither variables nor parameter markers. ..."
So for example:
create procedure crtTmpAlias
( IN ALname varchar(10)
, IN FORname varchar(21)
, IN MBname varchar(10)
) language sql
begin
declare stmt varchar(500) default '' ;
set stmt='create alias qtemp/' concat ALname
concat ' for ' concat FORname concat '('
concat MBname concat ')' ;
execute immediate stmt ;
end
/* invocation example; host variable is supported here: */
call crtTmpAlias ('utltest', 'qusrsys/qatmhinstc', :MBRNAME) ;
IBM i 7.1 Information Center -> Database -> Programming -> Embedded SQL
programming -> Common concepts and rules for using embedded SQL
_i Using host variables in SQL statements i_
http://publib.boulder.ibm.com/infocenter/iseries/v7r1m0/topic/rzajp/rzajphostvar.htm
"When your program retrieves data, the values are put into data items
that are defined by your program and that are specified with the INTO
clause of a SELECT INTO or FETCH statement. The data items are called
host variables.
A host variable is a field in your program that is specified in an SQL
statement, usually as the source or target for the value of a column.
The host variable and column must have compatible data types. Host
variables cannot be used to identify SQL objects, such as tables or
views, except in the DESCRIBE TABLE statement.
...
As a value in other clauses of an SQL statement:
* The SET clause in an UPDATE statement
* The VALUES clause in an INSERT statement
* The CALL statement
..."
midrange.com
