Switch to: V9V8V7V6V5

Using Stored Procedures

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

Brief Description

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:

Why to use Stored Routines?

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.

Return cursor from SP

Usually, we tend to write queries in our apps directly, but this way makes our app not-flexible (in case of changing db structure or when we need to refactor some queries). Also, another app has to reinvent the logic of querying this db (and do it differently, wrongfully…).

There is a common way to solve such problems, having “business logic” inside of the database, and Stored Procedures could be used for this purpose.

Example:

CREATE PROCEDURE sp1( IN inValue long ) 
BEGIN 
    SELECT * FROM t1 WHERE f1 = inValue;
END;

Now you can call this procedure in a bit special but logical way:

I_Cursor pCursor = db.SqlSelect( "CALL sp1(10)" );

In this case, SP will create a cursor (for select statement inside) and this cursor will be returned by db.SqlSelect().

TIP(s)

  • 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.