Publish date: 2007-07-24
Updated: 2017-11-12

Using new features of Firebird 2.1 in ADO.NET

Article examples

Keywords: Firebird 2.1, Firebird, ADO .Net, c#, .Net провайдер, Firebird 2.

Beta version of the Firebird 2.1 database was published at the end of June, 2007. I suppose, most interesting features are common table expressions, global database triggers, temporary tables, database monitoring through the SQL, canceling running queries and new SQL statements.

We implemented new Firebird 2.1 features in IBProvider and glad to announce new version of IBProvider Professional Edition

RETURNING Clause

UPDATE, INSERT and UPDATE OR INSERT now can be used with RETURNING clause. In the previous Firebird 2.0 release this feature was implemented only for INSERT statement, now it is possible for other statements. Let’s write C# sample: 

using (OleDbConnection con = ConnectionProvider.GetConnection())
  {
     // Description: UPDATE OR INSERT … RETURNING, UPDATE .. RETURNING, DELETE .. RETURNING, Firebird 2.1 ADO .Net (c#)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

  // 1. inserting new record using the UPDATE OR INSERT .. RETURNING statement
     OleDbCommand cmd = new OleDbCommand(
     «UPDATE OR INSERT INTO country (country, currency) VALUES (?, ?) RETURNING country, currency», con, trans);

     // in-parameters
     cmd.Parameters.AddWithValue(«country», «Russia»);
     cmd.Parameters.AddWithValue(«currency», «Kopec»);

     // out-parameters for RETURNING clause
     OleDbParameter outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     OleDbParameter outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «inserting record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from UPDATE or INSERT command. FAIL»);
     Assert.AreEqual(«Kopec», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from UPDATE or INSERT command. FAIL»);

  // 2. updating record using the UPDATE .. RETURNING statement
     cmd = new OleDbCommand(«UPDATE country SET currency = ? WHERE country = ? RETURNING country, currency», con, trans);

     // in-parameters
     cmd.Parameters.AddWithValue(«currency», «Rouble»);
     cmd.Parameters.AddWithValue(«country», «Russia»);

     // out-parameters
     outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from UPDATE command. FAIL»);
     Assert.AreEqual(«Rouble», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from UPDATE command. FAIL»);

  // 3. DELETE .. RETURNING             

     // input parameter required because ADO .Net will ignore parameters for commands which not contains 
     // explicit symbol ‘?’ (or :param_name)
     cmd = new OleDbCommand(«DELETE FROM country WHERE country =:country RETURNING country, currency», con, trans);

     // in-parameters
     cmd.Parameters.AddWithValue(«country», «Russia»);

     // out-parameters
     outpCountry = new OleDbParameter(«out_country», OleDbType.BSTR);
     outpCountry.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCountry);

     outpCurrency = new OleDbParameter(«out_currency», OleDbType.BSTR);
     outpCurrency.Direction = ParameterDirection.Output;
     cmd.Parameters.Add(outpCurrency);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «deleting record. FAIL»);
     Assert.AreEqual(«Russia», outpCountry.Value, «getting out parameter ‘COUNTRY’ from DELETE command. FAIL»);
     Assert.AreEqual(«Rouble», outpCurrency.Value, «getting out parameter ‘CURRENCY’ from DELETE command. FAIL»);


     trans.Commit();
 }

When you will be using the RETURNING clause, you must define at least one input parameter (like the named parameter :country in the previous code sample):

DELETE FROM country WHERE country =:country RETURNING country, currency

If you define SQL query without input parameters ADO.Net will ignore your output parameters and Firebird will throw the error:

DELETE FROM country WHERE country =‘Russia’ RETURNING country, currency

UPDATE OR INSERT

It is very helpful statement, which allow defining single expression for INSERT and UPDATE operations. Server compares records by MATCHING (column) clause and automatically detects which action must be performed. If MATCHING (column) skipped, server will use primary key:

using (OleDbConnection con = ConnectionProvider.GetConnection())
 {
     // Description: UPDATE OR INSERT, Firebird 2.1 ADO .Net (c#)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

     // insert ne wrecord
     // matching by primary key «country»
     OleDbCommand cmd = new OleDbCommand(
     «UPDATE OR INSERT into COUNTRY (country, currency) values (?, ?)», con, trans);

     cmd.Parameters.AddWithValue(«country», «Russia»);
     cmd.Parameters.AddWithValue(«currency», «Kopec»);

     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «inserting record. FAIL»);

     // update record
     cmd.Parameters[«country»].Value = «Russia»;
     cmd.Parameters[«currency»].Value = «Rouble»; // setting new value
     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record. FAIL»);

     // updating record which matched by ‘Currency’ field
     cmd = new OleDbCommand(
     «UPDATE OR INSERT into COUNTRY (country, currency) values (?, ?) MATCHING (currency) returning country, currency», con, trans);

     cmd.Parameters.AddWithValue(«country», «RF»);
     cmd.Parameters.AddWithValue(«currency», «Rouble»);
     Assert.AreEqual(1, cmd.ExecuteNonQuery(), «updating record fail.»);

     // delete record
     OleDbCommand cmd_delete = new OleDbCommand(string.Format(
         «delete from COUNTRY where country ='{0}'», «Russian Federaion»), con, trans);
     Assert.AreEqual(1, cmd_delete.ExecuteNonQuery(), «deleting record fail»);

     trans.Commit();
 }

The LIST() Function

LIST(column, delimiter) is the new aggregate function which build string from result set using delimiter. 

<list function> ::=
  LIST ‘(‘ [ {ALL | DISTINCT} ] <value expression> [‘,’ <delimiter value>] ‘)’

<delimiter value> ::= { <string literal> | <parameter> | <variable> }

