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

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

  • Next message: Daniel Morgan: "Re: choices regarding where to place code - in the database or middletier" 
    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

  • Next message: Daniel Morgan: "Re: choices regarding where to place code - in the database or middletier"

    Relevant Pages

    • Re: choices regarding where to place code - in the database or middletier
      ... > written for any other database product: ... > of how you could use packages and sequences in Oracle and meet your ... Solutions that are completely dependent on the database's feature set: ... Build it with mod_plsql and Oracle HTTP Server ...
      (comp.lang.java.programmer)
    • RE: Mysql install help
      ... So the complexity of software is measured in the number of packages? ... something like Oracle CRS that is ... products" in the database. ... So you did not bother to read the automated installation instructions ...
      (Ubuntu)
    • Re: What so special about PostgreSQL and other RDBMS?
      ... That's exactly the link the licence agreement for the database points to when it ... comes to what wecan expect for paying support. ... > "Oracle may provide additional releases or versions of its programs ... If the requirements are volatile I'd do a long term contract detailing what ...
      (comp.lang.php)
    • [Full-disclosure] The history of a -probably- 13 years old Oracle bug: TNS Poison
      ... tl;dr -> Patch your database ASAP with Oracle Critical Patch Update ... which is the responsible of connections establishment. ... Instances to register under the specified service name. ...
      (Full-Disclosure)
    • The history of a -probably- 13 years old Oracle bug: TNS Poison
      ... tl;dr -> Patch your database ASAP with Oracle Critical Patch Update ... which is the responsible of connections establishment. ... Instances to register under the specified service name. ...
      (Bugtraq)