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); endReading 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); endDeclare 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); endReading 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" }; endDeclare 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); endReading 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); endDeclare 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();