InterBase and Firebird in ADO Net. Part 3. Additional features: DDL, Metadata schemas, ODBC Escape Sequences

Database metadata schemes

The important part of all the OLE DB providers is the metadata schemes. They are used by clients for getting the database description: list of stored procedures, table structures, registered domens, limitations, primary and external keys, etc. To work with ADO.Net Library OLE DB provider should support OLE DB schemes, as the library components often use this information.

Click on the following link to see a list of schemes that are supported by IBProvider.

It’s possible to request a specific scheme by its name. For this an OleDbConnection object has a GetSchema() method. In Net 2 there is a GetOleDbSchema() method which takes one of the OleDbSchemaGuid enum values as an argument. Both modes behave the same way and back absolutely similar DataTable copies with an information set of the scheme.

Each scheme has a set of columns, with the help of which it is possible to filter the returned result. For example, in COLUMN scheme is it possible to make limitations for the following fields:

Restriction columns: TABLE_CATALOG, TABLE_SCHEMA, TABLE_NAME, COLUMN_NAME. If we want to get the description of all the columns for EMPLOYEE table we have to use COLUMNS scheme this way:

DataTable schema_table = connection.GetSchema(«COLUMNS», new string[] { null, null, «EMPLOYEE» });

The same for GetOleDbSchema() mode:

DataTable schema_table =
connnection.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, «EMPLOYEE» });

DDL queries. CREATE/ALTER/DROP

This type of queries enables to manage database metadata. You may create, drop and alter the columns, tables and whole databases via SQL — expressions, in the text of those there are DDL instructions. If to use them together with metadata schemes it would be possible to copy the existed database structures and create new ones without any effort.

All DDL queries, excluding CREATE DATABASE and DROP DATABASE, required transaction (in manual on in auto commit mode). By default the auto commit mode for DDD queries is disabled. It is made due to the reason of safety. To turn on the confirmation of DDL queries it is necessary to setup auto_commit_ddl property. Its description you may find in the chapter of this article called Controlling transactions automatically in IBProvider.

I will give the example of DROP DATABASE use for database dropping:

private void DropDatabase()
{
    if (File.Exists(databasePath))
    {
        OleDbConnectionStringBuilder builder =
           CreateConnectionStringBuilderForSample();

        //turn off connection pool
        builder.OleDbServices = OleDbServicesValues.EnableAll &
                                ~OleDbServicesValues.ResourcePooling;

        OleDbConnection con = new OleDbConnection(builder.ToString());
        con.Open();

        new OleDbCommand(«drop database»,con).ExecuteNonQuery();
        con.Close();
    }
}

Pay attention to the fact that for connecting that will be used for database dropping, we turn off any pooling services. IBProvider can inform about OLE DB services that became unavailable, connections, and in this case that action is excessive. But it is left here for solving different problems while using OLE DB providers of other manufacturers:

private OleDbConnection CreateDatabase()
{
    //connect to existing employee.gdb database
    OleDbConnection con = ConnectionProvider.CreateConnection();
    con.Open();

    //create an empty database
    new OleDbCommand(
               «create database ‘» + server_name + «:» + databasePath + «‘\n» +
                       «USER     ‘» + user_name + «‘    \n» +
                       «PASSWORD ‘» + password + «‘ \n», con).ExecuteNonQuery();

    con.Close();

    return new OleDbConnection(
          CreateConnectionStringBuilderForSample().ToString());
}

And, finally, the finished example that firstly drops database, then creates the new one on its place and specifies two tables connected by external key in it:

