• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Sun's feelings about stored procedures

 
Siegfried Heintze
Ranch Hand
Posts: 408
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
What is sun's feelings about stored procedures? Someone in our study group had a sample test question about improving maintainability by moving business logic out of the database into the middle tier.
I presume that busines logic in the database tier means stored procedures. Microsoft and other vendors and magazine article authors promote the use of stored procedures because
(1) They can be compiled and are therefor faster
(2) The improve maintanance because all database programmers can access them
(3) They are not specific to Java or FORTRAN or C# etc...
I notice that when JBoss fires up with the default RAM based database, it recreates the tables each time by executing the IDL produced by the deployement descriptors for the CMP Beans in the samples. One does not have to write any DDL! Is this unique to JBoss or common to all EJB/CMP implementations?
If it is common, then is SUN (and, more importantly, the folks grading my J2EE arch exam) promoting EJB/CMP as a substitute for stored procedures because EJB/CMP is database vendor neutral?

Thanks,
Siegfried
 
Thomas Hofmann
Ranch Hand
Posts: 72
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I presume that busines logic in the database tier means stored procedures.
That's correct!
One does not have to write any DDL! Is this unique to JBoss or common to all EJB/CMP implementations?
Weblogic and I think all mature application server with ejb2.0/cmp containers provide also means to create the database tables according to vendor specific CMP deployment descriptors. But it is not recommended in production mode because the resulting db schema is not optimized. So better let create your tables by experienced DBAs.

If it is common, then is SUN (and, more importantly, the folks grading my J2EE arch exam) promoting EJB/CMP as a substitute for stored procedures because EJB/CMP is database vendor neutral?
That is the decision of the architect (YOU!!). If the application requires hihg performance and EJB/CMP cannot guarantee it, so you have to switch to EJB/BMP/JDBC and perhaps make (carefully) use of stored procedures. But you must have a good rationale for this. You have to make a tradeoff: peformance vs. maintainabiliy/portability/db vendor neutrality.
Thomas
 
Michael Zalewski
Ranch Hand
Posts: 168
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Some databases can have stored procedures written in Java.
For example, Oracle, DB2, and PointBase.
I like to make a distinction between business logic and relational integrity logic. I would prefer to use a stored procedure to enforce integrity logic. But I would prefer an EJB (or some other form of Java object) to enforce the business logic. Here I am speaking of integrity in a wider sense than just a foreign key constraint.
For example, a sales order might be in the database as two tables. One table contains an order header row, and the other contains one row for each detail line of the order.
To enforce an integrity rule, such as assigning a sequential entry number to each row in the detail table for a given order, I prefer to use a stored procedure. This is a 'wider sense' of integrity constraint that cannot be expressed with DDL. I want a column in the order detail called 'line number', where the first detail line on the order gets assigned a value of 1, the next detail line gets assigned a value of 2, and so on. Using a stored procedure to add the detail lines, I can be sure that the line numbers are always properly assigned.
But to enforce a business rule, such as which customers must be charged tax, and at what sales tax rate, might require a Java object.
When the data model changes (for example, say I need to add a new type of Order Detail line to describe consulting services. So I add a second detail table, and now I want the line numbers assigned across each table. If the first line is a service, I write a Service Detail with line number 1. If the second line is a product, I write a Product Detail with line number 2.) -- I need to change the stored procedures to make sure the database state is consistent.
When the business model changes (for example, the tax laws change, or I expand to the point where I need to charge different tax rates for different orders), I need to change the business object written in Java.
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic