Publish date: 2008-03-03
Updated: 2017-11-12

New features of Firebird 2.0. Examples for ADO.NET (c#)

EXECUTE BLOCK instruction
INSERT RETURNING instruction
ROLLBACK RETAIN instruction
ROWS instruction
Derived Tables
Other changes
Useful references

Download article examples for Firebird 2.0 (c#)

Keywords: Firebird 2.0, ADO .Net, c#, Firebird, .Net provider for Firebird

EXECUTE BLOCK instruction

New SQL instructions were added to Firebird 2.0. One of them is the EXECUTE BLOCK instruction. It lets performing a block of instructions on the server side, in fact it is a virtual stored procedure. The following example demonstrates the database server resources’ use for performing the simple arithmetic calculation:

public void ExecuteBlockSQLTest()
{
    // Description: EXECUTE BLOCK — lets performing a block of instructions on
    //the server side of Firebird 2.0. or above
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //server side command text
    string execute_block_data =
    «EXECUTE BLOCK (X INTEGER = :X) \n» +
    «RETURNS (Y INTEGER)           \n» +
    «AS                            \n» +
    «BEGIN                         \n» +
    »    Y = X * 2;                \n» +
    «SUSPEND;                      \n» +
    «END                           \n»;

    //in parameter
    int in_parameter_X = 2;

    OleDbCommand cmd = new OleDbCommand(execute_block_data, con, trans);
    cmd.Parameters.AddWithValue(«X», in_parameter_X);

    //run EXECUTE BLOCK query
    Assert.AreEqual((int)cmd.ExecuteScalar(), in_parameter_X * 2);
    trans.Commit();

    con.Close();
}
Note.
For article examples IBProvider Professional Edition was used.

INSERT RETURNING instruction

One more new thing presented by Firebird 2.0 is the INSERT RETURNING instruction. In fact it lets performing the data inserting operation and reading values that were added while performing the operation. It is especially actual for getting new entry identifier while using the generator:

public void InsertReturning()
{
    // Description: INSERT RETURNING — it lets reading values that were added while performing the operation,
    //connect to Firebird 2.0 or above
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //INSERT RETURNING command
    OleDbCommand cmd = new OleDbCommand(
    «insert into customer (cust_no, customer)     \n» +
    «values(GEN_ID(CUST_NO_GEN,1),:customer_name) \n» +
    «RETURNING cust_no»,con,trans);

    cmd.Parameters.AddWithValue(«customer_name», «New customer»);
    //add output parameter
    cmd.Parameters.Add(«customer_no», OleDbType.Integer)
 				.Direction =ParameterDirection.Output;

    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //delete record
    OleDbCommand cmd_delete = new OleDbCommand(
    «delete from customer where cust_no=?», con, trans);

    cmd_delete.Parameters.AddWithValue(«?»,cmd.Parameters[«customer_no»].Value);
    Assert.AreEqual(1, cmd_delete.ExecuteNonQuery());

    trans.Commit();
    con.Close();
}
Note
There is no option to return the results for operations different from inserting operation in Firebird 2.0. This option appeared later in Firebird 2.1. version. Except standard UPDATE, DELETE operations, one more new UPDATE OR INSERT RETURNING instruction appeared.

ROLLBACK RETAIN instruction

ROLLBACK RETAIN — lets roll back transaction at the starting moment or till the last COMMIT_RETAIN, leaving the chance to its further use. Let’s demonstrate it with the example:

public void RollbackRetainTest()
{
    // Description: ROLLBACK RETAIN — пlets roll back transaction at the starting moment,
    //Firebird 2.0 and above feature
    OleDbConnection con = OpenFB2Connection();
    OleDbTransaction trans = con.BeginTransaction();

    //insert new record 
    OleDbCommand cmd = new OleDbCommand(
    «insert into customer (cust_no, customer)    « +
    «values(GEN_ID(CUST_NO_GEN,1),’New customer’)», con, trans);
    Assert.AreEqual(1, cmd.ExecuteNonQuery());

    //ROLLBACK RETAIN 
    new OleDbCommand(«ROLLBACK RETAIN», con, trans).ExecuteNonQuery();

    // transaction is active
    cmd = new OleDbCommand(
        «select count(*) from customer», con, trans);

    Assert.IsTrue((int)cmd.ExecuteScalar() > 0 );

    trans.Commit();
    con.Close();
}

ROWS instuction

ROWS key word matches the latest ANSI SQL standards and is an alternative to FIRST/SKIP. It lets specifying the processed rows number. It may be used in UNION, any subqueries, and in DELETE and UPDATE commands. The following example reads from first till third entries from database:

public void RowsKeywordTest()
{
      // Description: ROWS — lets specifying the processed rows number in Firebird 2.0
      OleDbConnection con = OpenFB2Connection();
      OleDbTransaction trans = con.BeginTransaction();

      //command will return 3 records
      OleDbCommand cmd = new OleDbCommand(
      «select * from customer rows 1 to 3», con, trans);

      short rec_count = 0;

      using (OleDbDataReader reader = cmd.ExecuteReader())
          while (reader.Read()) { rec_count++; }

      Assert.AreEqual(3, rec_count);

      trans.Commit();
      con.Close();
}

Derived Tables

Derived tables are named sets based on SELECT clause. The derived table EMPLOYEE_DERIVED_TABLE with new fields is based on EMPLOYEE table in the following example:

select * from
  (select EMP_NO, FIRST_NAME || ‘ ‘ || LAST_NAME from employee)
      as EMPLOYEE_DERIVED_TABLE (ID, FULL_NAME)

Other changes

I have given the examples of some improvements in DML Firebird 2.0. Here the following improvements can be mentioned:

  • Named cursors for PSQL.
  • New DML and DDL functions and operators (IIF, CREATE SEQUENCE, NEXT VALUE FOR, etc).
  • Plans support for update and remove functions.
  • Improved UNION, CROSS JOIN.

You may look at the examples in Firebird 2.0 release notes. document

Useful references

Author: Andrew A. Merkulov  

Publish date: 2008-03-03. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.