Sample EGL SQL program

The following complete sample EGL programs use an SQL database. Adapt any sections of this code to your own application.

SQLBatch.egl

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   

SQLCreateTable.egl

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
   

SQLDropTable.egl

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   

Feedback