IBM Support

Creating a Variable Field String for embedded SQL in RPGLE

Troubleshooting


Problem

This document explains how to create a Variable Field String for embedded SQL in RPGLE.

Resolving The Problem

Set up an SQL statement string that contains variable fields to be used in RPGLE embedded SQL.
This sample uses a table that can be created by using SQL.
CREATE TABLE <mylib>.NAME (FLD1 CHAR ( 5) NOT NULL WITH DEFAULT,
FLD2 CHAR ( 6) NOT NULL WITH DEFAULT)  ;

// add a couple of rows to test the program
INSERT INTO <mylib>.NAME (FLD1, FLD2) VALUES('AAA', 'CCC');
INSERT INTO <mylib>.NAME (FLD1, FLD2) VALUES('AAA', 'BBB')   

                     
The sample deletes rows that match the values for Fld1 and Fld2.
The values are updated in the job's LDA and the sample reads the LDA to obtain these values.
CHGDTAARA DTAARA(*LDA *ALL) VALUE(AAACCC)
The SQL statement gets constructed in the string variable.
This sample matches any rows that have Fld1 = 'AAA' and Fld2 = 'CCC'
// ******************************************************************** 
//  LICENSE AND DISCLAIMER                                            * 
//  ----------------------                                            * 
//  This material contains IBM copyrighted sample programming source  * 
//  code ( Sample Code ).                                             * 
//  IBM grants you a nonexclusive license to compile, link, execute,  * 
//  display, reproduce, distribute and prepare derivative works of    * 
//  this Sample Code.  The Sample Code has not been thoroughly        * 
//  tested under all conditions.  IBM, therefore, does not guarantee  * 
//  or imply its reliability, serviceability, or function. IBM        * 
//  provides no program services for the Sample Code.                 * 
//                                                                    * 
//  All Sample Code contained herein is provided to you "AS IS"       * 
//  without any warranties of any kind. THE IMPLIED WARRANTIES OF     * 
//  MERCHANTABILITY, FITNESS FOR A PARTICULAR PURPOSE AND             * 
//  NON-INFRINGMENT ARE EXPRESSLY DISCLAIMED.                         * 
//  SOME JURISDICTIONS DO NOT ALLOW THE EXCLUSION OF IMPLIED          * 
//  WARRANTIES, SO THE ABOVE EXCLUSIONS MAY NOT APPLY TO YOU.  IN NO  * 
//  EVENT WILL IBM BE LIABLE TO ANY PARTY FOR ANY DIRECT, INDIRECT,   * 
//  SPECIAL OR OTHER CONSEQUENTIAL DAMAGES FOR ANY USE OF THE SAMPLE  * 
//  CODE INCLUDING, WITHOUT LIMITATION, ANY LOST PROFITS, BUSINESS    * 
//  INTERRUPTION, LOSS OF PROGRAMS OR OTHER DATA ON YOUR INFORMATION  * 
//  HANDLING SYSTEM OR OTHERWISE, EVEN IF WE ARE EXPRESSLY ADVISED OF * 
//  THE POSSIBILITY OF SUCH DAMAGES.                                  * 
// ********************************************************************
**FREE
Ctl-Opt DftActGrp(*No);
Dcl-Ds Datastrct DtaAra(*Auto:*UsrCtl:*LDA);
  Fld1            Char(3);
  Fld2            Char(3);
End-Ds;

Dcl-S FldA            Char(5)         INZ('Fld1');
Dcl-S FldB            Char(6)         INZ('Fld2');
Dcl-S Name            Char(10)        INZ('NAME');
Dcl-S String          Char(100);
//
String = ('DELETE FROM ' + %trim(Name)      +
    ' WHERE ' + %trim(FldA)  + ' = ? and '      +
    %trim(FldB) + ' = ?');
DLTNAM();
*INLR = *ON;
//
Dcl-Proc DLTNAM;
EXEC SQL
 PREPARE P1 FROM :String;
EXEC SQL
 EXECUTE P1 USING :Fld1,:Fld2;
End-Proc DLTNAM;    
The sample source was named SQL1 and compiled by using:
 
CRTSQLRPGI OBJ(<mylib>/SQL1) SRCFILE(<mylib>/QRPGLESRC) SRCMBR(SQL1) OBJTYPE(*PGM)                                                             
                                   
Run the program from an interactive command line:
CALL <mylib>/SQL1
Examine the table after the program runs. 
The rows that had Fld1= 'AAA' Fld2= 'CCC' were deleted.
Note: This sample has no error handling.

[{"Type":"MASTER","Line of Business":{"code":"LOB57","label":"Power"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Platform":[{"code":"PF012","label":"IBM i"}],"Version":"6.1.0"}]

Historical Number

24233305

Document Information

Modified date:
28 October 2022

UID

nas8N1017259