IBProvider 3.16. New features for SQL and for 64bit applications

In this release of IBProvider:

• Support of «RETURNING … INTO …» in queries
• IN-OUT parameters in «exec SP <param list>» and «{call SP(<param_list>)}» queries
• Transparent support for 32bit and 64bit applications.
• Fix for critical errors

Also:

• Special offer

Support of «RETURNING … INTO …» in queries

Now you can define the explicit names for OUT-parameters, which returning from queries:

• «INSERT … RETURNING …»
• «UPDATE … RETURNING …»
• «DELETE … RETURNING …»
• «UPDATE OR INSERT … RETURNING …»
• «MERGE INTO … RETURNING …» [FB3]

All you need to do — add a section «INTO» (like in PSQL) in similar queries. And you can use in application code with your names for OUT-parameters.

using System;
using System.Data;
using xdb=lcpi.data.oledb;

namespace ConsoleApplication
{
 class Program
 {
  static void Main(string[] args)
  {
   const string c_cn_str
    ="provider=LCPI.IBProvider.3;"
    +"location=home2:d:\\database\\employee.fdb;"
    +"user id=sysdba;"
    +"password=masterkey;";

   try
   {
    using(var cn=new xdb.OleDbConnection(c_cn_str))
    {
     cn.Open();

     using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
     {
      using(var cmd=new xdb.OleDbCommand("",cn,tr))
      {
       cmd.CommandText
         ="insert into CUSTOMER (CUST_NO,CUSTOMER,COUNTRY)\n"
          +"values(NULL,'Yeti','Canada')\n"
          +"returning CUST_NO\n"
          +"into :ID";

       cmd.Parameters.Add("ID",xdb.OleDbType.BigInt,0,ParameterDirection.Output);

       var rows_affected=cmd.ExecuteNonQuery();

       Console.WriteLine("RowsAffected: {0}",rows_affected);

       Console.WriteLine("ID: {0}",cmd["ID"].Value);
      }//using cmd

      tr.Rollback();
     }//using tr
    }//using cn
   }
   catch(Exception exc)
   {
    Console.WriteLine("ERROR: [{0}] - {1}",exc.Source,exc.Message);
   }
  }//Main
 }//class Program
}//ConsoleApplication

Output:

You can use the one name for IN and OUT parameters. In this case provider will generate the single IN-OUT parameter.

using(var cmd=new xdb.OleDbCommand("",cn,tr))
{
 cmd.CommandText
   ="insert into CUSTOMER (CUST_NO,CUSTOMER,COUNTRY)\n"
    +"values(:ID,'Yeti2','Canada')\n"
    +"returning CUST_NO,CUSTOMER\n"
    +"into :ID,:NAME";

 cmd["ID"].Value=DBNull.Value;

 Console.WriteLine("Execute command ...");
 var rows_affected=cmd.ExecuteNonQuery();

 Console.WriteLine("RowsAffected: {0}",rows_affected);

 Console.WriteLine("ID   : {0}",cmd["ID"].Value);
 Console.WriteLine("NAME : {0}",cmd["NAME"].Value);
}//using cmd

Output:

Moreover, you can use unnamed parameters in section INTO — «?».

using(var cmd=new xdb.OleDbCommand("",cn,tr))
{
 cmd.CommandText
   ="insert into CUSTOMER (CUST_NO,CUSTOMER,COUNTRY)\n"
    +"values(NULL,'Yeti3','Canada')\n"
    +"returning CUST_NO,CUSTOMER\n"
    +"into ?,?";

 cmd.Parameters.Refresh();

 Console.WriteLine("Execute command ...");
 var rows_affected=cmd.ExecuteNonQuery();

 Console.WriteLine("RowsAffected: {0}",rows_affected);

 Console.WriteLine("ID   : {0}",cmd[0].Value);
 Console.WriteLine("NAME : {0}",cmd[1].Value);
}//using cmd

Output:

IN-OUT parameters in «exec SP <param list>» and «{call SP(<param_list>)}» queries

We improved the implementation of stored procedure execution through ADO.NET and ODBC call syntax. Now, if you will use the one name for IN and OUT parameters, then provider will generate the single IN-OUT parameter.

using System;
using System.Data;
using xdb=lcpi.data.oledb;

namespace ConsoleApplication
{
 class Program
 {
  static void Main(string[] args)
  {
   const string c_cn_str
    ="provider=LCPI.IBProvider.3;"
    +"location=localhost:d:\\database\\ibp_test_fb25_d3.gdb;"
    +"user id=sysdba;"
    +"password=masterkey;"
    +"support_odbc_query=true";

   try
   {
    Console.WriteLine("Connect to database ...");
    using(var cn=new xdb.OleDbConnection(c_cn_str))
    {
     cn.Open();

     Console.WriteLine("Start transaction ...");
     using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
     {
      //CREATE PROCEDURE SP_INC_I4(IN_VALUE INTEGER)
      //RETURNS (OUT_VALUE INTEGER)
      //AS
      //BEGIN
      //OUT_VALUE=IN_VALUE+1;
      //END

      using(var cmd=new xdb.OleDbCommand("",cn,tr))
      {
       Console.WriteLine("Use ODBC call style ...");

       cmd.CommandText="{call SP_INC_I4(:x,:x)}";

       cmd["x"].Value=123;

       cmd.ExecuteNonQuery();

       Console.WriteLine("result: {0}",cmd["x"].Value);

       //-------
       Console.WriteLine("");
       Console.WriteLine("Use ADO.NET call style ...");

       cmd.CommandText="exec SP_INC_I4 :y,:y";

       cmd["y"].Value=321;

       cmd.ExecuteNonQuery();

       Console.WriteLine("result: {0}",cmd["y"].Value);
      }//using cmd

      tr.Rollback();
     }//using tr
    }//using cn
   }
   catch(Exception exc)
   {
    Console.WriteLine("ERROR: [{0}] - {1}",exc.Source,exc.Message);
   }
  }//Main
 }//class Program
}//ConsoleApplication

Output:

Transparent support for 32bit and 64bit applications

In current release of provider were added two new initialize properties: dbclient_library_64 and icu_library_64. Now you can build the one connection string for 32-bit and 64-bit applications.

Fix for critical errors

We found and fixed a set of critical errors, which may have occurred after the allocation of all available process memory. For demonstration of these problems, we created a standalone test — «TestCode\ActiveX\IBP\stress_tests\00001__open_rowset_no_vmem».

Special offer

We are in really good mood – IBProvider v3 moved onto next level of technological development and quality. But we don’t want to spend our time onto increase major version number to v4. No. We really like this signature — v3. And we have a many other interests and complex ideas for v3. Plus — all for implementation of them: desire, knowledge, great code base, tests and hardware.

So we offering you do not spend your time to search in unknown directions and not use products with unknown future.