On 11 May 2012 12:52, Stone, Joel wrote:
<<SNIP>> Normally there are a bunch of variables in there, so it
would look more like the following:
Is it possible to copy this from a CL source member (in-line with
other CL commands) into a QTEMP member use RUNSQLSTM to run that?
CHGVAR &SQLCMD VALUE(' +
+
INSERT INTO LGFILE +
( LG_CLIENT, LG_SEQ, LG_DATE, LG_TIME +
, LG_DIR, LG_ID, LG_MODE, LG_NRECS +
) +
VALUES +
(' *tcat +
&QT *cat &P_CLIENT *cat &QT_C *cat +
&QT *cat &P_SEQ *cat &QT_C *cat +
&QT *cat &CUR_YYYYMD *cat &QT_C *cat +
&QT *cat &CUR_HHMMSS *cat &QT_C *cat +
&QT *cat &P_DIR *cat &QT_C *cat +
&QT *cat &P_ID *cat &QT_C *cat +
&QT *cat &MODE *cat &QT_C *cat +
&QT *cat &P_NRECS *cat &QT +
*tcat ') +
+
')
RUNSQL &SQLCMD
The CL is still going to have to somehow pass the values of the
variables. That is, there would be little difference between using
RUNSQL or RUNSQLSTM from the perspective of the CL. And while the
RUNSQLSTM could perform the CL requests, there is little error handling
capability. Again, using REXX with its "*COMMAND" and "*EXECSQL"
environments allows a nice integration of both CL and SQL, allowing for
error handling and coded logic\flow. If the desire is to use "SQL
statements in CL" then I suggest changing perspective, and embrace using
both CL and SQL in REXX.... and dump the CL and compiling, just
interpret the source. Easier than writing a CL interpreter with SQL
capabilities or a CL source pre-processor\pre-compiler to better
integrate SQL requests from CL. Anyhow...
IMO the CL command being used would resolve [at least ameliorate]
this issue for you; allowing specification of replacement variables for
values from the CL variables. 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, +
, :CUR_YYYYMD, :CUR_HHMMSS +
, :P_DIR, :P_ID, :MODE, :P_NRECS +
) +
/* --------- </nice SQL> --------- */ +
')
RUNSQL &SQLCMD SETVAR( +
(P_CLIENT &P_CLIENT) (P_SEQ &P_SEQ) +
(CUR_YYYYMD &CUR_YYYYMD) +
(CUR_HHMMSS &CUR_HHMMSS) +
(P_DIR &P_DIR) (P_ID &P_ID) +
(MODE &MODE) (P_NRECS &P_NRECS) +
)
</code>
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>
Regards, Chuck
midrange.com
