Borland Data Provider 2.5 Features

By: Ramesh Theivendran

Abstract: This article introduces the Borland Data Provider 2.5 features that shipped with Delphi 2006

Table of Contents

Borland Data Provider (BDP) 2.5

Introduction

BDP Connection pooling

Hide image

Connection pooling properties

New methods in BdpConnection

BDP SQL Tracing

BDP Resolver conflict resolution

Customizable SQL type mapping for Data Migration

Extended Metadata

Interface changes in BDP 2.5

Borland.Data.Common.ISQLCursor

Borland.Data.Schema.ISQLResolver

Borland.Data.Schema.ISQLSchemaCreate

Borland.Data.Schema.ISQLMetaData

Borland.Data.Schema.ISQLExtendedMetadata

    Borland Data Provider (BDP) 2.5

by Ramesh Theivendran, Principal Architect

    Introduction

This article highlights the various features that were added to Borland Data Provider (BDP) 2.5 that ships with Borland Developer Studio 2006. It is assumed that you are already familiar with the ADO.NET data-access model and have worked with one of the standard ADO.NET data providers.

BDP 2.5 new features include support for connection pooling, tracing of SQL statements, automatic conflict resolution, extended metadata retrieval, customizable SQL type mapping for data migration, schema create enhancements to support quoting objects and a new MySQL 4.0.24 provider. Overall, most of the features are added to enhance runtime connectivity. Some of the features, such as the schema create enhancements, are added for better design-time and tooling support via the Data Explorer.

    BDP Connection pooling

Establishing a new database connection can sometimes be very expensive as it involves allocating client and server resources, authenticating the user, etc. Applications can significantly improve performance by establishing a connection and reusing the same connection for subsequent requests. Since database connections are not always active while a client is locally processing data, it is possible for a single connection to be shared across multiple clients. So, a connection pool, which is a cache of database connections, can improve both the performance and scalability of an application especially in a multi-tier architecture.

In ADO.NET, a connection pool is identified by a unique connection string. When a new connection is opened, a new connection pool is created if the connection string is not an exact match to any existing pool. When a new connection pool is created, a minimum number of connection objects are created and added to the pool. If all the existing connections in the pool are being used then new connections are added to the pool up to the maximum pool size. The defaults for connection pooling parameters such as Min Pool Size, Max Pool Size can be overridden in the connection string.

BDP 2.5 now supports connection pooling. BdpPoolManager, BdpPool and ConnectionProperties are internal sealed classes that implement the BDP connection pooling subsystem. BdpPoolManager is a singleton for connection dispatching, and maintains a list of pools. Each pool is an instance of a BdpPool. BdpPool holds a stack of connections anywhere in between Min Pool size and Max Pool Size. Pools are created based on some of the ConnectionString properties. Pool properties include Database, UserName, Password, HostName, OsAuthentication, Pooling, Min Pool Size,

Max Pool Size, Connection Lifetime, GrowOnDemand. Currently, all these pooling properties are used to create a unique pool, and there is no option to customize the pooling properties for creating a connection pool. (In a future release, PoolKeys (currently internal) will be exposed so that users can control the pooling properties.) ConnectionProperties is an internal class for holding the Connection String name value pairs and identifying unique connection strings.

Connection pooling is enabled by default and the Pooling property can be set to false to disable connection pooling. Both pooled and unpooled connections go through the BdpPoolManager. An important advantage to note with the BdpPoolManager is that third party BDP drivers do not have to implement any new interface to take advantage of BDP connection pooling because the BDP core technology takes care of it for the driver. However, if you were to implement your own

ADO.NET provider, you will have to implement your own connection pooling.

    Hide image

Figure 1: Connection pooling

    Connection pooling properties

Pooling - Determines if connection pooling should be enabled. Default is True.

Min Pool Size - Determines the minimum number of connections that should be maintained in the pool. Default is 0. At the time of creation of a new pool MinPoolSize determines how many connections need to be created. For example, if you set MinPoolSize to 10, when a new pool is first created by the introduction of a new unique connection string, 10 connections will be created and added to the pool.

Max Pool Size – Determines the maximum number of connections to which the pool can grow. Default is 100. If GrowOnDemand is false and MaxPoolSize is reached, subsequent connection requests will throw an exception.

