Executing SQL statements from within a CL with RUNSQL

Here’s a CL Command I didn’t know about, RUNSQL, which allows an SQL statement to be run from within a CL program without needing a source file.

Inevitably, I found this because I was looking for a quick solution to a transitory problem. I won’t bore you with the details but, what I wanted to do was create a temporary file, populate it with orders for a (user specified) date and then copy this file into a library that has been made available to an external ftp application.

Here is what I ended up with (field, file and library names have been changed for clarity and to protect the innocent):

  pgm &date
  dcl &date *char 8
  dcl &statement *char 150
  dcl &nbrrcd *dec (10 0)
  
  cpyf fromfile(template/orders) tofile(qtemp/orders) +
       mbropt(*add) crtfile(*yes)
  clrpfm qtemp/orders
  
  chgvar &statement value('insert into qtemp/orders +
                           select * from archlib/orders  +
                           where msgdat = ' *cat &date)
  runsql sql(&statement) commit(*none)
  
  rtvmbrd qtemp/orders nbrcurrcd(&nbrrcd)
  if cond(&nbrrcd *gt 0) then(do)
     cpyf fromfile(qtemp/orders) tofile(ftplib/orders) +
          mbropt(*add) crtfile(*yes)
     enddo
  
  dltf qtemp/orders
  
  endpgm

It’s all pretty simple stuff but being able to embed the SQL statement right in the CL makes a conceptually simple solution very simple to implement.