public void CreateNewDBSample()
{
    DropDatabase();

    OleDbConnection con = CreateDatabase();
    con.Open();
    OleDbTransaction trans = con.BeginTransaction();

    //create SAMPLE_TABLE with 2 columns
    ExecuteDDL(
        «CREATE TABLE SAMPLE_TABLE(                     « + //int column                
        » ID             INTEGER      NOT NULL,         « + //varchar column
        » NAME           VARCHAR(64),                   « + //primary key
        «CONSTRAINT PK_SAMPLE_TABLE PRIMARY KEY(ID)    )», trans);

    //create SAMPLE_TABLE_2 referenced via FOREIGN KEY 
    ExecuteDDL(
        «CREATE TABLE SAMPLE_TABLE_2 (                  « +
        » ID             INTEGER      NOT NULL,         « + //int columns
        » PARENT         INTEGER      NOT NULL,         « + //int column
        «CONSTRAINT PK_SAMPLE_TABLE_2 PRIMARY KEY(ID),   « + //primary key
        «CONSTRAINT FK_SAMPLE_TABLE_PARENT              « +  //foreign key
        «FOREIGN KEY(PARENT) REFERENCES SAMPLE_TABLE(ID))», trans);

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

In latest version of IBProvider v3 you can create database without DDL and database connection

Read the following article for details: How to create a Firebird or InterBase database by using ADOX and Visual C# .NET (or VBScript)

ODBC Escape Sequences

Escape Sequences enable to upgrade the query text in the process of implementation. Sequence is included into the query text in braces. For example, {fn CURDATE} — will be upgraded into the server time value.

In ODBC escape sequences for the following characteristics are specified:

• Work with time and dates
• Type conversion fucntions
• Calling stored procedures
• others

This extension is widely used by such tools as MS SQL Server, Business Intelligence, Crystal Reports, and their support by OLE DB provider gives the possibility of their mutual use.

Moreover, it enables to write queries that do not depend on database. There are escape sequences in MS SQL, Oracle and many other database servers.

To turn on the ODBC extensions support to IBProvider it is necessary to setup the following initialization property: support_odbc_query = true. On default the support is off. Further information about ODBC escape sequences, supported functions you will find in ODBC Escape Sequences Guide.

The following example demonstrates the use of escape sequences in SQL queries text:

public void ODBCQueriesTest()
{
   OleDbConnectionStringBuilder builder =
ConnectionProvider.GetConnectionStringBuilderFromUDL();
   builder.Provider = «LCPI.IBProvider.2»;
   builder.Add(«support_odbc_query»,«true»);

   OleDbConnection con = new OleDbConnection(builder.ToString());
   con.Open();
   OleDbTransaction trans = con.BeginTransaction();

   //select current day name
   OleDbCommand cmd = new OleDbCommand(
       «select « +
       «{fn dayname({fn now()})} as DAY_NAME,» +
       «{fn dayofweek({fn now()})} as DAY_OF_WEEK,» +
       «{fn dayofmonth({fn now()})} as DAY_OF_MONTH,» +
       «{fn dayofyear({fn now()})} as DAY_OF_YEAR « +
       «from RDB$DATABASE», con, trans);

   using (OleDbDataReader rdr = cmd.ExecuteReader())
       if (rdr.Read())
           for (int i = 0; i < rdr.FieldCount; i++)
                  Console.WriteLine(rdr.GetName(i) + «: « + rdr[i].ToString());

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

Conclusion

In my guide with the example of OLE DB provider I reviewed the most often used possibilities of ADO.Net Library. The main advantage of OLE DB Providers in front of escape ones (.Net Data Providers) is the possibility to use them not only in .Net Framework but also in practically any context supporting COM.

I hope that this guide enables you to widen the range of possibilities used in ADO.Net not only for work with Firebird but also with other databases.

Another article parts

Part 1. Firebird and InterBase Developer’s manual for Visual Studio Introduction to ADO.Net.
Part 2. Firebird and InterBase Developer’s manual for ADO .Net. Using code generators and tools of Visual Studio 2005-2008.

Download Examples from the article.
Download IBProvider Professional Edition

Useful references

• New features of Firebird 2.0. Examples for ADO .Net (c#).
• New features of Firebird 2.1. Examples for ADO .Net (c#).
• Using Firebird and InterBase as MS SQL Linked Server.
• InterBase ODBC driver, Firebird ODBC or OLE DB?
• ODBC Escape Sequences.
• Ole Db schemas in MSDN.
• Download Firebird
• Firebird 2 release notes.
• Official Firebird SQL Server site.