The Business Rules Engine

Procedures

Introduction

Procedures are named blocks of procedural logic that can be executed in an ad hoc manner with the EXEC PROCEDURE statement. They can have parameters that are of any supported type, and can also include arrays. They can also have return values.

Coda has a somewhat unique approach to stored procedures. The definition of the procedure is very much like a SQL stored procedure, but the body of the procedure is written in a JVM-based scripting language called Groovy. This approach was chosen for several reasons.

  • Groovy is well thought out and documented, and there is no point in reinventing the wheel
  • It allows access to all of the standard Java libraries, and provides a very slick interface into them
  • This approach opens the door to providing other JVM-based language options in the future
  • It speeds up the time-to-market for Coda

Predictably, there are a few "gotchas" to this integration. Some of the most important ones:

  • Coda is case-insensitive and Groovy is not. Within the procedure, Coda attempts to camel-capitalize type names and variable names; a type called email_address becomes EmailAddress, and a variable called user_id would become userId.
  • Access to the database is provided through an object called "database." Only queries and DML statements can be run from within a procedure.
  • All commits must be explicit; there is no autocommit.
  • There is another object called "utility" that provides access to many handy utility functions such as Vector-to-Coda Array conversions.
  • Create or Replace Procedure

    create_procedure := [ CREATE | REPLACE ] PROCEDURE <procedure_name> 
                        [ IN <application_name> ]?
                        [ ( <procedure_parameter> [ , ... ]* ) ]? 
                        RETURNS <type_name> [ ARRAY ]? AS
                        <procedure_body> ENDPROCEDURE
    procedure_parameter := <parameter_name> <type_name> [ ARRAY ]?
    

    Creates or replaces a stored procedure. Creating will NOT overwrite an existing procedure if the name is already in use, and replace will. The application name is only required if one is not currently set in the session.

    Procedures can take parameters of any type, and can also take arrays. Return values can also be of any type, including RESULTSET for cursor-like values.

    The procedure body contains Groovy code. Documentation for the language can be found here. Once saved, the procedure definition is used to create a method within a Java class representing the procedure. The body of the procedure is also the body of the method, and the parameters of the procedure become the arguments of the method. Relevant objects like the "database" object are initialized before the procedure body is run.

    Alter Procedure

    alter_procedure := ALTER PROCEDURE [ <application_name> . ]? <procedure_name>
                       RENAME TO <new_procedure_name>
    

    Renames a procedure.

    Drop Procedure

    drop_procedure := DROP PROCEDURE [ <application_name> . ]? <procedure_name>
    

    Drops a procedure permanently.