Stored Procedures Guide

Stored Procedures are database specific SQL queries. A stored procedure has a name, SQL text and zero or more arguments.

They were originally designed to abstract all database specific query language away from the core code and into configuration files allowing Platform 6 to support multiple database types.

Stored Procedures can be public or private.

A public stored procedure is visible in the service’s user interface whereas a private stored procedure is for internal Platform 6 processing only. For more information about the user interface, read the guide Stored Procedures user interface.

More recently a requirement has arisen to allow maintenance of User defined stored procedures. A user defined stored procedure is one that is created via the service’s user interface and stored in the Platform 6 database. Because they are database specific, they are not portable between databases of different types and are therefore project specific (i.e. they will not be automatically available on another Platform 6 installation).

Because of the newer User defined stored procedures, the traditional stored procedures defined in external configuration files are now referred to as System stored procedures.

Query syntax

SQL text can use replacement tokens that will be evaluated before the stored procedure is run. A replacement token has the syntax: ${name}.

The name schema may be used and will be auto-replaced with the database schema name.

All other named tokens will be assumed to be stored procedure arguments; values that must be supplied when executing the stored procedure.

Argument type hints

These exist to all Platform 6 to perform basic argument validation before submitting the resulting stored procedure expression to the database engine.

Hints are defined using basic Java classes: java.lang.String, java.lang.Long.

System stored procedures

A System stored procedure is defined to query a well know Platform 6 storage entity: item, failed routing orders, completed routing orders etc. As such the definition of each stored procedure is defined together will all other attributes that define a Platform 6 storage entity.

Definition is using Spring bean XML configuration files.

Most are embedded in the b2box-dao.jar however some have been externalised in the b2box/conf folder:

• PGSQL-ItemContext.xml

Where configuration is database specific the database dialect is used to prefix the filename.

In general, these files are best maintained via Platform 6 R&D and will be version controlled and embedded in all product releases.

User defined stored procedures

A User defined stored procedure is not associated with a particular Platform 6 storage entity. In fact it can query any table in the Platform 6 database.

Note

Because a User defined stored procedure has no way to define argument hints, all ${name} replacement tokens are assumed to be of type java.lang.String.

One additional syntax is available: ${#name} which will assume the argument has a hint type of java.lang.Long.

Scripting

All stored procedures both private and public can be called via the sproc Groovy DSL.

def llstr = p6.sproc.execute("MyStoredProc", [param1: value1])