Using String Attributes in ECO IV dbExpress Applications

By: David Clegg

Abstract: This article discusses a potential problem with using string attributes in ECO IV applications which use the dbExpress persistence mapper, and provides a solution to overcome it.

    Introduction

ECO provides an excellent framework to create persistence agnostic applications. It comes with persistence mappers for the most popular databases, as well as providing persistence mappers for other persistence mechanisms (such as persisting to an XML file).

This agnostic approach can sometimes cause problems if the default configuration is used when creating ECO applications. This article discusses one such problem when using string attributes in an ECO application which uses the dbExpress persistence mapper, and shows how the flexibility and extensibility of the ECO framework allows it to be overcome with relative ease.

    The Problem

    Another persistence agnostic framework

Just as ECO provides a framework to persist data to a wide variety of persistence providers, dbExpress offers a database vendor independent framework allowing data to be persisted to different databases via a common interface.

    Unifying the dbExpress interface

One of the goals of the new dbExpress framework introduced with RAD Studio 2007 was to consolidate data access frameworks used in native and managed development environments. Rather than having different data access framework implementations for both Win32 and .NET, dbExpress drivers were implemented using native Delphi, and a bridge to these drivers was created so that .NET applications can consume them using the ADO.NET classes and interfaces in the System.Data.Common namespace (such as DbConnection and DbCommand). For more details on this, see Steve Shaughnessy's excellent blog post discussing the new dbExpress features

    Providing extra functionality via a ‘Common Denominator’ interface

One of the challenges raised by this approach is that dbExpress exposes a superset of functionality than what is exposed via the classes and interfaces in System.Data.Common. An example of this is that dbExpress parameters can have a data type and subtype specified, whereas the System.Data.IDataParameter interface only exposes a DbType property. As a result, if you set the parameter type for a dbExpress parameter using the IDataParameter interface or DbParameter class, the dbExpress implementation of this class (TAdoDbxParameter) must try to make a 'best guess' choice when setting the value of the TAdoDbxParameter.DbxType and TAdoDbxParameter.DbxSubType properties. Any time there is an ambiguity between the DbType enumeration value and possible TDbxDataTypes values, TDbxDataTypes.UnknownType is used. Likewise, setting TAdoDbxParameter.DbxType results in another 'best guess' choice to set the value of the TAdoDbxParameter.DbType property.

One particular instance where this can bite is when attempting to use string parameters to access an InterBase varchar column or parameter. Setting TAdoDbxParameter.DbType to DbType.String results in TAdoDbxParameter.DbxType being set to TDbxDataTypes.BlobType, and TAdoDbxParameter.DbxSubType set to TDBXDataTypes.WideMemoSubType. So when executing a parameterized query or stored procedure using these data types, you will get an exception raised due to invalid data types being specified.

    Solving this problem for dbExpress

There are two approaches that can be taken to solve this problem. You can either set the TAdoDbxParameter.DbxType parameter explicitly to the correct type (TDbxDataTypes.AnsiStringType or TDbxDataTypes.WideStringType, depending on the character set being used for the varchar column). Or you could set the DbParameter.DbType property to DbType.StringFixedLength (maps to TDbxDataTypes.WideStringType), or DbType.AnsiStringFixedLength (maps to TDbxDataTypes.AnsiStringType). In order to see which TDbxDataTypes value is used for each DbType enumeration member, look in the AdoDbxClient provider source code located in <RAD Studio Install Directory>\source\database\src\pas\dbx\driver\Borland.Data.AdoDbxClientProvider.pas. In particular, check out the TAdoDbxProviderFactory.AdoToDbxType procedure.

    How this applies to ECO

For ECO applications, there is no dbExpress specific attribute mapper for string attributes, so the Eco.Persistence.Default.StringAsVarChar attribute mapper is used. Because this attribute mapper performs all operations via common ADO.NET interfaces, it means that setting the data type for a parameter is done via the IDataParameter interface. So setting the type for a string attribute is done by setting IDataParameter.DbType to DbType.String. As discussed above, for TAdoDbxParameter instances this will result in the incorrect data types being used by the dbExpress framework.

    A Solution

    Framework extensibility to the rescue

As hinted at above, the solution to this problem lies in exploiting the attribute mapping portion of ECOs persistence framework. Attribute mappers are responsible for telling ECO how particular class attributes should communicate with the various persistence mechanisms. They provide functionality such as helping ECO to use the correct data type syntax when forming SQL statements, and helping it to configure and populate parameters used in parameterized queries. It is this second piece of functionality we need to override in order to persist string attributes in ECO classes via the dbExpress framework.

    Creating a custom attribute mapper

