<%@ page language="java" contentType="text/html" %>
<%@ taglib uri="/orataglib" prefix="ora" %>

<ora:useDataSource id="example" 
  className="sun.jdbc.odbc.JdbcOdbcDriver" 
  url="jdbc:odbc:example" />

<%-- 
  See if the employee is already defined. If not, insert the
  info, else update it.
--%>
<ora:sqlQuery id="empDbInfo" dataSource="example">
  SELECT * FROM Employee 
    WHERE UserName = ?
  <ora:sqlStringValue param="userName" />
</ora:sqlQuery>

<% if (empDbInfo.size() == 0) { %>

     <ora:sqlUpdate dataSource="example">
       INSERT INTO Employee 
         (UserName, Password, FirstName, LastName, Dept, 
           EmpDate, EmailAddr, ModDate)
         VALUES(?, ?, ?, ?, ?, ?, ?, ?)
       <ora:sqlStringValue param="userName" />
       <ora:sqlStringValue param="password" />
       <ora:sqlStringValue param="firstName" />
       <ora:sqlStringValue param="lastName" />
       <ora:sqlStringValue param="dept" />
       <ora:sqlDateValue param="empDate" pattern="yyyy-MM-dd" />
       <ora:sqlStringValue param="emailAddr" />
       <ora:sqlTimestampValue value="<%= new java.util.Date() %>" />
     </ora:sqlUpdate>

<% } else { %>

     <ora:sqlUpdate dataSource="example">
       UPDATE Employee
         SET Password = ?, 
             FirstName = ?, 
             LastName = ?, 
             Dept = ?,
             EmpDate = ?,
             EmailAddr = ?,
             ModDate = ?
         WHERE UserName = ?
       <ora:sqlStringValue param="password" />
       <ora:sqlStringValue param="firstName" />
       <ora:sqlStringValue param="lastName" />
       <ora:sqlStringValue param="dept" />
       <ora:sqlDateValue param="empDate" pattern="yyyy-MM-dd" />
       <ora:sqlStringValue param="emailAddr" />
       <ora:sqlTimestampValue value="<%= new java.util.Date() %>" />
       <ora:sqlStringValue param="userName" />
     </ora:sqlUpdate>

<% } %>

<%-- Get the new or updated data from the database --%>
<ora:sqlQuery id="newEmpDbInfo" dataSource="example" scope="session">
  SELECT * FROM Employee 
    WHERE UserName = ?
  <ora:sqlStringValue param="userName" />
</ora:sqlQuery>

<%-- Redirect to the confirmation page --%>
<ora:redirect page="confirmation.jsp" />