Re: easier SQL statements in CL

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>