So, in order to get around this problem, we have to implement our own attribute mapper for ECO string attributes which can then be used by our PersistenceMapperDbx instance when performing database operations. Here is an example implementation:-

unit CDN.Eco.StringAsDbxVarChar;

interface

uses
  System.Globalization,
  Eco.Persistence.Default, Eco.Persistence, System.Data;

type
  StringAsDbxVarChar = class(AbstractStringSingleColumnAttribute,
   ISingleColumnAttributeMapping)
  protected
    function GetDbType: DbType; virtual;
  public
    function ValueType: System.Type;
    function ColumnType(ALength: Integer): string;
    procedure ValueToParameter(AValue: System.Object; 
      AParameter: IDataParameter);
    procedure StringToParameter(AValue: string; AParameter: IDataParameter);
    function ColumnToValue(AColumnValue: TObject): System.Object; override;
  end;

  AnsiStringAsDbxVarChar = class(StringAsDbxVarChar)
  protected
    function GetDbType: DbType; override;
  end;

implementation

function StringAsDbxVarChar.ValueType: System.Type;
begin
  Result := typeof(System.String);
end;

function StringAsDbxVarChar.ColumnType(ALength: Integer): string;
begin
  if ALength > 0 then
    Result := System.String.Format(CultureInfo.InvariantCulture, 'VARCHAR({0:d})
    ', [TObject(ALength)])
  else
    Result := 'VARCHAR';
end;

function StringAsDbxVarChar.GetDbType: DbType;
begin
  Result := DbType.StringFixedLength;
end;

procedure StringAsDbxVarChar.ValueToParameter(AValue: System.Object; 
  AParameter: IDataParameter);
begin
  if not Assigned(AParameter) then
    raise ArgumentNullException.Create('AParameter');
  EnsureType(AValue, typeof(System.String));
  AParameter.DbType := GetDbType;
  if not Assigned(AValue) then
    AParameter.Value := DbNull.Value
  else
    AParameter.Value := AValue;
end;

procedure StringAsDbxVarChar.StringToParameter(AValue: string; 
  AParameter: IDataParameter);
begin
  ValueToParameter(AValue, AParameter);
end;

function StringAsDbxVarChar.ColumnToValue(
  AColumnValue: System.Object): System.Object;
begin
  EnsureType(AColumnValue, typeof(System.String));
  if (DBNull.Value.Equals(AColumnValue)) then
    Result := nil
  else
    Result := AColumnValue;
end;

{ AnsiStringAsDbxVarChar }

function AnsiStringAsDbxVarChar.GetDbType: DbType;
begin
  Result := DbType.AnsiStringFixedLength;
end;

end.

Ideally we would be able to inherit from the Eco.Persistence.Default.StringAsVarChar class, and simply re-implement the ValueToParameter procedure. But because this class is marked as sealed, we instead have to inherit from the Eco.Persistence.Default.AbstractStringSingleColumnAttribute class, and implement all the methods of the Eco.Persistence.ISingleColumnAttributemapping interface. The implementation for the these methods have been taken directly from the StringAsVarChar class (the source of which is in <Program Files>\CapableObjects\ECO\4.0\Source\Persistence\DefaultAttributeMappers.cs. The above code has implementations to cater for varchar columns containing Ansi or widestring data.

    Using custom attribute mappers

In order to use one of these attribute mappers, we need to invoke the PersistenceMapperDefinition Collection Editor for our PersistenceMapperDbx component. This is done by expanding the SqlDatabaseConfig property, and clicking on the ellipsis at the end of the PersistenceMapper property

Hide image
Click to see full-sized image

We then select the System.String item, and set the value in the property editor to the fully qualified class name of the attribute mapper we wish to use.

Hide image
Click to see full-sized image

This custom attribute mapper will then be used when any string class attributes require interaction with the database.

    Summary

Any framework that provides an agnostic approach to solve a particular problem domain, can run the risk of having problems due to needing to expose a ‘lowest common denominator’ interface to its functionality. Well designed frameworks will cater for this in their design, and will allow for implementation specific behaviour to be included where applicable.

ECO is no different in this regard, and allows the developer to change the way it interacts with the different persistence mechanisms it supports, as well as providing the opportunity to create custom persistence providers for pretty much any persistence mechanism you desire. This article showed an example of this, by describing how ECO can be expanded to overcome a particular issue persisting ECO string attributes when using the provided dbExpress persistence mapper.

Server Response from: ETNASC03