You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
Introduction to RPG (Report Program Generator). See this Wikipedia page for an introduction to RPG.
Todo
Intro to RLA
Creating service programs out of modules
Creating commands for programs
Contents
Creating your first RPG program.
RPG Syntax
RPG data-types
RPG Procedures
RPG subroutines
RPG data-structures
RPG prototypes
RPG modules and programs
Embedded SQL basics
Creating your first RPG program.
First, we will need to do the following steps:
Create a library (CRTLIB)
Create a source physical file (CRTSRCPF)
Create a source member (ADDPFM)
You may also do this within an IDE, but it’s very useful to memorise these commands for when you don’t have the IDE.
It is possible to write all your RPG code in stream files on the IFS. The easiest way to create a stream file is with an IDE.
**FREE
//Declare lText standalone field/variable
Dcl-S lText Char(25);
//Assign a value to field
lText = 'Hello world.';
//Display the lText value.
Dsply lText;
//Set Indicator LR to *On (true) and return
*InLR = *On;
Return;
Note: You must be running OS version 7.2 at TR 11 or 7.3 at TR 3 to use the **FREE directive. You can look up your system's TR level using the approach described in this post.
Once we have written our code, we are going to use CRTBNDRPG to create our program object. What makes CRTBNDPGM useful is that it just creates a program object – it’s automating the CRTRPGMOD step. Another way we could have created our program object is by using CRTRPGMOD and then CRTPGM over that module.
CRTBNDRPG does allow you to compile both stream files and source members.
To compile a source member: CRTBNDRPG PGM(MYLIB/XMPLE1) SRCFILE(MYLIB/QRPGLESRC) SRCMBR(XMPLE1) TEXT('My RPG IV Program')
To compile a stream file: CRTBNDRPG PGM(MYLIB/XMPLE1) SRCSTMF('xmple1.rpgle') TEXT('My RPG IV Program')
Note that when you compile a stream file, the SRCSTMF path should be relative to your current directory when running the compile.
You are now going to call your program using CALL <programname>, which should then display ‘Hello world’ on your terminal. If you only see the output flash on your screen then run WRKMBRPDM before calling your program.
RPG Syntax
There are lots of variations of RPG syntax. For all the RPG we write, we will be using free format.
Notice how in the last lecture we used **FREE – this is a compiler directive. It tells the compiler we’re going to write our free format code from the start of the line. If we didn’t have that directive, we’d have to start each line at the 8th index (8 spaces, then start the code). If used, **FREE must be on the first line and in the first 6 characters with nothing following.
*InLR = *On tells the runtime that we’re on the last record. We need this because some elements of legacy RPG are still supported. Read more about indicator LR here.
Setting *InLR = *On by itself never ends the program - it simply tells the program to terminate when you return. - JonBoy, code400.com
In RPG, things like IF, DOW, DOU, SELECT, WHEN, DCL-S, etc are all RPG operations. You will always need a space between the operation code and the next value. For good practice, you should surround expressions with brackets.
//Good practice
If (2 > 1);
Dsply 'True';
ENDIF;
//Bad practice
If 2 > 1;
Dsply 'True';
ENDIF;
Variables are case-insensitive and cannot start with numeric digits (but can contain them). Variables must be defined at the top of your program/procedure It is good practice to start variables in the global scope with a lowercase 'g' – variables in a local scope to start with a lowercase 'l'.
RPG consists of lots of data-types, in this lecture we’re going to cover a few of them. You declare variables with the ‘Dcl-S’ operation, followed by the name, type and then optional keywords.
Dcl-S name type [keywords]
Here are some of the types we’re going to use in our lectures:
When you use character fields in expressions, it will always trim the right blanks from the variable. For example:
If (%TrimR(lMyVarA) = %TrimR(lMyVarB));
Dsply 'True';
ENDIF;
RPG provides lots of keywords when declaring both variables and data-structures (more on data-structures in a future Lecture). One of those is DIM (dimensions), which can be used on both Dcl-S and Dcl-Ds. The DIM keyword allows you declare arrays, where the length is specified in the ‘DIM’ keyword. You can reference array elements the same way you would call a procedure.
Arrays indexes in RPG always start at 1. Whenever you’re referencing the length of an array, never use a hardcoded constant, instead use the ‘%Elem’ built-in function (elements). In this example, we will use this built-in function to clear every element in the array.
Dcl-S index Int(3);
Dcl-S MyArray Char(10) Dim(10);
MyArray(1) = 'Value1';
MyArray(5) = 'Value5';
For index = 1 to %Elem(MyArray);
MyArray(index) = *Blank;
ENDFOR;
While this code is valid and working, there is an easier option to clearing fields, arrays and data-structures. Instead of using this FOR loop to clear the, we can use the CLEAR operation code. The CLEAR operation code works on all fields, arrays, and data-structures (and subfields) – for every type too:
Another useful keyword is INZ (initialization, US spelling). This allows the developer to give variables an initial value when they are declared. You would have already seen this used in this Lecture and will in future lectures too. If you use ‘INZ’ with ‘DIM’, it will give every element that initial value. There is a handy extra operation code, called RESET. RESET allows you to reset fields, arrays, and data-structure subfields back to their initial value (defined with the ‘INZ’ keyword):
Dcl-Proc stands for 'declare procedure' and Dcl-Pi stands for declare procedure-interface. A PI is required for any procedure or program that has parameters or a return value. It’s used so the compiler knows how to invoke it correctly.
In this example, after MyProc() has been called – it will update the global variable to a different value. Notice how there is no PI because there are no parameters or return value.
In this example, calling MyProc() returns a Char(8) value and assigns it to gMyVar. Notice how the Dcl-Pi and End-Pi are on the same line because there are no parameters for this procedure. *N represents nothing/blank. You are able to replace it with the procedure name, but it is not required.
In this example, we call MyProc() with a parameter and returns my parameter with an exclamation mark concatenated on the end. We use the ‘const’ option here so we can pass in a constant value (or expression). Notice that ‘End-Pi’ is after the parameter definition.
RPG subroutines
A subroutine is a block of code that can be executed in the current scope and has access to all variables in the current and global scope. Variables cannot be defined in a subroutine and subroutines must be defined after the return point of your program/procedure. Mainlines can only access subroutines within the global scope and procedures can only access subroutines in the local scope (of that procedure).
The following operations are used for subroutines:
Simply put, data-structures (a DS) in RPG are a set of fields that are grouped together (even in memory). Data-structures contain subfields which can be of any type and they can also contain sub-data-structures (A DS, within a DS). When declaring subfields, you can use any of the keywords you’d use when declaring a regular field (e.g INZ, DIM). You can read more here about free-format data-structures.
dcl-ds name [keywords];
subfield type [keywords];
…
end-ds;
It is important to know what a qualified DS and non-qualified DS is. Qualified simply means that when you reference it, you must append the data-structure name to the left. For example, let’s make the previous DS example qualified.
Data-structure templating is a useful thing to know if you like to write clean code. A DS template is a data-structure that is not defined at runtime, but is known to the compiler so you can copy it’s subfields into another data-structure.
Note that you do not have to use LIKEDS on a data-structure with the TEMPLATE keyword – you can use it on any data-structure.
Externally-described data structure
It is possible to use the EXTNAME keyword to create a data-structure which will have subfields which match columns in a table. This is called a 'Externally-described data structure'.
Let's say you had this file:
CREATE TABLE CUSTOMERS (
CUS_ID INT NOT NULL WITH DEFAULT,
CUS_BAL NUMERIC (11 , 2) NOT NULL WITH DEFAULT,
CUS_NAME CHAR (25) NOT NULL WITH DEFAULT,
CUS_EMAIL CHAR (50) NOT NULL WITH DEFAULT
)
We could create a data-structure like so (below). Using the EXTNAME keyword will make it qualified automatically.
In RPG, each program and procedure can have an interface. We learned about procedure interfaces in a previous chapter, but we didn't learn that programs can also have a PI (procedure interface).
You can read more here for info on procedure interface declaration.
In general, procedure interfaces for programs can:
Have pass by reference and const parameters.
The value after Dcl-PI should be the program name.
Next we must learn about prototypes. A prototype is used so the compiler knows how to map the parameters of other procedures and programs so we can call them.
There are two types of prototypes:
Program prototypes, which uses keyword EXTPGM
Reference to external program objects
Procedure prototypes, which uses keyword EXTPROC
Reference to procedures in different modules within the current program
Reference to procedures in service programs within the specified binding directory
Reference to APIs provided by the operating system (printf or system for example)
The syntax is as follows
dcl-pr name [returntype] extpgm/extproc[()] [end-pi];
parmname parmtype passby;
end-pr;
Each parameter must match to that of the program, procedure or API you are referencing. The following example is a program with a prototype for the program shown at the beginning of this chapter:
**FREE
Dcl-PR XMPLE1 ExtPgm;
Name Char(10);
End-PR;
Dcl-S myName Char(10);
myName = 'Barry';
//Call your program like a procedure
XMPLE1(myName);
*InLR = *On;
Return;
Note that if the external program, procedure or API name is different from your prototype name: you will have to pass a parameter to EXTPGM/EXTPROC to specify the real name of what youre trying to call. For example:
Dcl-PR myPgm ExtPgm('XMPLE1');
Name Char(10);
End-PR;
You can read more here for info on procedure prototype declaration.
RPG modules
In this section we will use three new commands, CRTRPGMOD, CRTSQLRPGI and CRTPGM:
CRTRPGMOD is used to create a module object. Modules can contain procedures or can be used as an entry point for programs.
CRTSQLRPGI can be used to create both RPG modules and programs. We will use it to create a module in this section.
CRTPGM is used to package modules together to make a program object.
Modules should be used to make modular and reusable code. Normally, procedures in modules are created with the EXPORT keyword. The EXPORT keyword means it can be referenced by other modules within the program it is part of. If your module is just a set of 'exported' procedures, it should use the NOMAIN program-header which you will see next.
For example, this module wraps two embedded SQL statements into procedures; let's call this module MATHMOD.
To compile this, we can use CRTSQLRPGI with option OBJTYPE(*MODULE) so that a module is created. CRTSQLRPGI is similar to CRTBNDRPG and CRTRPGMOD, but we will talk more about that later.
NOMAIN is used to indicate that this module has no mainline and that there is no entry point. The other two procedures have the EXPORT keyword to indicate that they can be referenced by other modules.
To reference them, we must declare their procedure prototypes in all the modules we want to call them from. In the next example, we will create the entry module. This module will be the mainline entry point for the program object when it is called; let's call this module MYPGM.
To compile this, we can simply use CRTRPGMOD since there is no embedded SQL.
Now that we have both modules, we can use CRTPGM. Notice how the first module on my module list is the entry module - you are also able to manually specify the entry module. I am going to call the program MYPGM.
After you have created the program, you should be able to call the program like any other:
CALL MYPGM
This really should give a brief idea of how modules can be used. You may have an order entry program, where the entry module does all the display file handling, another module might do the order entry processing and another might send data to a web service.
Know that you can include modules written in any ILE language when creating programs.
RPG and Embedded SQL
How does Embedded SQL work?
Embedded SQL takes your source member, scans it for the EXEC SQL operation(?) and replaces it with RPG program calls. I/some call this step the 'pre-compile' - it's what the pre-compiler does. The pre-compiler also declares a data-structure for your use in programs, and one of the subfields is SQLSTATE for example. I use SQLSTATE to check if there are any data errors or SQL errors. SQLSTATE is a character five (char(5)) field and you can find what the data means here.
How do I start using it?
The first step is to ditch CRTBNDRPG and CRTRPGMOD. They are now useless for writing code with Embedded SQL because you can use CRTSQLRPGI as a replacement for both of these. Also, you can start using SQLRPGLE as the extention for all your Embedded SQL RPG code.
To create a regular program, you use CRTSQLRPGI with OBJTYPE(*PGM) as a parameter; for a module you use OBJTYPE(*MODULE). This is just my opinion though, of course you can still use the other commands, but you can still compile regular RPG with this command.
For this, we are going to use this SQL to create a new physical file/table. I did use STRSQL to create this table. I typed create table and used F4 to prompt the rest of the data in. LIAMALLAN1 is the library I made the table in, but that is optional of course.
CREATETABLELIAMALLAN1/CUSTOMERS (
CUS_ID INTNOT NULL WITH DEFAULT,
CUS_BAL NUMERIC (11 , 2) NOT NULL WITH DEFAULT,
CUS_NAME CHAR (25) NOT NULL WITH DEFAULT,
CUS_EMAIL CHAR (50) NOT NULL WITH DEFAULT
)
You can optionally use UPDDTA against the PF with insert mode to add data - or you can use SQL INSERT. Note that these are all seperate statements.
INSERT INTO LIAMALLAN1/CUSTOMERS VALUES(
1,
10.25,
'Liam Barry',
'mrliamallan@live.co.uk'
)
INSERT INTO LIAMALLAN1/CUSTOMERS VALUES(
2,
100.66,
'Eric Jooka',
'ericjooka@person.com'
)
INSERT INTO LIAMALLAN1/CUSTOMERS VALUES(
3,
1123124.12,
'Emily Bae',
'emilybae@hello.com'
)
How do I really start using it?
Now we have some data, we can really start using Embedded SQL. So, make sure you have a test source member/steamfile to put your Embedded SQL in. Embedded SQL allows any regular DB2 statement within your source, be it DELETE, UPDATE, INSERT or SELECT.
As SELECT may be the most important one for beginners, we'll look at that first. As good practice, for every PF I use within Embedded SQL, I like to declare a data-structure (Dcl-DS) matching the PF fields. I also make it a template, incase I want to use it in multiple places.
Selecting one record from the file is a simple start and useful if you're writing something like a maintainance screen.
Exec SQL SELECT CUS_BAL,
CUS_NAME
INTO :Customer.CUS_BAL,
:Customer.CUS_NAME
FROM CUSTOMERS
WHERE CUS_ID = 1;
And as you can see, it's simple to get data - very easy. What if we update data on our maintainance screen? The next snippet of code sits below the previous SELECT statement that we created in our RPG.
//Imagine this is the change on our screen
Customer.CUS_NAME = 'Barry James';
Exec SQL UPDATE CUSTOMERS SET
CUS_BAL = :Customer.CUS_BAL,
CUS_NAME = :Customer.CUS_NAME
WHERE CUS_ID = 1;
After we have compiled and ran this program, open STRSQL and SELECT * FROM CUSTOMERS..
Lots of data
It's a need to SELECT more than one row at a time and luckily you can do this with cursors. Note, when using cursors: make sure you close the cursor when you've finished using it - you're causing yourself problems if you don't do this. Luckily, we declared our CUSTOMER data-structure so we can re-use it in our do-while.
You delcare your cursor with your SELECT statement. The syntax is a bit like EXEC SQL DECLARE [cursor-name] CURSOR FOR [select-statement].
Exec SQL Declare Cust_Cur Cursor FOR
SELECT *
FROM CUSTOMERS;
Even after we've closed the cursor, we are able to re-open it again - but not while it's already open. The code is commented instead of seperating it into blocks.
Exec SQL Declare Cust_Cur Cursor FOR
SELECT *
FROM CUSTOMERS;
//Open our cursor that we defined previously.
Exec SQL Open Cust_Cur;
//'00000' = Unqualified Successful Completion
If (SQLSTATE = '00000');
//Attemping to get the first record from
//CUSTOMERS into our CUSTOMER data structure.
Exec SQL Fetch Cust_Cur Into :CUSTOMER;
//'00000' = Unqualified Successful Completion
Dow (SQLSTATE = '00000');
//Print some data
printf(%Trim(Customer.CUS_NAME) + ': ' + %Char(Customer.CUS_BAL) + x'25');
//Fetch the next record
Exec SQL Fetch Cust_Cur Into :CUSTOMER;
ENDDO;
ENDIF;
//Close the cursor.. WE MUST CLOSE THE CURSOR
Exec SQL Close Cust_Cur;
This code will loop through each record in the CUSTOMERS file and print some relevant data out. It looks something like this..
Deleting data
There is still a lot of cursors I haven't covered.. but this is a good start. The last part I will cover is deleting data from the CUSTOMERS file. It's simple, like all over Embedded SQL statements EXEC SQL [statement]. There are two ways I'm going to show. The first is a hardcoded CUS_ID, the second is using Customer.CUS_ID.
Exec SQL DELETE FROM CUSTOMERS
WHERE CUS_ID = 1;
Customer.CUS_ID = 2;
Exec SQL DELETE FROM CUSTOMERS
WHERE CUS_ID = :Customer.CUS_ID;
The ending result would remove two records from the file.
The text was updated successfully, but these errors were encountered:
Introduction to RPG (Report Program Generator). See this Wikipedia page for an introduction to RPG.
Todo
Contents
Creating your first RPG program.
First, we will need to do the following steps:
CRTLIB
)CRTSRCPF
)ADDPFM
)You may also do this within an IDE, but it’s very useful to memorise these commands for when you don’t have the IDE.
It is possible to write all your RPG code in stream files on the IFS. The easiest way to create a stream file is with an IDE.
Note: You must be running OS version 7.2 at TR 11 or 7.3 at TR 3 to use the
**FREE
directive. You can look up your system's TR level using the approach described in this post.Once we have written our code, we are going to use CRTBNDRPG to create our program object. What makes CRTBNDPGM useful is that it just creates a program object – it’s automating the CRTRPGMOD step. Another way we could have created our program object is by using CRTRPGMOD and then CRTPGM over that module.
CRTBNDRPG does allow you to compile both stream files and source members.
CRTBNDRPG PGM(MYLIB/XMPLE1) SRCFILE(MYLIB/QRPGLESRC) SRCMBR(XMPLE1) TEXT('My RPG IV Program')
CRTBNDRPG PGM(MYLIB/XMPLE1) SRCSTMF('xmple1.rpgle') TEXT('My RPG IV Program')
Note that when you compile a stream file, the SRCSTMF path should be relative to your current directory when running the compile.
You can read more here for info on CRTBNDPGM.
You are now going to call your program using
CALL <programname>
, which should then display ‘Hello world’ on your terminal. If you only see the output flash on your screen then runWRKMBRPDM
before calling your program.RPG Syntax
There are lots of variations of RPG syntax. For all the RPG we write, we will be using free format.
Notice how in the last lecture we used
**FREE
– this is a compiler directive. It tells the compiler we’re going to write our free format code from the start of the line. If we didn’t have that directive, we’d have to start each line at the 8th index (8 spaces, then start the code). If used,**FREE
must be on the first line and in the first 6 characters with nothing following.*InLR = *On
tells the runtime that we’re on the last record. We need this because some elements of legacy RPG are still supported. Read more about indicator LR here.In RPG, things like IF, DOW, DOU, SELECT, WHEN, DCL-S, etc are all RPG operations. You will always need a space between the operation code and the next value. For good practice, you should surround expressions with brackets.
Variables are case-insensitive and cannot start with numeric digits (but can contain them). Variables must be defined at the top of your program/procedure It is good practice to start variables in the global scope with a lowercase 'g' – variables in a local scope to start with a lowercase 'l'.
RPG data-types
RPG consists of lots of data-types, in this lecture we’re going to cover a few of them. You declare variables with the ‘Dcl-S’ operation, followed by the name, type and then optional keywords.
Here are some of the types we’re going to use in our lectures:
When you use character fields in expressions, it will always trim the right blanks from the variable. For example:
Is actually computed as:
RPG provides lots of keywords when declaring both variables and data-structures (more on data-structures in a future Lecture). One of those is
DIM
(dimensions), which can be used on bothDcl-S
andDcl-Ds
. TheDIM
keyword allows you declare arrays, where the length is specified in the ‘DIM’ keyword. You can reference array elements the same way you would call a procedure.Arrays indexes in RPG always start at 1. Whenever you’re referencing the length of an array, never use a hardcoded constant, instead use the ‘%Elem’ built-in function (elements). In this example, we will use this built-in function to clear every element in the array.
While this code is valid and working, there is an easier option to clearing fields, arrays and data-structures. Instead of using this
FOR
loop to clear the, we can use theCLEAR
operation code. TheCLEAR
operation code works on all fields, arrays, and data-structures (and subfields) – for every type too:Another useful keyword is
INZ
(initialization, US spelling). This allows the developer to give variables an initial value when they are declared. You would have already seen this used in this Lecture and will in future lectures too. If you use ‘INZ’ with ‘DIM’, it will give every element that initial value. There is a handy extra operation code, calledRESET
.RESET
allows you to reset fields, arrays, and data-structure subfields back to their initial value (defined with the ‘INZ’ keyword):RPG Procedures
Procedures in RPG are very comparable to functions in C. If you studied Lecture 5, you would have already seen an example of a basic procedure in RPG.
Procedures can:
The syntax of a procedure can be confusing at first, but after practice it will become much simpler.
Dcl-Proc
stands for 'declare procedure' andDcl-Pi
stands fordeclare procedure-interface
. A PI is required for any procedure or program that has parameters or a return value. It’s used so the compiler knows how to invoke it correctly.Procedure with no parameters or return value
In this example, after
MyProc()
has been called – it will update the global variable to a different value. Notice how there is no PI because there are no parameters or return value.Procedure with no parameters and return value
In this example, calling
MyProc()
returns a Char(8) value and assigns it togMyVar
. Notice how theDcl-Pi
andEnd-Pi
are on the same line because there are no parameters for this procedure.*N
represents nothing/blank. You are able to replace it with the procedure name, but it is not required.Procedure with a parameter and return value.
In this example, we call MyProc() with a parameter and returns my parameter with an exclamation mark concatenated on the end. We use the ‘const’ option here so we can pass in a constant value (or expression). Notice that ‘End-Pi’ is after the parameter definition.
RPG subroutines
A subroutine is a block of code that can be executed in the current scope and has access to all variables in the current and global scope. Variables cannot be defined in a subroutine and subroutines must be defined after the return point of your program/procedure. Mainlines can only access subroutines within the global scope and procedures can only access subroutines in the local scope (of that procedure).
The following operations are used for subroutines:
RPG data-structures
Simply put, data-structures (a DS) in RPG are a set of fields that are grouped together (even in memory). Data-structures contain subfields which can be of any type and they can also contain sub-data-structures (A DS, within a DS). When declaring subfields, you can use any of the keywords you’d use when declaring a regular field (e.g
INZ
,DIM
). You can read more here about free-format data-structures.It is important to know what a qualified DS and non-qualified DS is. Qualified simply means that when you reference it, you must append the data-structure name to the left. For example, let’s make the previous DS example qualified.
Data-structure template
Data-structure templating is a useful thing to know if you like to write clean code. A DS template is a data-structure that is not defined at runtime, but is known to the compiler so you can copy it’s subfields into another data-structure.
Note that you do not have to use
LIKEDS
on a data-structure with theTEMPLATE
keyword – you can use it on any data-structure.Externally-described data structure
It is possible to use the
EXTNAME
keyword to create a data-structure which will have subfields which match columns in a table. This is called a 'Externally-described data structure'.Let's say you had this file:
We could create a data-structure like so (below). Using the
EXTNAME
keyword will make it qualified automatically.This means it would have four subfields, which match the table columns:
You can read more about the
EXTNAME
keyword here.RPG Prototypes
In RPG, each program and procedure can have an interface. We learned about procedure interfaces in a previous chapter, but we didn't learn that programs can also have a PI (procedure interface).
You can read more here for info on procedure interface declaration.
In general, procedure interfaces for programs can:
Dcl-PI
should be the program name.Prototyping
Next we must learn about prototypes. A prototype is used so the compiler knows how to map the parameters of other procedures and programs so we can call them.
There are two types of prototypes:
EXTPGM
EXTPROC
printf
orsystem
for example)The syntax is as follows
Each parameter must match to that of the program, procedure or API you are referencing. The following example is a program with a prototype for the program shown at the beginning of this chapter:
Note that if the external program, procedure or API name is different from your prototype name: you will have to pass a parameter to
EXTPGM
/EXTPROC
to specify the real name of what youre trying to call. For example:You can read more here for info on procedure prototype declaration.
RPG modules
In this section we will use three new commands,
CRTRPGMOD
,CRTSQLRPGI
andCRTPGM
:CRTRPGMOD
is used to create a module object. Modules can contain procedures or can be used as an entry point for programs.CRTSQLRPGI
can be used to create both RPG modules and programs. We will use it to create a module in this section.CRTPGM
is used to package modules together to make a program object.Modules should be used to make modular and reusable code. Normally, procedures in modules are created with the
EXPORT
keyword. TheEXPORT
keyword means it can be referenced by other modules within the program it is part of. If your module is just a set of 'exported' procedures, it should use theNOMAIN
program-header which you will see next.For example, this module wraps two embedded SQL statements into procedures; let's call this module MATHMOD.
To compile this, we can use
CRTSQLRPGI
with optionOBJTYPE(*MODULE)
so that a module is created.CRTSQLRPGI
is similar toCRTBNDRPG
andCRTRPGMOD
, but we will talk more about that later.NOMAIN
is used to indicate that this module has no mainline and that there is no entry point. The other two procedures have theEXPORT
keyword to indicate that they can be referenced by other modules.To reference them, we must declare their procedure prototypes in all the modules we want to call them from. In the next example, we will create the entry module. This module will be the mainline entry point for the program object when it is called; let's call this module
MYPGM
.To compile this, we can simply use
CRTRPGMOD
since there is no embedded SQL.Now that we have both modules, we can use
CRTPGM
. Notice how the first module on my module list is the entry module - you are also able to manually specify the entry module. I am going to call the programMYPGM
.After you have created the program, you should be able to call the program like any other:
This really should give a brief idea of how modules can be used. You may have an order entry program, where the entry module does all the display file handling, another module might do the order entry processing and another might send data to a web service.
Know that you can include modules written in any ILE language when creating programs.
RPG and Embedded SQL
How does Embedded SQL work?
Embedded SQL takes your source member, scans it for the EXEC SQL operation(?) and replaces it with RPG program calls. I/some call this step the 'pre-compile' - it's what the pre-compiler does. The pre-compiler also declares a data-structure for your use in programs, and one of the subfields is
SQLSTATE
for example. I useSQLSTATE
to check if there are any data errors or SQL errors.SQLSTATE
is a character five (char(5)
) field and you can find what the data means here.How do I start using it?
The first step is to ditch
CRTBNDRPG
andCRTRPGMOD
. They are now useless for writing code with Embedded SQL because you can useCRTSQLRPGI
as a replacement for both of these. Also, you can start usingSQLRPGLE
as the extention for all your Embedded SQL RPG code.To create a regular program, you use
CRTSQLRPGI
withOBJTYPE(*PGM)
as a parameter; for a module you useOBJTYPE(*MODULE)
. This is just my opinion though, of course you can still use the other commands, but you can still compile regular RPG with this command.For this, we are going to use this SQL to create a new physical file/table. I did use
STRSQL
to create this table. I typed create table and used F4 to prompt the rest of the data in.LIAMALLAN1
is the library I made the table in, but that is optional of course.You can optionally use
UPDDTA
against the PF with insert mode to add data - or you can useSQL INSERT
. Note that these are all seperate statements.How do I really start using it?
Now we have some data, we can really start using Embedded SQL. So, make sure you have a test source member/steamfile to put your Embedded SQL in. Embedded SQL allows any regular DB2 statement within your source, be it
DELETE
,UPDATE
,INSERT
orSELECT
.As
SELECT
may be the most important one for beginners, we'll look at that first. As good practice, for every PF I use within Embedded SQL, I like to declare a data-structure (Dcl-DS
) matching the PF fields. I also make it a template, incase I want to use it in multiple places.Selecting one record from the file is a simple start and useful if you're writing something like a maintainance screen.
And as you can see, it's simple to get data - very easy. What if we update data on our maintainance screen? The next snippet of code sits below the previous
SELECT
statement that we created in our RPG.After we have compiled and ran this program, open
STRSQL
andSELECT * FROM CUSTOMERS
..Lots of data
It's a need to
SELECT
more than one row at a time and luckily you can do this with cursors. Note, when using cursors: make sure you close the cursor when you've finished using it - you're causing yourself problems if you don't do this. Luckily, we declared ourCUSTOMER
data-structure so we can re-use it in our do-while.You delcare your cursor with your
SELECT
statement. The syntax is a bit likeEXEC SQL DECLARE [cursor-name] CURSOR FOR [select-statement]
.Even after we've closed the cursor, we are able to re-open it again - but not while it's already open. The code is commented instead of seperating it into blocks.
This code will loop through each record in the
CUSTOMERS
file and print some relevant data out. It looks something like this..Deleting data
There is still a lot of cursors I haven't covered.. but this is a good start. The last part I will cover is deleting data from the CUSTOMERS file. It's simple, like all over Embedded SQL statements
EXEC SQL [statement]
. There are two ways I'm going to show. The first is a hardcodedCUS_ID
, the second is usingCustomer.CUS_ID
.The ending result would remove two records from the file.
The text was updated successfully, but these errors were encountered: