Re: choices regarding where to place code - in the database or middletier

From: Stu Charlton (stuartc_at_mac.com)
Date: 01/30/04 

Date: 29 Jan 2004 16:57:08 -0800

Daniel Morgan <damorgan@x.washington.edu> wrote in message news:<1075402857.512884@yasure>...

> Please acknowledge that once your code is in packages and you are using
> sequences ... the code absolutely can not be compatible with code
> written for any other database product: No exceptions. It just will not
> be compatible and no quantity of bubble gum, paper clips, or rubber
> bands is going to make it compatible.

Acknowledged.

> So while I will gladly acknowledge a communication gap I still can't get
> past thinking what you guys are proposing is a logical impossibility. If
> you disagree, and I suspect you do/will, then please provide an example
> of how you could use packages and sequences in Oracle and meet your
> stated objective.

Ok.

Requirement: A web-page that displays all of the employees in a given
department.

Solutions that are completely dependent on the database's feature set:
a. Build it with Marvel / HTML DB
b. Build it with mod_plsql and Oracle HTTP Server

Solutions that while still dependent on the database's feature set
(i.e. packages and stored procedures), it uses another technology for
its presentation logic:
a. Build it with mod_perl
b. Build it with JSP
c. Build it with ASP.NET

So, here's an example with ASP.NET (which I actually took and modified
into C# .NET from http://osi.oracle.com/~tkyte/ResultSets/index.html).
 It's untested pseudocode, so probably has minor bugs in the ASP page.

CREATE OR REPLACE
PACKAGE DEPARTMENT AS
TYPE  CURSOR_TYPE IS REF CURSOR;
PROCEDURE GET_EMPS (I_DEPTNO     IN  NUMBER,
                O_RESULT_SET OUT CURSOR_TYPE);
END;
/

CREATE OR REPLACE
PACKAGE BODY DEPARTMENT AS
PROCEDURE GET_EMPS (I_DEPTNO     IN  NUMBER,
                     O_RESULT_SET OUT CURSOR_TYPE)
AS
  BEGIN
      OPEN O_RESULT_SET FOR
      SELECT EMPNO, ENAME
      FROM EMP
      WHERE DEPTNO = I_DEPTNO;
  END;
END;

employees.aspx:

<%@ Page language="c#" %>
<%@ Import Namespace="System.Data" %>
<html>
<body>
 <h2>Query Employees in Department Code</h2>
 <form id="Form1" method="post" runat="server">
   <asp:textbox id="DeptCode" runat="server"/>
   <asp:button id="Submit" runat="server" Text="Run Query"/>
    <asp:DataGrid id="MyDataGrid" runat="server" width="800" />
</form>
 <script language="c#" runat="server">
          void Page_Load(object src, EventArgs e) {
                   Submit += new System.EventHandler( RunQuery );
          }
          void RunQuery(object src, EventArgs e) {
                 OracleConnection conn = new OracleConnection();
                 OracleCommand cmd = new OracleCommand();
                 conn.ConnectionString = "data
source="oracle.server;uid=scott;password=tiger";
                 cmd.Connection = conn;
                 cmd.CommandText = "SCOTT.DEPARTMENT.GET_EMPS";
                 cmd.CommandType = CommandType.StoredProcedure;
                 OracleParameter i_deptno =
                    cmd.Parameters.Add("I_DEPTNO",
OracleClient.OracleType.Number);
                 i_deptno.Direction = ParameterDirection.Input;
                 OracleParameter o_resultset =
                 cmd.Parameters.Add("O_RESULT_SET",
OracleClient.OracleType.Cursor);
                 o_resultset.Direction = ParameterDirection.Output;
                 conn.Open();

                 i_deptno.Value = Int32.Parse ( DeptCode.Text );
                 OracleDataReader results = cmd.ExecuteReader();
                 MyDataSet.DataSource = results;
                 MyDataSet.DataBind();
          }
 </script>
</body>
</html>

The whole point of this is that we *didn't* use the Oracle-built in
features of HTML DB or mod_plsql, even though many successful people
CAN do so. It's really a matter of tradeoff of available skills,
future maintenance, and performance. In this case, I don't really
think the ASP.NET approach is any less scalable than Oracle-only
approaches.

The problem that too many Java or .NET folks cause is that they
re-invent database features all the time in the misguided attempt to
be "generic" because they don't want to be tied to their database
economically. This is not appropriate and is usually too drastic a
trade-off.

Anyhow, I can't speak for Joe, but this is my understanding of the
viewpoint.

Cheers
Stu

Quantcast