SQL examples

You can access an SQL data base in any of these ways:

• By hand-coding an SQL statement whose format is known at generation time.

• By using an SQL record as the I/O object of an EGL statement, when the format of the SQL statement is known at generation time--
  • If you place an explicit SQL statement in the EGL source, that SQL statement is used at run time;

  • Otherwise, an implicit SQL statement is used at run time.

• By coding an EGL prepare statement, which generates an SQL PREPARE statement that in turn creates an SQL statement at run time.

In every case, you can use an SQL record as a memory area and to provide a simple way to test for successful operation. The examples in this section assume that a record part is declared in an EGL file and that a record based on the part was declared in a program in that file:

• The SQL record part is as follows--

    Record Employee type sqlRecord
      { 
        tableNames = employee,
        keyItems = empnum,
        defaultSelectCondition = 
          #sql{   // no space between #sql and the brace
            aTableColumn = 4   -- start each SQL comment 
                               -- with a double hyphen
          }
      }
  
      empnum  decimal(6,0) {isReadonly=yes};
      empname char(40);
    end

• The SQL record is as follows--

    emp Employee;

For further details on SQL records and implicit statements, see SQL support.

Coding SQL statements

To prepare to code SQL statements, declare variables:

  empnum decimal(6,0);
  empname char(40);

Adding a row to an SQL table

To prepare to add a row, assign values to variables:

  empnum = 1;
  empname = "John";

To add the row, associate an EGL execute statement with an SQL INSERT statement as follows:

  try
    execute
      #sql{
        insert into employee (empnum, empname)
        values (:empnum, :empname)
      };
  onException
    myErrorHandler(8);
  end

Reading a set of rows from an SQL table

To prepare to read a set of rows from an SQL table, identify a record key:

  empnum = 1;  

To get the data, code a series of EGL statements:

• To select a result set, run an EGL open statement--

    open selectEmp 
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname    
      }
      into empnum, empname;

• To access the next row of the result set, run an EGL get next statement--

    get next from selectEmp;        

  If you did not specify the into clause in the open statement, you need to specify the into clause in the get next statement; and if you specified the into clause in both places, the clause in the get next statement takes precedence:

    get next from selectEmp
      into empnum, empname;  

  The cursor is closed automatically when the last record is read from the result set.

A more complete example is as following code, which updates a set of rows:

  handleHardIOErrors = 1;

  try
    open selectEmp
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname
      }
      into empnum, empname;
  onException
    myErrorHandler(6);    // exits program
  end

  try 
    get next from selectEmp;
  onException
    if (sqlcode != 100)
      myErrorHandler(8);  // exits program
    end 
  end

  while (sqlcode != 100) 
    empname = empname + " " + "III";

    try
      execute
        #sql{
          update employee
          set empname = :empname
          where current of selectEmp
        };
    onException
      myErrorHandler(10); // exits program
    end

    try
      get next from selectEmp;
    onException
      if (sqlcode != 100)
        myErrorHandler(8);  // exits program
      end 
    end
  end  // end while; cursor is closed automatically
       // when the last row in the result set is read

  sysLib.commit();

Instead of coding the get next and while statements, you can use the forEach statement, which executes a block of statements for each row in a result set:

  handleHardIOErrors = 1;

  try
    open selectEmp
      with #sql{
        select empnum, empname
        from employee
        where empnum >= :empnum
        for update of empname
      }
      into empnum, empname;
  onException
    myErrorHandler(6);    // exits program
  end

  try 
    forEach (from selectEmp)
      empname = empname + " " + "III";
    
      try
        execute
          #sql{
            update employee
            set empname = :empname
          where current of selectEmp
          };
      onException
        myErrorHandler(10); // exits program
      end
    end  // end forEach; cursor is closed automatically
         // when the last row in the result set is read

  onException
    // the exception block related to forEach is not run if the condition 
    // is "sqlcode = 100", so avoid the test "if (sqlcode != 100)"
    myErrorHandler(8);  // exits program
  end

  sysLib.commit();

Using SQL records with implicit SQL statements

To begin using EGL SQL records, declare an SQL record part:

  Record Employee type sqlRecord
    { 
      tableNames = employee,
      keyItems = empnum,
      defaultSelectCondition = 
        #sql{
          aTableColumn = 4 -- start each SQL comment 
                           -- with a double hyphen
        }
    }

    empnum  decimal(6,0) {isReadonly=yes};
    empname char(40);
  end

Declare a record that is based on the record part:

  emp Employee;

Adding a row to an SQL table

To prepare to add a row to an SQL table, place values in the EGL record:

  emp.empnum = 1;
  emp.empname = "John";

Add an employee to the table by specifying the EGL add statement:

  try
    add emp;
  onException
    myErrorHandler(8);
  end

Reading rows from an SQL table

To prepare to read rows from an SQL table, identify a record key:

  emp.empnum = 1;  

Get a single row in either of these ways:

