package examples.htmlkona;

import java.io.*;
import javax.servlet.*;
import javax.servlet.http.*;
import java.sql.*;
import weblogic.db.jdbc.*;
import weblogic.html.*;

/**
 * This servlet shows how to use JDBC stored procedures to
 * carry out tasks, the results of which you display on a page.
 * 
 * @author Copyright (c) 1996-98 by WebLogic, Inc. All Rights Reserved.
 * @author Copyright (c) 1999-2000 by BEA Systems, Inc. All Rights Reserved.
 */
public class StoredProcResults extends HttpServlet {

  public synchronized void service(HttpServletRequest req, HttpServletResponse res) 
    throws IOException
  {
    Connection conn = null;

    try {
      res.setStatus(HttpServletResponse.SC_OK);
      res.setContentType("text/html");
    
      conn = defaults.login();

      java.sql.Statement stmt = conn.createStatement();

      // Define a stored procedure with an IN/OUT parameter
      stmt.execute("CREATE OR REPLACE PROCEDURE proc_squareInt (field1 IN OUT INTEGER) IS  " +
                   "BEGIN field1 := field1 * field1; END proc_squareInt;");

      java.sql.CallableStatement sp = conn.prepareCall("BEGIN proc_squareInt(?); END;");

      String procstr = "";

      for (int i = 5; i < 13; i++) {

        // Set the parameter value
        sp.setInt(1, i);

        // Execute the stored procedure
        sp.execute();

        // Get the parameter value
        int afterval = sp.getInt(1);

        procstr += i + " / " + afterval + "\n";
      }

      sp.close();

      // Define a function and return value
      stmt.execute("CREATE OR REPLACE FUNCTION func_squareInt (field1 INTEGER) " +
                    "RETURN INTEGER IS " +
                    "BEGIN return field1 * field1; END func_squareInt;");

      // Get a handle to the stored procedure
      java.sql.CallableStatement func = conn.prepareCall("BEGIN ? := func_squareInt(?); END;");

      String funcstr = "";

      for (int i = 5; i < 13; i++) {

        // Register the return vale
        func.registerOutParameter(1, java.sql.Types.INTEGER);

        // Set the parameter value
        func.setInt(2, i);

        // Execute the function
        func.execute();

        // Get the function return value
        int afterval = func.getInt(1);

        funcstr += i + " / " + afterval + "\n";
      }

      ServletPage hp = new ServletPage("Displaying stored proc results");
      hp.getBodyElement()
	.setAttribute(BodyElement.bgColor, HtmlColor.white);
      hp.getBody()
        .addElement(MarkupElement.HorizontalRule)
        .addElement(new HeadingElement("Parameters before/after stored procedure execution:" , 2))
        .addElement(new LiteralElement(procstr))
        .addElement(MarkupElement.HorizontalRule)
        .addElement(new HeadingElement("Parameters and return values of function execution:" , 2))
        .addElement(new LiteralElement(funcstr))
        .addElement(MarkupElement.HorizontalRule)
	.addElement("Copyright 1996-2000 by BEA Systems, Inc. All Rights Reserved.");
      hp.output(res.getOutputStream());
    }
    catch (Exception e) {
      defaults.showException(e, res.getOutputStream());
    }

    // Always close the connection in a finally block.
    finally {
      try {conn.close();} catch (Exception e2) {;}
    }
  }
}

