0 Replies Latest reply on Dec 10, 2002 8:08 AM by Shai Almog

    CMP stored procedure

    Shai Almog Newbie

      I've been programming J2SE since 96 and J2EE for the past
      3 years and I've had an idea for quite some time that no
      one seems to have brought up:

      Stored procedures are evil mostly due to maintainance and
      portability issues, however in "real life" projects we
      need them to minimize the database hit. Caching can solve
      some things but not with all deployments. Furthermore some
      things are just more elegant as a stored procedure i.e.:

      We have a logging system that is completely based on
      database triggers and doesn't require us to write code
      inside every entity bean that interfaces with the database.


      Anyway J2EE can solve these problems. Since most major
      databases support stored procedures in Java (even if they
      use a different API an adapter can be used to unify a
      subset of their features). We can present an interface to
      the user which they can implement as the stored procedure.
      The schema on which it will be applied can be indicated in
      the deployment descriptor and the ejb home can define
      a method to invoke the stored procedure (if it is not a
      trigger).

      Problems I forsee are:

      1. Deployment will get "tricky" - since deploying a stored
      procedure isn't automatic JBoss will probably have to
      generate the code for the database and instructions
      for the DBA to deploy the procedure... Ugly at first but
      I think that this will encorage database vendors to work
      on a unified stored procedure deployment API.

      2. Triggers invoking EJB's - this could cause a problem
      since the database's internal VM may be different from
      the VM of the bean. This caused a problem for us under
      websphere which relies on an IBM VM for IIOP and Oracle
      that has an internal VM.

      I think that a lot of ejbhome methods will migrate to
      stored procedures and significantly boost the performance
      of J2EE.

      I'd like feedback from JBoss developers (would be glad to
      help with the implementation myself with the little time
      I have) about feasability and issues. If this can be made
      to work I have no doubt this will make it into J2EE as
      well.