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.
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.
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
Was this topic helpful?
Document Information
Modified date:
28 October 2022
UID
nas8N1017259