Query execution cancellation and support of new data type of Firebird v2.5 — SQL_NULL

Commands cancellation support in Firebird 2.5 and InterBase 6.5

The new IBProvider version has implemented the option of commands execution cancellation. The query itself and cancellation operation shall be performed in different streams.

Minimum requirements: Server and client part InterBase 6.5 or above, Firebird 2.5 or above.

Test example on C#:

Before doing the example, run the following DLL query:

CREATE OR ALTER PROCEDURE SP_EXEC_DUMMY_COUNTER (n integer)
 AS
 BEGIN
  WHILE(N>0)DO
  BEGIN
   N=N-1;
  END
 END

Example code:

[Test(Description = "Sample - Command.Cancel")]
public void CancelCommand()
{
     using (OleDbConnection con = ConnectionProvider.CreateConnection())
     {
        con.Open();
        OleDbTransaction trans = con.BeginTransaction();

        cmd.CommandText = "exec SP_EXEC_DUMMY_COUNTER(5000000)";

        // call cmd.ExecuteScalar using BackgroundWorker class
        BackgroundWorker bw = new BackgroundWorker();
        bw.DoWork += delegate(object sender, DoWorkEventArgs e)
        {
            OleDbCommand cmd1 = e.Argument as OleDbCommand;
            Console.WriteLine("begin call");
            cmd1.ExecuteScalar();
            Console.WriteLine("end call");
        };

        bw.RunWorkerAsync(cmd);

        //Cancel command after 500 miliseconds
        System.Threading.Thread.Sleep(500);
        cmd.Cancel();

        // waiting while cancel perform
        while (bw.IsBusy) { System.Threading.Thread.Sleep(500); }
        trans.Commit();
    }
}

Support of new data type SQL_NULL in Firebird 2.5

Firebird 2.5 has acquired the option of running queries with expressions of «? is NULL» type. This is achieved with the help of new data type — SQL_NULL. IBProvider can process SQL_NULL for named and positional parameters.

Example:

select count(*) from PROJECT where :par_null is NULL

If par_null parameter value is NULL, condition WHERE will be met and the command will return the number of entries in table PROJECT, otherwise the command will return 0. The following is the full text of the example in VBScript:

dim cn, cmd, rs

set cn = wscript.createobject("adodb.connection")
call cn.open("Provider=LCPI.IBProvider.3;Password=masterkey;" & _
             "Persist Security Info=True;User ID=sysdba; " & _
             "Location=localhost:EMPLOYEE.FDB; dbclient_library=fbclient.dll")

cn.BeginTrans

WScript.Echo "IB Base=" & cn.Properties("IB Base").Value
WScript.Echo "IB Client Version=" & cn.Properties("IB Client Version").Value
WScript.Echo "Provider Version =" & cn.Properties("Provider Version").Value

set cmd = WScript.Createobject("adodb.command")
cmd.activeconnection = cn
cmd.CommandText = "select count(*) from PROJECT where :par_null is NULL"
cmd("par_null").Value = null

set rs=cmd.Execute

WScript.Echo cstr(rs(0))

cn.CommitTrans
cn.Close

The same example on C#:

[Test(Description = "Sample -  'is NULL' operator")]
public void ExecIsNullCommand()
{
 using (OleDbConnection con = ConnectionProvider.CreateConnection())
 {
  con.Open();
  OleDbTransaction trans = con.BeginTransaction();

  OleDbCommand cmd = new OleDbCommand("select (*) from PROJECT where ? is NULL", con, trans);
  cmd.Parameters.AddWithValue("", null);

  // if cmd parameter is null then command will return count result
  Assert.IsTrue((int)cmd.ExecuteScalar() > 0);
  trans.Commit();
 }
}

Minimum requirements: Server and client parts Firebird 2.5 Beta 1 or above.

Access to BLOB fields from ADOExpress (dbGo) bug was fixed

Bug with BLOB fieds access was fixed. In the previous versions of IBProvider error message may appeared Write BLOB: User storage-object works incorrectly during writing BLOB data via ADOExpress (DbGo). Read details here: IB-native IN-parameter value create failed.

New implementation of result sets cache

• Asynchronous writing (in a separate stream) into own swap file has been implemented.
• Writing into a temporary file has been optimized.

Finally

Along with implementation of support for new Firebird 2.5 options we are constantly working on the improvement of IBProvider core, implement new services that allow retaining driver’s flexibility and scalability when extending its functionality.