SQL Lookup with Returned Value (LOOKUPSQL)
From MidrangeWiki
Summary
The following are almost the complete definitions and instructions needed for using the SQL Lookup with Returned Value (LOOKUPSQL) command. This command performs an SQL lookup on behalf of the invoking CL/ILE programs with an up to 510-character result returned to a CL/ILE variable. This result is from a single row but can be either the value of a single column or multiple columns concatenated into a CL data structure. The only part of this code which will require your modifications is the SQL error checking—as this example uses our proprietary service procedures for this purpose.
I made a number of enhancements, but original credit for this idea goes to Michael Sansoterra, as published at: https://www.mcpressonline.com/programming/sql/using-dynamic-sql-in-cl-part-2retrieving-a-data-value on November 9, 2003.
By Dave Clark
Example Call
Note that this example call uses a CL-based data structure. The definition for the data structure is also shown.
DCL VAR(&APOST) TYPE(*CHAR) LEN(1) VALUE('''')
DCL VAR(&BLANK) TYPE(*CHAR) LEN(3) VALUE(''' ''')
DCL VAR(&VTXCFG) TYPE(*CHAR) LEN(510)
DCL VAR(&VTXURL) TYPE(*CHAR) LEN(60) STG(*DEFINED) DEFVAR(&VTXCFG 001)
DCL VAR(&VTXUSR) TYPE(*CHAR) LEN(25) STG(*DEFINED) DEFVAR(&VTXCFG 061)
DCL VAR(&VTXPWD) TYPE(*CHAR) LEN(25) STG(*DEFINED) DEFVAR(&VTXCFG 085)
DCL VAR(&VTXSHR) TYPE(*CHAR) LEN(20) STG(*DEFINED) DEFVAR(&VTXCFG 111)
DCL VAR(&VTXPTN) TYPE(*CHAR) LEN(20) STG(*DEFINED) DEFVAR(&VTXCFG 131)
LOOKUPSQL RTNVAL(&VTXCFG) +
COLUMN('CHAR(VALUE(C.URL,' || &BLANK || '),60) +
|| VALUE(C.USRNAM,' || &BLANK || ') +
|| VALUE(C.PW,' || &BLANK || ') +
|| VALUE(C.SBSCRBR,' || &BLANK || ') +
|| CHAR(VALUE(C.CNTTYPE,' || &BLANK || '),20)') +
TABLE('SHR460/HTTPCFG AS C') +
WHERE('C.APPLCTN = ' || &APOST || &APPLCTNKEY || &APOST +
|| ' AND C.ENVRMNT = ' || &APOST || &ENVRMNTKEY || &APOST) +
ORDER('C.APPLCTN ASC, C.ENVRMNT ASC')
Command Definition
Place the following in a QCMDSRC member named LOOKUPSQL and compile it.
/******************************************************************************/
/* Program Name: LOOKUPSQL -- SQL Lookup for CL/ILE with Returned Value */
/* Programmer: Dave L Clark I */
/* Date: February 16, 2018 */
/* Project/Request #: IN-53 Create a Command to Return an SQL Value in CL Pgm */
/* Purpose: This command allows a CL/ILE program to pass the parts */
/* of an SQL statement to an RPG/ILE program for the */
/* purpose of getting back what is logically a one-column */
/* value but can actually be multiple concatenated columns */
/* that were converted to character format, first. The CL */
/* program can then use %SST to split it apart and CHGVAR */
/* to convert character values back to decimal if needed. */
/* Note that, internally, only a single SQL row will be */
/* retrieved so the ORDER BY parameter could be important. */
/******************************************************************************/
/* */
/* Original credit for this idea goes to Michael Sansoterra, as published at: */
/* https://www.mcpressonline.com/programming/sql/... */
/* ...using-dynamic-sql-in-cl-part-2retrieving-a-data-value */
/* on November 9, 2003 */
/* */
/******************************************************************************/
/* Modification Log: */
/* */
/* Mod# Date User Description */
/* ---- ---------- ---------- ----------------------------------------------- */
/* M000 03/08/2018 DLCLARK Initial design. */
/******************************************************************************/
LOOKUPSQL: CMD PROMPT('SQL Lookup with Returned Value') +
TEXT('SQL Lookup for CL/ILE with Returned +
Value') ALLOW(*BMOD *IMOD) +
HLPID(LOOKUPSQL) HLPPNLGRP(*LIBL/LOOKUPSQL)
PARM KWD(RTNVAL) TYPE(*CHAR) LEN(510) +
RTNVAL(*YES) MIN(1) +
PROMPT('Return value (Char 510)')
PARM KWD(COLUMN) TYPE(*CHAR) LEN(510) MIN(1) +
EXPR(*YES) VARY(*YES *INT2) CASE(*MIXED) +
INLPMTLEN(132) PROMPT('Retrieve column or +
expression')
PARM KWD(TABLE) TYPE(*CHAR) LEN(510) MIN(1) +
EXPR(*YES) VARY(*YES *INT2) CASE(*MIXED) +
INLPMTLEN(132) PROMPT('Lookup table or +
expression')
PARM KWD(WHERE) TYPE(*CHAR) LEN(510) DFT(*NONE) +
SPCVAL((*NONE ' ')) EXPR(*YES) VARY(*YES +
*INT2) CASE(*MIXED) INLPMTLEN(132) +
PROMPT('SQL WHERE clause')
PARM KWD(ORDER) TYPE(*CHAR) LEN(510) DFT(*NONE) +
SPCVAL((*NONE ' ')) EXPR(*YES) VARY(*YES +
*INT2) CASE(*MIXED) INLPMTLEN(132) +
PROMPT('SQL ORDER BY clause')
SQL RPG/ILE Program
Place the following in an QRPGLESRC member named LOOKUPSQL (type SQLRPGLE) and compile it. Note that the SQL error checking must be replaced with your own shop standards for such coding.
**free
//==============================================================================
// Program: LOOKUPSQL -- SQL Lookup for CL/ILE with Returned Value
// Programmer: Dave L Clark I
// Date: February 16, 2018
// Project/Request #: IN-53 Create a Command to Return an SQL Value in CL Program
// Purpose: This command allows a CL/ILE program to pass the parts
// of an SQL statement to an RPG/ILE program for the
// purpose of getting back what is logically a one-column
// value but can actually be multiple concatenated columns
// that were converted to character format, first. The CL
// program can then use %SST to split it apart and CHGVAR
// to convert character values back to decimal if needed.
// Note that, internally, only a single SQL row will be
// retrieved so the ORDER BY parameter could be important.
//------------------------------------------------------------------------------
// Special Programming Logic / Considerations
//
// NONE
//
//------------------------------------------------------------------------------
// Modification Log
//
// Mod# Date User Description
// ---- ---------- ---------- --------------------------------------------------
// M000 03/08/2018 DLCLARK Initial design.
//==============================================================================
ctl-opt MAIN(LOOKUPSQL) AlwNull(*UsrCtl) DatFmt(*ISO) TimFmt(*ISO)
DftActGrp(*No) ActGrp(*Caller) Debug Option(*SrcStmt:*NoDebugIo);
// general copybooks
/include *libl/qrpglesrc,utsqlutsv // SQL utilities service program
//==============================================================================
// Enter all global data definitions before this point
//==============================================================================
exec sql set option ALWBLK = *ALLREAD, CLOSQLCSR = *ENDACTGRP,
COMMIT = *NONE, DATFMT = *ISO,
EXTIND = *YES, LANGID = ENU,
NAMING = *SYS, SRTSEQ = *HEX,
USRPRF = *USER, DYNUSRPRF = *USER;
//==============================================================================
// Begin main procedure definition
//==============================================================================
dcl-proc LOOKUPSQL;
dcl-pi *n extpgm;
pReturnedValue char(510);
pColumnExpression varchar(510);
pTableExpression varchar(510);
pWhereClause varchar(510);
pOrderByClause varchar(510);
end-pi;
dcl-c loc_info 'LOOKUPSQL_Main_Procedure';
dcl-s sql_ind like(NULL_VALUE);
dcl-s sqlstmt varchar(3072);
sqlstmt = 'Values(';
sqlstmt += 'Select Cast(' + pColumnExpression
+ ' as Char(' + %char(%len(pReturnedValue))
+ '))';
sqlstmt += ' from ' + pTableExpression;
pWhereClause = %trim(pWhereClause);
if %len(pWhereClause) > *zero;
sqlstmt += ' where ' + pWhereClause;
endif;
pOrderByClause = %trim(pOrderByClause);
if %len(pOrderByClause) > *zero;
sqlstmt += ' order by ' + pOrderByClause;
endif;
sqlstmt += ' ' + FETCH_FIRST + FETCH_ROW_ONLY;
sqlstmt += ') into ?';
exec sql prepare LOOKUPSQL_Statement
attributes :FOR_READ_ONLY
from :sqlstmt;
if not SqlUtl_SqlCompleted(sqlstate); // did prepare fail?
GenUtl_MsgInfo = SqlUtl_LogSqlFailure( Sql_Prepare_Error: '*ESCAPE'
: sqlca: loc_info: sqlstmt );
endif;
exec sql execute LOOKUPSQL_Statement
using :pReturnedValue :sql_ind;
if not SqlUtl_SqlCompleted(sqlstate); // did execute fail?
select;
when SqlUtl_SqlWarning(sqlstate);
callp GenUtl_Escape('ACL0006': 'SQLWRN');
when SqlUtl_SqlNoData(sqlstate);
callp GenUtl_Escape('ACL0006': 'SQLNTF');
other;
GenUtl_MsgInfo = SqlUtl_LogSqlFailure( Sql_Execute_Error: '*ESCAPE'
: sqlca: loc_info: sqlstmt );
endsl;
endif;
if sql_ind = NULL_VALUE; // if null
pReturnedValue = *blanks; // return blanks
endif;
return; // return to caller
end-proc;
HELP Panel Group
Place the following in a QPNLSRC member named LOOKUPSQL and compile it.
.******************************************************************************/
.* Program Name: LOOKUPSQL -- SQL Lookup for CL/ILE with Returned Value */
.* Programmer: Dave L Clark I */
.* Date: February 16, 2018 */
.* Project/Request #: IN-53 Create a Command to Return an SQL Value in CL Pgm */
.* Purpose: This command allows a CL/ILE program to pass the parts */
.* of an SQL statement to an RPG/ILE program for the */
.* purpose of getting back what is logically a one-column */
.* value but can actually be multiple concatenated columns */
.* that were converted to character format, first. The CL */
.* program can then use %SST to split it apart and CHGVAR */
.* to convert character values back to decimal if needed. */
.* Note that, internally, only a single SQL row will be */
.* retrieved so the ORDER BY parameter could be important. */
.******************************************************************************/
.* Modification Log: */
.* */
.* Mod# Date User Description */
.* ---- ---------- ---------- ----------------------------------------------- */
.* M000 03/08/2018 DLCLARK Initial design. */
.******************************************************************************/
:pnlgrp.
.************************************************************************
.* Help for command LOOKUPSQL
.************************************************************************
:help name='LOOKUPSQL'.
SQL Lookup with Returned Value - Help
:p.The SQL Lookup with Returned Value (LOOKUPSQL) command provides a means,
in a CL program, of obtaining a returned value from the execution of an SQL
statement. Sure, you could write your own one-off RPG program and call it
to accomplish the same thing, but this command packages a generic means of
obtaining almost any value via SQL that you might imagine -- and you didn't
have to write it yourself. You just have to use a little imagination when
providing the parameters to this command.
:p.:hp2.Restrictions::ehp2.
:ul.
:li.
Must be executed from a CL/ILE program. It cannot be executed from a command
line or from an RPG program because a CL return variable is required for the
returned value.
:eul.
:ehelp.
.*******************************************
.* Help for parameter RTNVAL
.*******************************************
:help name='LOOKUPSQL/RTNVAL'.
Return value (Char 510) (RTNVAL) - Help
:xh3.Return value (Char 510) (RTNVAL)
:p.Specifies the name of a character variable to receive
the returned value from your SQL statement that you provide.
:p.This is a required parameter.
:note.If the returned value will be a concatenation of more than one SQL
column, then the return variable should represent the equivalent of an
RPG/ILE data structure to make it easier to obtain the separate
values. The CL/ILE DCL command does support the STG(*DEFINED)
parameter for this purpose.
:ent.
:ehelp.
.*******************************************
.* Help for parameter COLUMN
.*******************************************
:help name='LOOKUPSQL/COLUMN'.
Retrieve column or expression (COLUMN) - Help
:xh3.Retrieve column or expression (COLUMN)
:p.Specifies a string expression of up to 510 characters that is used
to form the SQL column list in a SELECT statement. However, only a
single column may be described -- either as a single function or column name;
or as a complex string expression (i.e., using concatenation of two or more
columns together). A little imagination may be required when concatenating
two or more columns together because the resulting value must be a character
string and the calling CL program must be able to parse out the concatenated
values.
:p.This is a required parameter.
:note.If you need to expand the input field to accept more data, type
an ampersand, a space, and then press the ENTER key.
:ent.
:ehelp.
.*******************************************
.* Help for parameter TABLE
.*******************************************
:help name='LOOKUPSQL/TABLE'.
Lookup table or expression (TABLE) - Help
:xh3.Lookup table or expression (TABLE)
:p.Specifies a string expression of up to 510 characters that is used
to form the SQL FROM clause in a SELECT statement. This can be specified
either as a single table name (with or without a JOIN clause) or as a table
expression. In the first case correlation name(s) may be specified as
needed. In the second case, a table expression requires that it be enclosed
in parentheses and have a correlation name specified.
:p.This is a required parameter.
:note.If you need to expand the input field to accept more data, type
an ampersand, a space, and then press the ENTER key.
:ent.
:ehelp.
.*******************************************
.* Help for parameter WHERE
.*******************************************
:help name='LOOKUPSQL/WHERE'.
SQL WHERE clause (WHERE) - Help
:xh3.SQL WHERE clause (WHERE)
:p.Optionally specifies a string expression of up to 510 characters that is
used to form the SQL WHERE clause in a SELECT statement. If specified,
this can be anything that is valid in a WHERE clause.
:note.If you need to expand the input field to accept more data, type
an ampersand, a space, and then press the ENTER key.
:ent.
:parml.
:pt.:pk def.*NONE:epk.
:pd.
Specify this keyword if no WHERE clause is required. This is the default.
:eparml.
:ehelp.
.*******************************************
.* Help for parameter ORDER
.*******************************************
:help name='LOOKUPSQL/ORDER'.
SQL ORDER BY clause (ORDER) - Help
:xh3.SQL ORDER BY clause (ORDER)
:p.Optionally specifies a string expression of up to 510 characters that is
used to form the SQL ORDER BY clause in a SELECT statement. If specified,
this can be anything that is valid in an ORDER BY clause.
:note.Since (internally) only a single SQL row is retrieved, the ORDER BY
clause can be important. Otherwise, if you need to expand the input field to
accept more data, type an ampersand, a space, and then press the ENTER key.
:ent.
:parml.
:pt.:pk def.*NONE:epk.
:pd.
Specify this keyword if no ORDER BY clause is required. This is the default.
:eparml.
:ehelp.
.**************************************************
.* Error messages for LOOKUPSQL
.**************************************************
:help name='LOOKUPSQL/ERROR/MESSAGES'.
&MSG(CPX0005,QCPFMSG). LOOKUPSQL - Help
:xh3.&MSG(CPX0005,QCPFMSG). LOOKUPSQL
:p.:hp3.*ESCAPE &MSG(CPX0006,QCPFMSG).:ehp3.
:DL COMPACT.
:DT.ACL0006
:DD.&MSG(ACL0006,WSMSGF,SHR460,nosub).
:DT.SQL0003
:DD.&MSG(SQL0003,WSMSGF,SHR460,nosub).
:DT.SQL0014
:DD.&MSG(SQL0014,WSMSGF,SHR460,nosub).
:EDL.
:ehelp.
:epnlgrp.