Grow On Demand - Determines if new connection request should grow on demand after a pool reaches the MaxPoolSize. Connection that grow on demand will not be returned to the pool. Instead, they will be released when applications call BdpConnection.Close(). Default is True.

Connection Lifetime - Determines the life time of a pooled connection. When a connection returns to the pool its lifetime is checked to see if it has expired. If it is expired, then the connection is released instead of returning to the pool. ConnectionLifetime is in seconds and the default is 0 (no expiry).

    New methods in BdpConnection

When BdpConnection.Open() is called and pooling is true, connection pooling is enabled implicitly. Similarly, when BdpConnection.Close() is called connections are returned to the pool or released based on the ConnectionLifetime. However, there are new methods added to BdpConnection to get pool statistics and to clear pools.

ClearPool(String)

Frees up all the connection in a given pool.

ClearPools()

Frees up all the connections in all the pools

GetAllPoolStatistics()

Get connection statistics for all the pools

GetPoolStatistics(String)

Get connection statistics for a given pool

    BDP SQL Tracing

BDP 2.5 now allows SQL statement executed by provider to be traced. The SQL statements that can be traced include any application specific SQL that the application is executing, metadata retrieval SQL executed by the provider, and any SQL that is generated by the BDP resolver for resolving changes.

To enable tracing, set Tracing = True in the BdpConnection editor which in turn will set BdpConnection.ConnectionOptions. All the SQL statements will then be automatically traced into the Windows Event Viewer. You can redirect tracing by hooking the Tracing event in BdpConnection. Once that is done, any time a SQL is prepared or executed, this event will be triggered and BdpTracingEventArgs.Message will have the SQL.

The following code snippet will show a message box with the SQL being executed.

procedure TWinForm.BdpConnection1_Tracing(sender: System.Object; e: Borland.Data.Common.BdpTracingEventArgs);

begin

MessageBox.Show(e.Message);

end;

Hide image
Click to see full-sized image

Figure 2: BDP tracing showing a SQL in Windows event viewer

    BDP Resolver conflict resolution

In BDP, DataSet changes can be resolved back to the database by either calling BdpDataAdapter.Update() or BdpDataAdapter.AutoUpdate(). BdpDataAdapter.Update() uses static SQL and doesn’t handle resolving NULL data correctly. It is mainly useful when you want to provide the SQL for resolving. For example: when you resolve changes by executing stored procedures for INSERTs, UPDATEs and DELETEs.

If you want BDP to automatically resolve single DataTable and master-detail DataSet changes BdpDataAdapter.AutoUpdate() is recommended instead.

During the resolving process, errors can occur because of constraint violations on the database. In a multi-user environment, the data on the server might have been modified by other users and as a result an optimistic locking would fail. When such errors happen in ADO.NET a concurrency violation exception will be thrown.

Application developers can handle conflict resolution by iterating through all the DataTables in the DataSet and look for errors by checking the DataTable.HasErrors property. Once a DataTable with errors is found you can call DataTable.GetErrors() to get an array of DataRows with errors. Then you can iterate through the DataRows and call DataRow.GetColumnsInError() to return an array of DataColumns with error. At this point, depending upon the error, you can either skip the errors by calling DataRow.ClearErrors() or attempt to resolve the conflict by retrieving the error row from the database and reconciling it with the changes in the DataSet.

BDP 2.5 now automatically handles error reconciliation when BdpDataAdapter.AutoUpdate() or DataHub.ApplyChanges() fails. All you need to do is hook the OnUpdateError event in BdpDataAdapter or DataHub and create a ReconcileErrorForm as shown in the following code snippet.


procedure TWinForm.BdpDataAdapter1_OnUpdateError(sender: System.Object; e: Borland.Data.Common.BdpUpdateErrorEventArgs);
var f : ReconcileErrorForm;
begin
    f := ReconcileErrorForm.Create(e);
    f.ShowDialog();
end;

For each error, the OnUpdateError event is triggered and the BdpUpdateErrorEventArgs has ErrorRow and ServerTable which provides the error row and the current data on the database to the ReconcileErrorForm. On the ReconcileErrorForm you have options to retry the update using primary key, skip the current error and continue resolving other changes or abort all the changes.

