Usage of BOOLEAN and GUID types in Firebird and InterBase

The essence of the problem

• GUID type is currently not supported by any Firebird and InterBase server, but it is frequently used by different clients (for example, in ADO Entity Framework).
• Boolean type is supported by InterBase starting from 7th version and Firebird starting from 3th version.

IBProvider supports Guid and Boolean types at driver level, and now you can freely use these types in your applications irrespective of Firebird and InterBase server versions.

Data is stored in the types supported by the server. IBProvider browsers data domains and if it finds a domain suitable by mask, it sets the corresponding Boolean or Guid in the column.

Domains should meet the following requirements:

• Be based on SMALLINT type for BOOLEAN type.
• Be based on CHAR(16) type with OCTETS charset for GUID type.

Enabling emulation of types

To specify the data for the provider to interpret it as Bool or Guid, IBProvider has acquired two new initialization properties: user_type_boolean and user_type_guid.

They define the mask for the domain representing the emulated type. The mask supports «*” and “?” symbols. The mask is case sensitive.

When these requirements are not met, the provider ignores domain name and doesn’t try to substitute the type.

Emulation works for:

• Columns with a simple type;
• Columns with arrays;
• OUT-parameters with a simple type;
• OUT-parameters with arrays;
• IN-parameters with arrays.

Restrictions for emulation of the types

Due to ISC API (IB/FB) restrictions, you should explicitly specify parameter type when using simple types for input command parameters. Automatic generation of parameter description is supported for columns with arrays.

Emulation of the types in metadata

Emulation of the types also applies to metadata schemes. The provider ensures correspondence of types read at queries running and those displayed in schemes COLUMNS, PROCEDURE_PARAMETERS, PROCEDURE_COLUMNS.

Example of usage

To demonstrate the work, we will need:

• IBProvider, version 3.0.0.7412 or above;
• Any Firebird or InterBase server.

Let’s start with creating a test table and domains for storing emulated types of data.

CREATE DOMAIN D_BOOL_D1 AS SMALLINT;
CREATE DOMAIN D_GUID AS CHAR(16) CHARACTER SET OCTETS;

CREATE TABLE TYPE_EMULATOR (
    IDENTITY D_GUID,
      RESIDENT D_BOOL_D1 NOT NULL,
      FULLNAME VARCHAR(100));

To enable emulation of the types by the provider, set the mask in connection string for domains selection:

user_type_boolean= D_BOOL_*;user_type_guid= D_GUID;

The following is an example of C# code demonstrating usage of the type emulator:

[Test]
public void ComlexEmulatorTest()
{
    // retrive standard connection string parameters from config
    OleDbConnectionStringBuilder sb = ConnectionProvider.GetConnectionStringBuilderFromUDL();
    // add 2 masks for domains
    sb.Add("user_type_boolean", "D_BOOL*");
    sb.Add("user_type_guid", "D_GUID*");
    // disable resourse pooling because DROP operators are used
    sb.OleDbServices = OleDbServicesValues.EnableAll & ~OleDbServicesValues.ResourcePooling;

    using (OleDbConnection cn = new OleDbConnection(sb.ToString()))
    {
        cn.Open();

        // inserting...
        OleDbTransaction tran = cn.BeginTransaction(IsolationLevel.RepeatableRead);
        OleDbCommand cmd = new OleDbCommand(
        "insert into type_emulator (identity, resident, fullname) values (?,?,?) ", cn, tran);

        Guid guid = Guid.NewGuid();
        cmd.Parameters.Add("identity", OleDbType.Guid).Value = guid;
        cmd.Parameters.Add("resident", OleDbType.Boolean).Value = true;
        cmd.Parameters.AddWithValue("fullname", "Resident of Russian Federation");

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

        // selecting and validating
        tran = cn.BeginTransaction(IsolationLevel.RepeatableRead);
        cmd = new OleDbCommand("select * from type_emulator", cn, tran);

        using (OleDbDataReader rdr = cmd.ExecuteReader())
        {
            if (rdr.Read())
            {
                Console.WriteLine("Identity: {0} [type: {1}]", rdr["identity"], rdr["identity"].GetType().Name);
                Console.WriteLine("Resident: {0} [type: {1}]", rdr["resident"], rdr["resident"].GetType().Name);
                Console.WriteLine("Fullname: {0} [type: {1}]", rdr["fullname"], rdr["fullname"].GetType().Name);

                // check values
                Assert.AreEqual(guid, rdr["identity"]);
                Assert.AreEqual(rdr["resident"], true);
            }
            else
                Assert.Fail("Row was not inserted");
        }

        tran.Commit();
    } // Dispose cn.close
}

If properties user_type_boolean and user_type_guid have been set and IBProvider Professional version is 3.0.0.7412 or above, the console will display the following text:

identity: 6ef41c55-03ff-4941-9382-290813ad46c2 [type: Guid]
Resident: True [type: Boolean]
Fullname: Resident of Russian Federation [type: String]

Make sure that values and types in ADO .Net are defined correctly according to masks set for domains. If a domain mask has not been set or another provider is used, the following text will be displayed after this example has been made:

identity: System.Byte[] [type: Byte[]]
Resident: 1 [type: Int16]
Fullname: Resident of Russian Federation [type: String]

Standard types will be displayed instead of Guid and Boolean.

Source texts of examples for ADO .Net can be downloaded here: Firebird ADO .Net Samples

Tags: Guid, Boolean, Bool, Firebird, InterBase, ADO Entity Framework.