• Specify the EGL get statement in a way that generates a series of statements (DECLARE cursor, OPEN cursor, FETCH row, and in the absence of forUpdate, CLOSE cursor):

    try
      get emp;
    onException
      myErrorHandler(8);
    end

• Specify the EGL get statement in a way that generates a single SELECT statement:

    try
      get emp singleRow;
    onException
      myErrorHandler(8);
    end

Process multiple rows in either of these ways:

• Use the EGL open, get next, and while statements--

    handleHardIOErrors = 1;
  
    try
      open selectEmp forUpdate for emp;
    onException
      myErrorHandler(6);    // exits program
    end
  
    try 
      get next emp;
    onException
      if (emp not noRecordFound)
        myErrorHandler(8);  // exit the program
      end 
    end
  
    while (emp not noRecordFound) 
      myRecord.empname = myRecord.empname + " " + "III";
  
      try
        replace emp;
      onException
        myErrorHandler(10);   // exits program
      end
  
      try
        get next emp;
      onException
        if (emp not noRecordFound)
          myErrorHandler(8);  // exits program
        end 
      end
    end  // end while; cursor is closed automatically
         // when the last row in the result set is read
  
    sysLib.commit();

• Use the EGL open and forEach statements:

    handleHardIOErrors = 1;
  
    try
      open selectEmp forUpdate for emp;
    onException
      myErrorHandler(6);    // exits program
    end
  
    try 
      forEach (from selectEmp)
        myRecord.empname = myRecord.empname + " " + "III";
       
        try
          replace emp;
        onException
          myErrorHandler(10);   // exits program
        end
      end  // end forEach; cursor is closed automatically
           // when the last row in the result set is read
  
    onException
  
      // the exception block related to forEach is not run if the condition 
      // is noRecordFound, so avoid the test "if (not noRecordFound)" 
      myErrorHandler(8);  // exit the program
    end
  
    sysLib.commit();

Using SQL records with explicit SQL statements

Before using SQL records with explicit SQL statements, you declare an SQL record part. This part is different from the previous one, in the syntax for SQL item properties and in the use of a calculated value:

  Record Employee type sqlRecord
    {
      tableNameVariables = empTable, 
                   // use of a table-name variable
                   // means that the table is specified
                   // at run time
      keyItems = empnum
    }
    empnum decimal(6,0) { isReadonly = yes };
    empname char(40);

    // specify properties of a calculated column 
    aValue decimal(6,0) 
      { isReadonly = yes,
        column = "(empnum + 1) as NEWNUM" };
  end

Declare variables:

  emp Employee;
  empTable char(40);

Adding a row to an SQL table

To prepare to add a row to an SQL table, place values in the EGL record and in a table name variable:

  emp.empnum = 1;
  emp.empname = "John";
  empTable = "Employee";

Add an employee to the table by specifying the EGL add statement and modifying the SQL statement:

  // a colon does not precede a table name variable
  try
    add emp
      with #sql{
        insert into empTable (empnum, empname)
        values (:empnum, :empname || ' ' || 'Smith')
      }

  onException
    myErrorHandler(8);
  end

Reading rows from an SQL table

To prepare to read rows from an SQL table, identify a record key:

  emp.empnum = 1;  

Get a single row in any of these ways:

• Specify the EGL get statement in a way that generates a series of statements (DECLARE cursor, OPEN cursor, FETCH row, CLOSE cursor):

    try
      get emp into empname   // The into clause is optional. (It
                             // cannot be in the SELECT statement.)
      with #sql{
        select empname         
        from empTable      
        where empum = :empnum + 1
      }
    onException
      myErrorHandler(8);
    end

• Specify the EGL get statement in a way that generates a single SELECT statement:

    try
      get emp singleRow // The into clause is derived
                        // from the SQL record and is based
                        // on the columns in the select clause
      with #sql{
        select empname         
        from empTable         
        where empnum = :empnum + 1
      }
    onException
      myErrorHandler(8);
    end

Process multiple rows in either of these ways:

• Use the EGL open, get next, and while statements:

    try
  
    // The into clause is derived
    // from the SQL record and is based
    // on the columns in the select clause
      open selectEmp forUpdate 
        with #sql{
          select empnum, empname
          from empTable
          where empnum >= :empnum
          order by NEWNUM       -- uses the calculated value
          for update of empname 
        } for emp;
    onException
      myErrorHandler(8);        // exits the program
    end
     
    try
      get next emp;
    onException
      myErrorHandler(9);    // exits the program
    end
  
    while (emp not noRecordFound)
      try 
        replace emp
        with #sql{
          update :empTable
          set empname = :empname || ' ' || 'III'
        } from selectEmp;
  
      onException
        myErrorHandler(10); // exits the program 
      end
    
      try 
        get next emp;
      onException
        myErrorHandler(9);  // exits the program
      end
    end  // end while
  
    // no need to say "close emp;" because emp
    // is closed automatically when the last 
    // record is read from the result set or 
    // (in case of an exception) when the program ends
  
    sysLib.commit();

