Starting off with Embedded SQL

[worksofliam]

I love Embedded SQL in RPG, it's my favourite feature and it couldn't be any better really (well it could, but this isn't a rant). This long blog post is actually going to be more of a guide.

Embedded SQL is going to make your code cleaner, and also process more workload in less lines of code. No more Dcl-F.. Embedded SQL is here to replace it.

I'd like to note, Embedded SQL should not be used to modernize code. If you have business logic using delcared files, do not remove it. Embedded SQL should be used to write new logic in new software.

How does Embedded SQL work?

Interesting topic, this. I like how it works because I have an interest in compilers, others may find it boring - but it's interesting to know.

Embedded SQL takes your source member, scans it for the EXEC SQL operation(?) and replaces it with fixed format RPG (sigh) program calls. I/some call this step the 'pre-compile' - it's what the pre-compiler does. The pre-compiler also declares a load of variables for your use in programs, like SQLSTATE for example.

I use SQLSTATE to check if there are any data errors or SQL errors. SQLSTATE is a character five 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 in my mind, as you can use CRTSQLRPGI as a replacement for both of these. Also, start using SQLRPGLE as the extention for all your 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 post, I'm gonna use this SQL to create a new physical file/table. Now, as naughty as I am: 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 PF in, but it's optional of course.

CREATE TABLE LIAMALLAN1/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
)

This is not an SQL tutorial, but we do want data in the file. 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',
	'[email protected]'
)

INSERT INTO LIAMALLAN1/CUSTOMERS VALUES(
	2, 
	100.66, 
	'Eric Jooka',
	'[email protected]'
)

INSERT INTO LIAMALLAN1/CUSTOMERS VALUES(
	3, 
	1123124.12, 
	'Emily Bae',
	'[email protected]'
)

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.

//Template data structure matching CUSTOMERS file
Dcl-DS CUSTOMER_Temp Qualified Template;
  CUS_ID    Int(10);
  CUS_BAL   Packed(11:2);
  CUS_NAME  Char(25);
  CUS_EMAIL Char(50);
End-Ds;

Dcl-DS CUSTOMER LikeDS(CUSTOMER_Temp);

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, 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. I've commented on the code 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.