EffiProz Systems is a Finalist for the 2010 Red Herring 100 Asia Award

 EffiProz Systems has been selected as a Finalist for Red Herring's top 100 Asia award, a prestigious list honoring the year's most promising private technology ventures from the Asia business region.
http://herringevents.com/rha2010/finalists/2010finalists.html

Expressions with long AND/OR chains

Logical expressions with long AND/OR chains, like the one shown below (where EffiProz 1.4 had performance issues), now works smoothly with new optimizations in EffiProz 1.5.
(Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3950_22178.zip)

SELECT ORDERID , CUSTOMERID FROM PUBLIC.ORDERS WHERE ORDERID=10248 OR ORDERID=10249 OR ORDERID=10250 OR ORDERID=10251 OR ORDERID=10252 OR ORDERID=10253 OR ORDERID=10254 OR ORDERID=10255 OR ORDERID=10256 OR ORDERID=10257 OR ORDERID=10258 OR ORDERID=10259 OR
                        ORDERID=10260 OR ORDERID=10261 OR ORDERID=10262 OR ORDERID=10263 OR ORDERID=10264 OR ORDERID=10265 
                        OR ORDERID=10266 OR ORDERID=10267 OR ORDERID=10268 OR ORDERID=10269 OR ORDERID=10270 OR ORDERID=10271
                        OR ORDERID=10272 OR ORDERID=10273 OR ORDERID=10274 OR ORDERID=10275 OR ORDERID=10276 OR ORDERID=10277 
                        OR ORDERID=10278 OR ORDERID=10279 OR ORDERID=10280 OR ORDERID=10281 OR ORDERID=10282 OR ORDERID=10283 
                OR ORDERID=10284 OR ORDERID=10285 OR ORDERID=10286 OR ORDERID=10287 OR ORDERID=10288 OR ORDERID=10289 
                OR ORDERID=10290 OR ORDERID=10291 OR ORDERID=10292 OR ORDERID=10293 OR ORDERID=10294 OR ORDERID=10295 OR
                ORDERID=10296 OR ORDERID=10297 OR ORDERID=10298 OR ORDERID=10299 OR ORDERID=10300 OR ORDERID=10301 OR 
                ORDERID=10302 OR ORDERID=10303 OR ORDERID=10304 OR ORDERID=10305 OR ORDERID=10306 OR ORDERID=10307 OR 
                ORDERID=10308 OR ORDERID=10309 OR ORDERID=10310 OR ORDERID=10311 OR ORDERID=10312 OR ORDERID=10313 OR
                ORDERID=10314 OR ORDERID=10315 OR ORDERID=10316 OR ORDERID=10317 OR ORDERID=10318 OR ORDERID=10319 OR
                ORDERID=10320 OR ORDERID=10321 OR ORDERID=10322 OR ORDERID=10323 OR ORDERID=10324

Invoke Stored Procedures with Table Parameters via ADO.NET provider

EffiProz 1.5 ADO.NET provider supports passing either a DataTable or a DbDataReader to a Stored Procedure Table Parameter. Following example demonstrates how to pass a DataTable to a Stored Procedure. (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3948_33802.zip )

string connString = "Connection Type=Memory ; auto commit=true; Initial Catalog=TestSPTabularParameter; User=sa; Password=;";
            string sql = @"CREATE TABLE MyTable (
                         col1 int NOT NULL PRIMARY KEY,
                         col2 varchar(20) NULL,                       
                         UserID varchar(20) NOT NULL
                         );";
            using (DbConnection conn = new EfzConnection(connString))
            {
                DbCommand command = conn.CreateCommand();
                command.CommandText = sql;
                conn.Open();
                command.ExecuteNonQuery();

                sql = @"CREATE TYPE MyType AS TABLE 
                         (
                         col1 int  , 
                         col2 varchar(20)                        
                         );";
                command.CommandText = sql;
                command.ExecuteNonQuery();

                sql = @"CREATE PROCEDURE AddRowsToMyTable( @MyTableParam MyType,@UserID varchar(20))
                         BEGIN
                         INSERT INTO MyTable(col1,col2,UserID)SELECT col1,col2,@UserID FROM @MyTableParam;
                         END;";

                command.CommandText = sql;
                command.ExecuteNonQuery();

                DataTable table = new DataTable("@MyTableParam");
                DataColumn col1 = new DataColumn("col1", System.Type.GetType("System.Int32"));
                DataColumn col2 = new DataColumn("col2", System.Type.GetType("System.String"));
              
                table.Columns.Add(col1);
                table.Columns.Add(col2);
           
                for (int i = 1; i < 10; i++)
                {
                    var vals = new object[2];
                    vals[0] = i;
                    vals[1] = "TEST"+i;                 
                    table.Rows.Add(vals);
                }

                command.CommandType = CommandType.StoredProcedure;
                command.CommandText = "AddRowsToMyTable";

                DbParameter p1 = command.CreateParameter();
                DbParameter p2 = command.CreateParameter();
                command.Parameters.Add(p1);
                command.Parameters.Add(p2);
                p1.ParameterName = "@MyTableParam";
                p2.ParameterName = "@UserID";
                p1.Value = table;             
                p2.Value = "admin";
                command.ExecuteNonQuery();

                command.CommandText = "SELECT * FROM MyTable";
                command.CommandType = CommandType.Text;
                DbDataReader reader = command.ExecuteReader();
  }

