IBProvider 3.12. New features for named parameters and nested transactions

Support for named parameters.

We added the new property for data source initialization and for command — «named_param_rules». Sample of usage:

//Use
// - Firebird SQL Server v2.5.2
// - LCPI.IBProvider v3.12
// - LCPI ADO.NET Data Provider for OLE DB
using System;
using System.Data;

using xdb=lcpi.data.oledb;

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

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

     using(var tr=cn.BeginTransaction(IsolationLevel.RepeatableRead))
     {
      var cmd=new xdb.OleDbCommand("select * from employee where first_name=@name",cn,tr);

      //use an explicit definition
      cmd.Parameters.AddWithValue("@name","Roger");

      //small optimization through hint "SingleResult"
      using(var reader=cmd.ExecuteReader(CommandBehavior.SingleResult))
      {
       //Test result set. We get false for empty result set. Really.
       if(reader.HasRows)
       {
        //reuse the command for update query
        cmd.CommandText="update employee set first_name=upper(first_name)\n"
                       +"where emp_no=@emp_id\n"
                       +"returning old.first_name";

        //ask provider to generate the parameters list
        cmd.Parameters.Refresh();

        for(int n=1;reader.Read();++n)
        {
         cmd["@emp_id"].Value=reader["emp_no"];

         cmd.ExecuteNonQuery();

         Console.WriteLine("{0}. Update emp_no: {1}. Old first_name: \"{2}\"",
                           n,
                           reader["emp_no"],
                           cmd["@first_name"].Value);
        }//while reader
       }//if HasRows
      }//using reader
     }//using tr
    }//using cn
   }
   catch(Exception e)
   {
    Console.WriteLine("ERROR: {0} - {1}",
                      e.Source,
                      e.Message);
   }//catch
  }//Main
 }//class Program
}//namespace ConsoleApplication

Nested transactions.

By default, new IBProvider prohibits the commit of transaction with active nested transactions. Also, by default, new provider prohibits the release of savepoint, if this savepoint contains the active nested transactions. The new initialize property «nested_trans_rules» allows changing this new behavior. Sample of new IBProvider behavior:

option explicit

rem Use
rem - Firebird SQL Server v2.5.2
rem - LCPI.IBProvider v3.12

dim cn
set cn=createobject("ADODB.Connection")

cn.Provider="LCPI.IBProvider.3"

call cn.Open("location=localhost:d:\database\employee.fdb;" _
             +"nested_trans=true", _
             "gamer", _
             "vermut")

call cn.BeginTrans() 'level 1
call cn.BeginTrans() 'level 2

dim tr1
set tr1=createobject("LCPI.IBP.Samples.TransactionLevel.1")

call tr1.Attach(cn,1)

on error resume next

'error
call tr1.Commit(false)

wscript.echo "["&err.Source&"]"&vbCrLf&err.Description

Output:

Other changes.

• BUG FIX: memory leak, MT-error, mistakes in work with DTC and others.
• Improved the errors processing.
• Optimization.

Tests.

• New build was tested with InterBase (4.0-10.0.4) and Firebird (0.9.4-3.0).
• The load tests were executed with Firebird 2.5.2.