Using message files to effect conversions of data of various types
into formatted text strings had been common on the AS/400 [and the
System/38; and *QTDCHAR was not new to AS/400].
As to how widely the technique is used for generating a SQL statement
string? No clue. But unlikely used beyond a smattering of cases is my
guess, because mostly the SQL attempts from CL probably would just be
replaced with call(s) to HLL SQL programs or utilities. Deferred to
programs with the desired embedded or dynamic SQL statement(s) coded,
where the variables already are a known SQLTYPE, there is no need for
the CL to delimit the strings. Or perhaps instead deferred to a *QMQRY
invoked by STRQMQRY using the SETVAR((&Q '''')...). Or even using the
REXX using EXECSQL /command/ environment which allows more creative
alternatives [e.g. INTERPRET]. Then, having already decided that the
SQL should be stored externally, how much benefit is there in using a
*MSGF over another external location to store those SQL statement(s).?
Over the releases and especially since the ILE, using messages to
format strings are often accomplished as easy if not easier using other
techniques. So having likely declined in usage generally, I doubt the
messaging technique was all that popular specific to the SQL. I almost
exclusively replaced my use of that technique for the SQL, with the REXX
SQL; REXX being especially nice because the delimiter for REXX is the
quotation mark, thus easily avoiding the requirement to escape the
apostrophes when building a SQL statement; e.g.:
Stmt="insert into qtemp/x values('A')"
One of my earliest RUNSQLSTM processors had used ADDMSGD and RTVMSGD
to effect variable replacement, accepting a large character string
parameter to provide the FMT() elements to define the variables. The
CLP that would invoke the command first had to build up a MSGDTA()
string, just as if preparing to send a message. However a message that
was dynamically defined, from the perspective of that CLP. For example,
the CLP might code:
CHGVAR %sst(&MSGDTA 01 10) VALUE('Some Value')
CHGVAR %sst(&MSGDTA 11 01) VALUE('X')
RUNSQLSTMT SQLSTMT('insert into qtemp/x values(&1, &2)') +
DCLVAR('(*QTDCHAR 10) (*QTDCHAR 1)')
Then in the CPP for the RUNSQLSTMT command, that program would
perform _effectively_ both of the following [with the final effect of
the example generating the SQL statement string "insert into qtemp/x
values('Some Value', 'X')" which then would be passed to the dynamic SQL
processor.
CHGVAR &CMDSTR VALUE( +
'ADDMSGD STM0001 SECLVL(' *TCAT &INPSQLSTMT *TCAT ')' +
*BCAT 'FMT(' *BCAT &INPDCLVAR *TCAT ')' /* etc. */ +
)
CALL QCMDEXC PARM(&CMDSTR &CMDSTRLEN)
RTVMSG MSGID(STM0001) MSGDTA(&INPMSGDTA) SECLVL(&SQLSTMT)
A later iteration of the same command\program had the CL variables
passed with the type information on a SETVAR() parameter, but using some
"For IBM-supplied commands" features of the command [CRTCMD; PARM, ELEM,
QUAL] definition objects.
But since the requirement to effect escaping of embedded apostrophe
is not available from the "quoted character" data type, even that type
*QTDCHAR is not very valuable generally. Having to accommodate for such
limitations often leads back to other /more appropriate/ ways to perform
the SQL requests than the CL. That was one reason I finally abandoned
using the message with message data, although I recall trying to use the
command prompter interface to perform the escaping for me, before I
completely abandoned that approach.
An eventual variation of that same command\program for which *MSGF
was no longer being used, the variables were able to be dynamically
named and each replaced using the SQL REPLACE() scalar. That removed
the limitation for use of only automatic FLD() naming [i.e. naming:
"&nbr"] of the message data elements\variables, plus handled the
escaping of apostrophes which was performed in an external scalar UDF.
For example, the RUNSQLSTMT shown in:
http://archive.midrange.com/midrange-l/201203/msg01172.html
Regards, Chuck
On 02-Apr-2012 08:49 , Stone, Joel wrote:
That looks pretty good - is this method widely used in the /400
community for building SQL command strings?
On Monday, April 02, 2012 10:45 AM CRPence wrote:
If using that technique, surely the use of *QTDCHAR data type for
the message data fields representing quoted character string
literals would be better, to also avoid the escaped apostrophes in
the ADDMSGD?
ADDMSGD
...
SECLVL('UPDATE TESTFILE
SET LG_ERRMSG=&1, LG_ERPATH=&2 ...
FMT((*QTDCHAR 7) (*QTDCHAR 10) ...
midrange.com
