Interesting RESIGNAL example

Following example is given in MySQL manual to demonstrate that RESIGNAL doesn't need to be defined inside a HANDLER, but merely require an active HANDLER. EffiProz RESIGNAL implementation happens to support it exactly as MySQL.

This example also shows EffiProz Query Tool's support for delimiter command. Default delimiter is set to back slash character "\".

MySQL style CONDITION declarations in Stored Procedures

EffiProz supports MySQL style CONDITION declarations. CONDITION declarations can later reference in HANDLER, SIGNAL and RESIGNAL statements. In EffiProz local variables, cursors and conditions share the same namespace.

Example - Condition declaration with HANDLER declaration,



Example - Condition declaration with SIGNAL statement,

Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip

RESIGNAL statement in Stored Procedures

RESIGNAL statement can be used alone to re-throw current signal as it is. Or RESIGNAL statement can be used with SQLSTATE value and/or signal information items to throw a new signal. RESIGNAL statement is required to be inside an active signal handler.

Example - RESIGNAL statement alone,



Example - RESIGNAL statemet with SQLSTATE value

Example - Active HANDLER requirement for RESIGNAL statement

SIGNAL statement in Stored Procedures

EffiProz supports standard SQL/PSM SIGNAL statement in stored routines. But EffiProz only recognize MESSAGE_TEXT and EFFIPROZ_ERRNO condition information items.

Following example shows how errors and warnings can be signaled,

Following example further demonstrates how Warnings are handled,

HANDLER Declarations in Stored Procedures

EffiProz Supports CONTINUE, EXIT and UNDO handler types. Handler condition can be,

• SQLSTATE [VALUE] sqlstate
• SQLWARNING
• NOT FOUND
• SQLEXCEPTION
• effiproz_error_code

Example of a NOT FOUND condition was given in http://blog.effiproz.com/2010/04/sql-cursors.html

Following is an example CONTINUE HANDLER that uses a SQLSTATE condition,

Following is the same example as above but uses a effiproz_error_code condition instead of SQLSTATE,

Following example shows an EXIT HANDLER inside a nested block,

SELECT ... INTO statement with LIMIT clause

You can use standard SELECT ... INTO statement if the query returns a single row. This is shown in example 1 below. If the query returns more than one row, like MySQL, you can use LIMIT clause to limit result set to one row as shown in example 2.

Example 1,

Example 2:

WHILE and REPEAT statements

EffiProz supports standard SQL/PSM WHILE and REPEAT statements in Stored Procedures/Functions.
Example 1 demonstrate the WHILE statement and Example 2 shows the same example using REPEAT statement.

Example 1:

Example 2:

Session User Variables as OUT parameters to Stored Procedures

Session user variables can be used as IN, OUT or INOUT parameters to Stored Routines.

Following example shows how to use two user variables as OUT parameters to a stored procedure. It also demonstrates EffiProz's MySQL style DATEDIFF function and arithmetic function FLOOR.

Resolution of Local Variables

In effiproz, Column references are resolved before local variable references are resolved. Therefore if you have a local variable with the same name as a column name in a select statement, then the column takes precedence over the local variable.


his is illustrated in the following example,

Database Engine Code Coverage

Currently we have about 70% code coverage and we have set a goal of 80% code coverage for 1.1 release.
We run each regression test case 3 times:

•  With an In-Memory database
•  With a Persistent database to test regular recovery
•  With a Persistent database to test transaction log based recovery.

Below figure shows latest code coverage figures,

IF ... ELSEIF ... ELSE statement in Stored Procedures

Following are three examples that demonstrate the capabilities of IF statement. They also illustrate the use of MySQL style CONCAT function that can take unconstrained number parameters.

Example 1:

Example 2:

Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip

CASE ... WHEN statement in Stored Procedures

EffiProz supports both simple CASE statement and search CASE statement in stored routines.
following example demonstrates the simple CASE statement,

Following example shows the usage of search CASE statement,

ADO.NET provider support for multiple result sets

Following example shows how EfzDataReader (DbDataReader) can be used to fetch multiple result sets generated by a stored procedure. Use DbDataReader's NextResult method to iterate through result sets.

