The following complete sample EGL programs use an SQL database. Adapt any sections of this code to your own application.
package com.CompanyB.CustomerPackage;
program SQLBatch type basicProgram
dept Department;
function main()
try
clearTable();
loadTable();
getRow();
updateRow();
getAllRows();
forceRecordNotFound();
forceSQLException();
onException (ex SQLException)
sqlFailure();
onException (ex AnyException)
anyFailure( ex );
end
end
function getRow( );
setVariation ( "Get row from department table" );
getDept("D100");
end
function updateRow();
set dept empty;
setVariation ( "Update department table" );
dept.id = "T100";
get dept forUpdate;
if ( dept is noRecordFound )
sqlFailure();
else
dept.description = "Test Engineers";
replace dept;
commit();
end
getDept( "T100" );
end
function getAllRows()
setVariation ( "Get all departments" );
dept.id = " ";
open deptSet for dept;
foreach ( dept )
showDept();
end
end
function clearTable()
setVariation ( "Clear all rows from department table" );
dept.id = " ";
execute delete
#sql{
delete from DEPARTMENT_TABLE
where
id >= :dept.id
} for dept;
commit();
end
function forceRecordNotFound()
setVariation( "Try to get a department that is not in the table");
getDept("B100");
end
function forceSQLException()
setVariation("Force an sql exception by dropping a non-existent table");
prepare dropStmt from "DROP TABLE NOT_A_TABLE";
execute dropStmt;
end
function loadTable();
setVariation ( "Load department table" );
addDept("A100","Architects","AM0001");
addDept("D100","Developers","DM0001");
addDept("T100","Testers","TM0001");
addDept("M100","Managers","MM0001");
commit();
end
function addDept( deptNo string, desc String, manager string )
dept.id = deptNo;
dept.description = desc;
dept.manager = manager;
add dept;
end
function getDept( id string );
set dept empty;
dept.id = id;
get dept;
showDept();
end
function showDept()
if ( dept is noRecordFound )
writeStdOut ( "Department " + dept.id + " not found." );
else
writeStdOut( "Department " + dept.id +
" description: " + dept.description );
end
end
function setVariation ( desc string )
writeStdOut ( "Variation = " + desc );
end
function sqlFailure()
writeStdOut ( "SQL Exception, SQL code =
" + sysVar.sqlData.sqlCode
+ ", SQL state: " + sysVar.sqlData.sqlstate );
rollback();
end
function anyFailure( ex AnyException )
writeStdOut ( "Unexpected Exception, Error code = " + ex.messageID );
writeStdOut ( ex.message );
rollback();
end
end
package com.CompanyB.CustomerPackage;
program SQLCreateTable type basicProgram
dept Department;
function main()
try
execute #sql{
create table "DEPARTMENT_TABLE" (
id char(4) not null,
description varchar(30),
manager char(6) not null,
primary key(id))
};
writeStdOut( "DEPARTMENT_TABLE created");
onException (ex SQLException)
writeStdOut ( "Create DEPARTMENT_TABLE failed"
+ ", Unexpected SQL Exception, SQL code = "
+ sysVar.sqlData.sqlCode
+ ", SQL state: " + sysVar.sqlData.sqlstate );
end
end
end
Record Department type SQLRecord {
tableNames = [["DEPARTMENT_TABLE"]] ,
keyItems = [id]
}
id string { maxLen = 4 };
description string { isSQLNullable = YES };
manager string ;
end
package com.CompanyB.CustomerPackage;
program SQLDropTable type basicProgram
variation string;
function main()
try
execute #sql{ drop table "DEPARTMENT_TABLE" };
onException (ex sqlException)
end
if ( sysVar.sqlData.sqlState == "00000" // success
|| sysVar.sqlData.sqlState == "42704" ) // table didn't exist
writeStdOut( "DEPARTMENT_TABLE dropped");
else
writeStdOut ( "Drop DEPARTMENT_TABLE failed"
+ ", Unexpected SQL Exception, SQL code = " + sysVar.sqlData.sqlCode
+ ", SQL state: " + sysVar.sqlData.sqlstate );
end
end
end