• Use the EGL open and forEach statements:

    try
  
      // The into clause is derived
      // from the SQL record and is based
      // on the columns in the select clause
      open selectEmp forUpdate 
        with #sql{
          select empnum, empname
          from empTable
          where empnum >= :empnum
          order by NEWNUM       -- uses the calculated value
          for update of empname 
        } for emp;
  
    onException
      myErrorHandler(8);        // exits the program
    end
     
    try
      forEach (from selectEmp) 
        
        try 
          replace emp 
          with #sql{
            update :empTable
            set empname = :empname || ' ' || 'III'
          } from selectEmp;
       
        onException
          myErrorHandler(9);  // exits program
        end 
  
      end   // end forEach statement, and there is
            // no need to say "close emp;" because emp
            // is closed automatically when the last 
            // record is read from the result set or 
            // (in case of an exception) when the program ends
  
    onException
      // the exception block related to forEach is not run if the condition 
      // is noRecordFound, so avoid the test "if (not noRecordFound)" 
      myErrorHandler(9);  // exits program
    end
  
    sysLib.commit();

Using EGL prepare statements

You have the option to use an SQL record part when coding the EGL prepare statement. Declare the following part:

  Record Employee type sqlRecord
    { 
      tableNames = employee,
      keyItems = empnum,
      defaultSelectCondition = 
        #sql{
          aTableColumn = 4 -- start each SQL comment 
                            -- with a double hyphen
        }
    }

    empnum  decimal(6,0) {isReadonly=yes};
    empname char(40);
  end

Declare variables:

  emp Employee;
  empnum02 decimal(6,0);
  empname02 char(40);
  myString char(120);

Adding a row to an SQL table

Before adding a row, assign values to variables:

  emp.empnum = 1;
  emp.empname = "John";
  empnum02 = 2;
  empname02 = "Jane";

Develop the SQL statement:

• Code the EGL prepare statement and reference an SQL record, which provides an SQL statement that you can customize:

    prepare myPrep
      from "insert into employee (empnum, empname) " +
      "values (?, ?)" for emp;
    
    // you can use the SQL record 
    // to test the result of the operation 
    if (emp is error)
      myErrorHandler(8);
    end

• Alternatively, code the EGL prepare statement without reference to an SQL record:

    myString = "insert into employee (empnum, empname) " +
      "values (?, ?)";  
  
    try
      prepare addEmployee from myString;
    onException
      myErrorHandler(8);
    end

In each of the previous cases, the EGL prepare statement includes placeholders for data that will be provided by an EGL execute statement. Two examples of the execute statement are as follows:

• You can provide values from a record (SQL or otherwise):

    execute addEmployee using emp.empnum, emp.empname;

• You can provide values from individual items:

    execute addEmployee using empnum02, empname02;

Reading rows from an SQL table

To prepare to read rows from an SQL table, identify a record key:

  empnum02 = 2;

You can replace multiple rows in either of these ways:

• Use the EGL open, while, and get next statements--

    myString = "select empnum, empname from employee " + 
               "where empnum >= ? for update of empname"; 
  
    try
      prepare selectEmployee from myString for emp;
    onException
      myErrorHandler(8);    // exits the program
    end
   
    try
      open selectEmp with selectEmployee 
        using empnum02 
        into emp.empnum, emp.empname;
    onException
      myErrorHandler(9);    // exits the program
    end
    
    try
      get next from selectEmp;
    onException
      myErrorHandler(10);   // exits the program
    end
  
    while (emp not noRecordFound)
                                      
      emp.empname = emp.empname + " " + "III";
  
      try 
        replace emp
          with #sql{
            update employee 
            set empname = :empname
          }
        from selectEmp;
      onException
        myErrorHandler(11); // exits the program
      end
  
      try
        get next from selectEmp;
      onException
        myErrorHandler(12); // exits the program
      end
    end  // end while; close is automatic when last row is read
  
    sysLib.commit();

• Use the EGL open and forEach statements--

    myString = "select empnum, empname from employee " + 
               "where empnum >= ? for update of empname"; 
  
    try
      prepare selectEmployee from myString for emp;
    onException
      myErrorHandler(8);    // exits the program
    end
   
    try
      open selectEmp with selectEmployee 
        using empnum02 
        into emp.empnum, emp.empname;
    onException
      myErrorHandler(9);    // exits the program
    end
    
    try
      forEach (from selectEmp)
        emp.empname = emp.empname + " " + "III";
       
        try 
          replace emp
            with #sql{
              update employee 
              set empname = :empname
            }
          from selectEmp;
        onException
          myErrorHandler(11); // exits the program
        end
      end // end forEach; close is automatic when last row is read
    onException
  
      // the exception block related to forEach is not run if the condition 
      // is noRecordFound, so avoid the test "if (not noRecordFound)" 
      myErrorHandler(12); // exits the program
    end
  
    sysLib.commit();