Table Parameters in Stored Procedures

Following examples demonstrate how-to use Table type parameters in Stored Procedures in EffiProz 1.5 (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3944_36929.zip ),

CREATE TYPE MyType AS TABLE 
 (
 col1 int  , 
 col2 varchar(20) , 
 col3 datetime
 );

DECLARE MyTableVar MyType;

INSERT INTO MyTableVar(col1,col2,col3)
 VALUES (1,'pat','1/1/2000'),
 (2,'mat','1/1/2011'),
 (3,'bat','1/1/2011'),
 (4,'cat','1/1/2023'),
 (5,'rat','1/1/2034');


CREATE TABLE MyTable (
 col1 int NOT NULL PRIMARY KEY,
 col2 varchar(20) NULL,
 col3 datetime NULL,
 UserID varchar(20) NOT NULL
 );

CREATE PROCEDURE usp_AddRowsToMyTable( @MyTableParam MyType,@UserID varchar(20))
 BEGIN
 INSERT INTO MyTable(col1,col2,col3,UserID)SELECT col1,col2,col3,@UserID FROM @MyTableParam;
 END;

CALL usp_AddRowsToMyTable(  MyTableVar,'Admin');

SELECT * FROM MyTable;

Following example uses a Procedure local Table as an intermediate store,

CREATE PROCEDURE usp_AddRowsToMyTable3( @MyTableParam MyType,@UserID varchar(20))
 BEGIN
 DECLARE @localtable MyType;
 INSERT INTO  @localtable(col1,col2,col3) SELECT col1,col2,col3 FROM @MyTableParam;
 Call usp_AddRowsToMyTable(@localtable,@UserID);
 END;

CALL usp_AddRowsToMyTable3(  MyTableVar,'Admin');

/*r5*/SELECT count(*) FROM MyTable;

Table Variables and Recursive Functions

Following example (Similar to the one provided by HSQL) demonstrates how use Recursive Functions with  Table variables in upcoming EffiProz 1.5 (Development Release http://www.effiproz.com/downloads/effiproz_db_1_5_3944_36929.zip),

CREATE TABLE ptree (pid INT, id INT);
INSERT INTO ptree VALUES (NULL, 1) ,(1,2), (1,3),(2,4),(4,5),(3,6),(3,7);


CREATE FUNCTION child_table(p_pid INT) RETURNS TABLE(r_pid INT, r_id INT)
  SPECIFIC child_table_one
  READS SQL DATA
  SIGNAL SQLSTATE '45000';

ALTER SPECIFIC ROUTINE child_table_one SET BODY
  BEGIN ATOMIC
    DECLARE TABLE child_tree (pid INT, id INT);
    for_loop:
    FOR SELECT pid, id FROM ptree WHERE pid = p_pid DO
      INSERT INTO child_tree VALUES pid, id;
      INSERT INTO child_tree SELECT r_pid, r_id FROM TABLE(child_table(id));
    END FOR for_loop;
    RETURN TABLE(SELECT * FROM child_tree);
  END

SELECT * FROM TABLE(child_table(1)); 

Recursive CTEs (Common Table Expressions)

EffiProz 1.5 development release with Recursive CTE support http://www.effiproz.com/downloads/effiproz_db_1_5_3941_21958.zip

EffiProz supports connecting non-recursive and recursive elements of the CTE with either UNION or UNION ALL operators. Maximum  number of recursions allowed is limited to 10000.

Few examples on different types of CTEs supported by EffiProz: http://www.effiproz.com/examples/cte.aspx.

DatAdmin - Database Administration Tool for EffiProz

DatAdmin (http://datadmin.com/ ) Database Administration tool has included support for EffiProz database (currently a Beta). This would make working with EffiProz databases a lot easier than with our Basic Query Tool. Here’s the link for DataAdmin with EffiProz support: http://datadmin.com/datadmin-beta.exe.

According to DatAdmin team following features are supported for EffiProz,
• connection wizard
• data editor
• create, alter table, indexes, constraints (PK, FK, checks), autoincrement
• rename/drop table, column, constraint
• drop/create/list view/trigger/procedure/function syntax highlighter for EffiProz dialect
• basic migrate from/to MSSQL, SQLite, MySQL, Postgres, Access
• Export, import, drag & drop table copy, generate SQL, etc.

Northwind Database opened in DatAdmin,

Connection Dialog,

Stored Procedure Editor

Necleon Software Database Master for EffiProz Database

Necleon Software Database Master is now supporting EffiProz Database. This would be a wonderful choice if you need more features than what's currently available in our Query Tool.


You can download Necleon Software Database master from http://www.nucleonsoftware.com/Download.aspx  (You need to get Database Master 3 Beta http://www.nucleonsoftware.com/download/DatabaseMasterSetupBETA.msi  ).

Database Master EffiProz connection setup dialog,



EffiProz Northwind sample database opened in Database Master,

You can use Database Master to import (or export) data in wide variety of format into EffiProz Tables,

MonoDroid Database for Android Devices

 EffiProz MonoDroid Database for Android Devices: http://www.effiproz.com/downloads/effiproz_md_beta.zip . Zip distribution contains EffiProz-MD assembly and Coding Samples.