SQL Lookup with Returned Value (LOOKUPSQL)

From MidrangeWiki
Revision as of 14:31, 19 August 2019 by DaveLClarkI (talk | contribs) (Summary)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

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')                         

[top]

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;

[top]

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.                                                                        

[top]