static void Main(string[] args)
        {
             string connString = "Connection Type=Memory ;auto shutdown=false; Initial Catalog=MultiStepReaderTest2; User=sa; Password=;";
            using (EfzConnection _cnn = new EfzConnection(connString))
            {
                _cnn.Open();
                using (DbCommand cmd = _cnn.CreateCommand())
                {              
                    cmd.CommandText = "create table animals(id int, name varchar(100)); create table vehicals(id int, name varchar(100));";
                    cmd.ExecuteNonQuery();
                   
                    cmd.CommandText = @"insert into animals values (1,'cat');insert into animals values (2,'dog');
                                        insert into vehicals values (1,'car');insert into vehicals values (2,'bus');";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"create procedure selecttest2() BEGIN SELECT * FROM animals; SELECT * FROM vehicals; END;";
                    cmd.ExecuteNonQuery();

                    cmd.CommandText = @"CALL selecttest2();";

                    using (DbDataReader reader = cmd.ExecuteReader())
                    {
                        Console.WriteLine("Result Set 1");
                        reader.Read();
                        Console.WriteLine("id={0} , name={1}", reader.GetInt32(0), reader.GetString(1));
                        reader.Read();
                        Console.WriteLine("id={0} , name={1}", reader.GetInt32(0), reader.GetString(1));
              
                        reader.NextResult();
                        Console.WriteLine("");
                        Console.WriteLine("Result Set 2");
                        reader.Read();
                        Console.WriteLine("id={0} , name={1}", reader.GetInt32(0), reader.GetString(1));
                        reader.Read();
                        Console.WriteLine("id={0} , name={1}", reader.GetInt32(0), reader.GetString(1));                                         
                    }                 

                }

                _cnn.Shutdown();
            }

            Console.ReadKey();
        }

Multiple select statements in a Stored Procedure

EffiProz now supports multiple select statements inside a stored procedure. You can fetch all result sets by invoking the Stored Procedure with CALL statement.
This is demonstrated in the following example,

Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip

LEAVE and ITERATE statements in SQL Stored Procedures

Following demonstrates the use of LEAVE and ITERATE statements inside SQL Stored Procedures.

SQL Cursors

Cursors can be created/used in SQL Language Stored Routines using DECLARE, OPEN, FETCH and CLOSE statements. Cursors are none updatable and forward-only.

Example,

create table test1(id int,name varchar(10));
create table test2(id int,name varchar(10));
create table test3(id int,name varchar(10));

insert into test1 values(1,'bus');
insert into test1 values(2,'van');
insert into test2 values(3,'car');
insert into test2 values(4,'lorry');

CREATE PROCEDURE curtest()
   BEGIN
   DECLARE done BOOLEAN DEFAULT FALSE;
   DECLARE y,z VARCHAR(10);
   DECLARE x INT;
   DECLARE cur1 CURSOR FOR SELECT id,name FROM test1;
   DECLARE cur2 CURSOR FOR SELECT name FROM test2;
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   OPEN cur1;
   OPEN cur2;
   REPEAT
      FETCH cur1 INTO x, y;
      FETCH cur2 INTO z;
      IF NOT done THEN
          IF y < z THEN
             INSERT INTO test3 VALUES (x,y);
          ELSE
            INSERT INTO test3 VALUES (x,z);
          END IF;
      END IF;
   UNTIL done END REPEAT;
   CLOSE cur1;
   CLOSE cur2;
 END

CALL curtest();
SELECT * FROM test3;

Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip

User-Defined Variables

Support for session specific user-defined variables. User defined variable names starts with "@" character.
Values can be assigned using either "SET" statement or "SELECT" statement. In "SET" statement both "=" and ":=" operators are allowed but in "SELECT" statement only ":=" operator is allowed.

 Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip 

Dynamic SQL Execution

MySQL style support for executing Dynamic SQL Statements using PREPARE, EXECUTE and DEALLOCATE PREPARE statements.
Following are few samples on Dynamic SQL execution capabilities.
SQL Statement as a String Literal:

SQL Statement as a User Variable:

Create SQL statement at Run-Time:

Download latest development release from http://www.effiproz.com/downloads/EffiProz_db_1_0_dev.zip

EffiProz Vs SQL Server CE

Following is a crude performance comparison between EffiProz 1.0 and SQL Server CE 3.5 using the same setup as earlier comparison.