Re: choices regarding where to place code - in the database or middletier
Date: 29 Jan 2004 16:57:08 -0800
Message-ID: <21398ab6.0401291657.7ad25c50_at_posting.google.com>
Daniel Morgan <damorgan_at_x.washington.edu> wrote in message news:<1075402857.512884_at_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.
[Quoted] 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:
<%_at_ Page language="c#" %> <%_at_ 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
Received on Fri Jan 30 2004 - 01:57:08 CET