The simple sample of getting customers list as single value:

using (OleDbConnection con = ConnectionProvider.GetConnection())
{
  // Description: LIST function, Firebird 2.1 c# (ADO .Net)
  con.Open();
  OleDbTransaction trans = con.BeginTransaction();

  // select all customers into the string with separators
  OleDbCommand cmd = new OleDbCommand(
  string.Format(«SELECT LIST(CUSTOMER, ‘,{0}’) FROM CUSTOMER», Environment.NewLine) , con, trans);

  // query return single value 
  string customer_list = cmd.ExecuteScalar().ToString();
  Console.WriteLine(customer_list);

  trans.Commit();
}

Database monitoring through the SQL

Database statistic

New Firebird 2.1 feature is the Database Monitoring Tables. It’s allowed to get info about active connections, queries, and transactions, collect database statistic, cancel executing queries, etc.

The first Sample shows how to get general database info:

using (OleDbConnection con = ConnectionProvider.GetConnection())
 {
     // Description: MONITORING TABLES Firebird 2.1 c# (ADO .Net)
     con.Open();
     OleDbTransaction trans = con.BeginTransaction();

     OleDbCommand cmd = new OleDbCommand(«select * from MON$DATABASE», con, trans);

  using(OleDbDataReader rdr = cmd.ExecuteReader())
      while (rdr.Read())
      {
          Console.WriteLine(«[DATABASE INFO]»);
          Console.WriteLine(«Database name:       {0}», rdr[«MON$DATABASE_NAME»]);
          Console.WriteLine(«Dialect:             {0}», rdr[«MON$SQL_DIALECT»]);
          Console.WriteLine(«Page size:           {0}», rdr[«MON$PAGE_SIZE»]);
          Console.WriteLine(«Pages:               {0}», rdr[«MON$PAGES»]);
          Console.WriteLine(«Creation date:       {0}», rdr[«MON$CREATION_DATE»]);
          Console.WriteLine(«Read only database:  {0}», Convert.ToBoolean(rdr[«MON$READ_ONLY»]));

          Console.WriteLine(«ODS Version:         {0}.{1}», rdr[«MON$ODS_MAJOR»], rdr[«MON$ODS_MINOR»]);
          Console.WriteLine(«Oldest transaction:  {0}», rdr[«MON$OLDEST_TRANSACTION»]);
          Console.WriteLine(«Oldest active:       {0}», rdr[«MON$OLDEST_ACTIVE»]);
          Console.WriteLine(«Oldest snapshot:     {0}», rdr[«MON$OLDEST_SNAPSHOT»]);

          // enumeration from RDB$TYPES WHERE RDB$FIELD_NAME =’MON$SHUTDOWN_MODE’
          Console.WriteLine(«Shutdown mode:       {0}», rdr[«MON$SHUTDOWN_MODE»]);

                     Console.WriteLine(«Sweep interval:      {0}», rdr[«MON$SWEEP_INTERVAL»]);
                 }

             trans.Commit();
         }

How to get the isolation level of the current transaction?

The simple way to get the isolation level of the current transaction is use the table MON$TRANSACTIONS. The SQL query below:

select CURRENT_TRANSACTION, trans.MON$ISOLATION_MODE, types.RDB$TYPE_NAME
from MON$TRANSACTIONS trans
join RDB$TYPES types
on types.RDB$FIELD_NAME = ‘MON$ISOLATION_MODE’ and types.RDB$TYPE = trans.MON$ISOLATION_MODE
where MON$TRANSACTION_ID = CURRENT_TRANSACTION

Results of using all ADO .Net IsolationLevel enum values:

ADO .Net Isolation level Firebird Isolation level
Chaos READ_COMMITTED_NO_VERSION
ReadUncommitted Not supported
ReadCommitted READ_COMMITTED_NO_VERSION
RepeatableRead CONCURRENCY (or SNAPHOT)
Serializable CONSISTENCY
Snapshot Not supported
Unspecified READ_COMMITTED_NO_VERSION

The isolation level READ_COMMITTED_NO_VERSION is setted by default. Firebird supports one more isolation level which not supported by ADO .Net — it is the READ_COMMITTED_VERSION. You can set it through the SET TRANSACTION operator.

Cancel executing query

Information about executing queries is available from the table MON$STATEMENTS. If you want cancel any query you must delete record from the MON$STATEMENTS, for example:

delete from mon$statements where mon$sql_text = :sql_text

Sometimes query text doesn’t available, however you can detect required query by alternate parameters like MON$STATE (query activity [1 — active, 0 — idle]) or MON$TIMESTAMP (query start time).

Conclusion

Firebird is very progressive and absolutely free database. More info about Firebird 2.1 features you will find in official Release Notes. We has chose Firebird and offer it to the customers. IBProvider allows rapid development with many modern tools and code editors. If you search robustness and inexpensive decision, you already find it.

What do you need for quick start?

  1. Download Firebird 2.1 database or above and install it.
  2. Run tool which supports OLE DB technology (like Visual Studio, Borland C++ Builder, Delphi, WSH, etc).
  3. Install IBProvider Professional Edition.

Useful links:

  1. Quick Start with Firebird + ADO .Net Provider (c#). 26 and 1 samples for ADO .Net 2.0. and Firebird 2.0.
  2. Quick Start with Firebird Delphi and InterBase Delphi.
  3. The differences between data access technologies: ODBC Firebird driver, ODBC InterBase or OLE DB provider?
  4. ODBC Escape Sequences guide.
Author: Andrew Merkulov  
Publish date: 2007-07-24. Copyright: IBProvider. This material may be reproduced on other web sites, without written permission but link https://www.ibprovider.com/eng required.
All documentation