SQL examples

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

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:

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();