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:
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
emp Employee;
For further details on SQL records and implicit statements, see SQL support.
To prepare to code SQL statements, declare variables:
empnum decimal(6,0); empname char(40);
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
To prepare to read rows from an SQL table, identify a record key:
empnum = 1;
To get rows, code a series of EGL statements:
open selectEmp with #sql{ select empnum, empname from employee where empnum >= :empnum for update of empname } into empnum, empname;
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.
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
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;
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
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:
try get emp; onException myErrorHandler(8); end
try get emp singleRow; onException myErrorHandler(8); end
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();
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);
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
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:
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
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 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();
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);
Before adding a row, assign values to variables:
emp.empnum = 1; emp.empname = "John"; empnum02 = 2; empname02 = "Jane";
Develop the SQL statement:
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
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:
execute addEmployee using emp.empnum, emp.empname;
execute addEmployee using empnum02, empname02;
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
(C) Copyright IBM Corporation 1992, 2005. All Rights Reserved.