EGL Reference Guide for iSeries

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 rows from an SQL table

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

  empnum = 1; 

To get rows, code a series of EGL statements:

The following code updates SQL 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(8);    // 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

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:

Process multiple rows by using the EGL open and get next statements:

  handleHardIOErrors = 1;
 
  try
    open selectEmp forUpdate for emp;
  onException
    myErrorHandler(8);    // 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();

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:

Process multiple rows by using the EGL open and get next statements:

  try
 
  // The into clause is derived
  // from the SQL record and is based
  // on the columns in the select clause
    open selectEmp forUpdate for emp 
      with #sql{
        select empnum, empname
        from empTable
        where empnum >= :empnum
        order by NEWNUM       -- uses the calculated value
        for update of empname 
      }
  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'
      }
 
    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();

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:

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:

Reading rows from an SQL table

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

  empnum02 = 2;

The next example replaces multiple rows:

  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


[ Top of Page | Previous Page | Next Page | Table of Contents | Index ]