Using Stored Procedures

Valentina 3.0 engine introduces Stored Procedures implemented mainly by SQL standard. You can see the description of SQL commands here.

A stored routine is either a procedure or a function. Stored routines are created with CREATE PROCEDURE and CREATE FUNCTION statements.

A procedure is invoked using CALL statement. A Function can be called just by name from an SQL statement like any other function.

A procedure can pass back values using output parameters only. A function can have only input parameters and returns a value.

Stored routines are stored inside of a database file.

The main difference between Valentina's stored routines and the SQL standard is how errors are handled. Although standard suggests CONDITIONS and ERROR HANDLERS, Valentina does not implement them because they are quite confusing and hard for understanding. Instead Valentina offers a simple tool - EXCEPTIONS that work in the way similar to many well known programming languages.

In Valentina SQL you can use for Stored Procedures:

• Flow Control statements: IF, CASE.
• LOOP statements.
• EXCEPTIONS.
• Cursor statements: DECLARE, OPEN, FETCH, CLOSE.
• Variables: local-scope, connection-scope and system-scope.
  • Mapping fields of single-row-SELECT into variables.
• EXECUTE a query produced by Stored Procedure → meta-programming.
  • EXECUTE a query with binding of variables.

The main reasons are:

• Reduce network traffic between VServer and VClient

therefore you get faster work. Really, let you need build some cursor and iterate its records and calculate some value(s). If you use for this task some ADK API, then many calls to API may go by network to VServer, e.g. when you load next record of a cursor. It is known that network is the main bottleneck of client/server apps. If, instead you write this in stored procedure, then you get ZERO move by network for this calculation and get back the result only.

• Business Logic is inside of database.

This may help in different cases.

• Let you develop solution using few programming languages, e.g. C# and Adobe Director, and both applications need above calculation. This means that you need implement the same algorithm in both languages - way to the hell… Instead you can implement it as stored procedure, and now both applications have access to the same algorithm.

• Stored Procedure can be invoked from some Trigger. This means that you can develop complex behavior of a database on some data changes. And magic is that all this will work automatically even if you DELETE/INSERT/UPDATE a record via e.g. Valentina Studio.

• It is possible to create a stored procedure and test its functionality without Valentina Server, i.e. working with LOCAL database using some ADK.

• Never test your new stored procedure on real database without having a backup copy first.

• Use the PRINT command to print messages into Warning.log file to debug your stored procedure.