Re: easier SQL statements in CL

[CRPence <CRPbottle@xxxxxxxxx>]

  Oh!  And the Debug APIs could be used to get the values from the 
already-defined variable names; i.e. no need to define external named 
data areas or environment variables, since the CL variables would be 
directly available via debug.  The effective REWRITESQL CPP could start 
debug on the calling program to get the variable names that would be 
identified in the passed statement; e.g. in the CHGVAR below, passed as 
:CLVARNAME thus requiring the Retrieve request to obtain the value of 
'&CLVARNAME' to replace the part of the SQL statement string.  Of course 
that makes debugging the CLP a bit more problematic, or at least making 
the rewrite processor deal with debug already active; and even 
preventing the rewrite processor from functioning whenever service of 
that job is active from another job.

Retrieve Program Variable (QTERTVPV) API
http://publib.boulder.ibm.com/infocenter/iseries/v5r4/topic/apis/QTERTVPV.htm

Regards, Chuck

On 11 May 2012 16:10, CRPence wrote:
> <<SNIP>>
> If the command being used does not provide such support, then create
> an interface that enables something similar. It does not have to be
> a RUNSQL command itself nor even implement a SETVAR parameter which
> is a bit complex; and when I did, used IBM-only command parameter
> features. It could use the effective DS capability of CL variables
> where each variable includes definitional attributes preceding its
> data. Or it could use something like data area(s) or environment
> variables to define both the variable name and its value from the CL
> variable, then invoke a command that returns the updated statement.
> For example:
>
> <code>
>
>       CHGVAR &SQLCMD VALUE('                    +
>            /* ---------- nice SQL ---------- */  +
>         INSERT INTO LGFILE                       +
>          ( LG_CLIENT, LG_SEQ, LG_DATE,  LG_TIME  +
>          , LG_DIR,    LG_ID,  LG_MODE,  LG_NRECS +
>          )                                       +
>         VALUES                                   +
>          ( :P_CLIENT, :P_SEQ,                    +
>       /* ( ":P_CLIENT", :P_SEQ,     */           +
>          , :CUR_YYYYMD, :CUR_HHMMSS              +
>          , :P_DIR, :P_ID, :MODE, :P_NRECS        +
>          )                                       +
>            /* ------------------------------ */  +
>                             ')
>
>       ADDENVVAR ENVVAR(P_CLIENT) VALUE(&P_CLIENT)
>       ADDENVVAR ENVVAR(P_SEQ) VALUE(&P_SEQ)
>       ...
>       ADDENVVAR ENVVAR(P_NRECS) VALUE(&P_NRECS)
>       REWRITESQL INPSTMT(&SQLCMD) RTNSTMT(&NEWSQLSTMT)
>       /* The CPP of REWRITESQL replaces each :EnvVar with the */
>       /* VALUE() from the environment variable named EnvVar   */
>       /* A naming convention or something else could define   */
>       /* rules for what to delimit; e.g. ":EnvVar" could be   */
>       /* replaced by ''ValueOfEnvVar'', even escaping of the  */
>       /* apostrophes, so a P_CLIENT="O'Connor" correctly gets */
>       /* written into the overall SQL statement string as     */
>       /*  'INSERT ... VALUES( ''O''''Connor'', ...)'          */
>
>       RUNSQL &NEWSQLSTMT
>
> </code>