When working with DataHub and DataSync, error reconcile is not enabled by default. To enable error reconcile you need to set DataSync.Reconcile property to true. DataHub error reconcile will also work in a multi-tier distributed application when hosting a DataSync on a .NET Remoting server using RemoteServer and RemoteConnection components.

Hide image
Click to see full-sized image

Figure 3: BDP Error reconcile form

    Customizable SQL type mapping for Data Migration

BDP allows migrating tables from one BDP data source to another easily. The data type mappings are built into the provider and can be persisted into an XML file by calling ISQLSchemaCreate.WriteSQLTypeMapping. Users can now change the mapping in the XML file

and use the new mapping for data migration after loading the mapping by calling ISQLSchemaCreate.ReadSQLTypeMapping. Currently, there is no UI for customizing the mapping, and future releases will make this process easier.

BdpCopyTable by default uses the built-in SQL type mapping and this can be changed by setting the SQLTypeMapping property to the modified mapping file as shown in the following code snippet.


procedure TWinForm3.Button1_Click(sender: System.Object; e: System.EventArgs);
var
 md: ISQLMetaData;
 sc: ISQLSchemaCreate;
begin
     if (bdpconnection1.State <> ConnectionState.Open) then
       bdpConnection1.Open
    md := bdpConnection1.GetMetaData;
    sc := md.GetSchemaCreate;
    sc.WriteSQLTypeMapping('c:\demos\bdpSQLMapping.xml',true);
  end;

procedure TWinForm3.Button2_Click(sender: System.Object; e: System.EventArgs);
begin
    bdpCopyTable1.SQLTypeMapping :='c:\demos\bdpSQLMapping.xml';
    bdpCopyTable1.Copy;   
end;

    Extended Metadata

For retrieving foreign keys associated with a table and related objects for a given database object a new interface ISQLExtendedMetadata was introduced in BDP 2.5 called Borland.Data.Schema.ISQLMetaData. GetExtendedMetaData() returns a ISQLExtendedMetadata implementation for the provider. Currently, this is only supported in INTERBASE, ORACLE, MSSQL and SYBASE.

    Interface changes in BDP 2.5

While we try to avoid interface changes, every major release we take the opportunity to make some minor interface changes to address some of the shortcomings or prior design limitations.

    Borland.Data.Common.ISQLCursor


   public interface ISQLCursor
   {
     
     //New method added
    Int32 GetClob(Int16 uColumn, ref char[] buffer, ref Int32 iInd,        
                  Int32 iLength );
   }

GetClob() is added for returning character LOBs as a array of System.Char so that we can return Unicode data.

    Borland.Data.Schema.ISQLResolver


   public interface ISQLResolver
   {
      
      //New method added
      String GetRefreshSQL(IDbConnection Conn, IDbCommand Command,
         DataRowCollection columns, String TableName);
   }

GetRefreshSQL() returns the SQL needed to refresh a single record from the database. For GetRefreshSQL to work there needs to be a primary key or unique key.

    Borland.Data.Schema.ISQLSchemaCreate


   public interface ISQLSchemaCreate
   {
    
    //New methods added
    void ReadSQLTypeMapping(String FileName);
    void WriteSQLTypeMapping(String FileName, bool bDefault);
   }

ReadSQLTypeMapping()- Loads SQL type mapping between data sources from a XML document and uses the mapping for Data migration

WriteSQLTypeMapping()- Write the current or default SQL type mapping between data sources into a XML document.

    Borland.Data.Schema.ISQLMetaData


    publicinterface ISQLMetaData
       {
          ...
    //New method added
        ISQLExtendedMetaData GetExtendedMetaData();
       }

    Borland.Data.Schema.ISQLExtendedMetadata


    //New interface added
    publicinterface ISQLExtendedMetaData
      {
    //Get dependent objects of a particular type for the given
    //database object name and type
        DataTable GetRelatedObjects(
    String szObjectName, 
          ObjectType eObj,
          ObjectType eRelObj);
    //Get Foreign Key
        DataTable GetForeignKeys(
    String szBaseTableName,
    String szFKeySchemaName, 
    String szFKeyTableName);
    }

Server Response from: ETNASC04