Using Stored Procedures

The Valentina DB engine supports Stored Procedures implemented mainly by SQL standard. As stored procedure statements are stored directly in the database, they may remove compiling overhead that is typically needed in situations where software applications send inline (dynamic) SQL queries to a database.

You can see the description of supported SQL commands in the Valentina SQL Reference.

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.

There are significant benefits from using stored routines.

A primary benefit is that since the routines are stored in the database, it reduces network traffic between Valentina CLIENT and Valentina Server. It lets you build cursors, iterate records and calculate value(s) entirely on the server. If you use for this an ADK API by way of Valentina CLIENT, then many calls to API may go by network to Valentina Server when you load the next record of a cursor. The network itself can be the primary bottleneck of client-server applications.

If you use stored procedures instead, then you get almost no network traffic for this calculation and get back only the result.

If you use different front end platforms (such as C# or PHP) then you can get stuck having to implement the same algorithm in both languages. Instead you can implement it as stored procedure, and now both applications have access to the same algorithm within the database.

You can use a Trigger of some time to invoke a Stored Procedure. This means that you can develop a complex behavior of a database that is triggered on some data changes. This will work automatically even if you DELETE/INSERT/UPDATE a record using Valentina Studio.

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().

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