
<%@ page import="java.sql.*" %>
<%@ page import="sqlj.runtime.ref.DefaultContext,oracle.sqlj.runtime.Oracle" %>

<!------------------------------------------------------------------
 * This is a SQLJ JavaServer Page that does a SQLJ query on the
 * emp table in schema scott and outputs the result in an html table.
 *  
--------------------------------------------------------------------!>

<%! #sql iterator Empiter(String ename, double sal, java.sql.Date hiredate) %>

<%
   String connStr=request.getParameter("connStr");
   if (connStr==null) {
     connStr=(String)session.getValue("connStr");
   } else {
     session.putValue("connStr",connStr);
   }
   if (connStr==null) { %>
<jsp:forward page="../setconn.jsp" />
<%
   }
%>

<%
   DefaultContext dctx = null;
   dctx = Oracle.getConnection(connStr, "scott", "tiger");
%>

<HTML> 
  <HEAD> <TITLE> The SqljIterator SQLJSP </TITLE>  </HEAD>
 <BODY BGCOLOR="E0FFF0"> 
  <% String user;
	#sql [dctx] {SELECT user INTO :user FROM dual}; 
  %>

 <H1> Hello, <%= user %>!   </H1>
 <HR>
 <B> I will use a SQLJ iterator to get employee data
     from EMP table in schema SCOTT..
 </B> 
 <P>
<%  
    Empiter emps;
    try {
      #sql [dctx] emps = { SELECT ename, sal, hiredate 
 		    FROM scott.emp ORDER BY ename};
      if (emps.next()) {
%>
	<TABLE BORDER=1 BGCOLOR="C0C0C0">
	<TH WIDTH=200 BGCOLOR=white> Employee Name </TH>
	<TH WIDTH=100 BGCOLOR=white> Salary </TH>
	<TR> <TD> <%= emps.ename() %> </TD>
	     <TD> <%= emps.sal() %> </TD>
	</TR>

<%	while (emps.next()) {
%>
	<TR> <TD> <%= emps.ename() %> </TD>
	     <TD> <%= emps.sal() %> </TD>
	</TR>
<% } %>
      </TABLE>
<%  } else { %>
	<P> Sorry, the query returned no rows! </P>
<%    }
      emps.close();
    } catch (SQLException e) {  %>
       <P>There was an error doing the query:<PRE> <%= e %> </PRE> <P>
<%    } %>
 </BODY>
</HTML>
