Procedures

Posted February 19th, 2008 by mike

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.

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.

Login

justCODASERVED

CodaServer is a business rules engine; similar to a database, but specialized to make building form-based business applications simple. As your processes change, your software can incorporate these changes automatically without recoding.