Using String Attributes in ECO IV dbExpress Applications

Today I was converting an ECO III application originally written in Borland Developer Studio 2006, to an ECO IV application using RAD Studio 2007, when I stumbled across an interesting problem. The application talks to an InterBase database, and was using the Borland Data Provider components for its database connectivity. While BDP is still included in RAD Studio 2007, it has been deprecated and dbExpress is now the preferred data access framework to use. So, as well as converting the app to use the newer ECO framework, it also made sense for me to convert it to use dbExpress for database persistence. Thanks to the ECO PersistenceMapper classes, this is a pretty straightforward exercise to do, and normally would simply involve using a new connection and PersistenceMapper class (in this case TAdoDbxConnection and PersistenceMapperDbx respectively).

One of the goals of DBX was to consolidate data access frameworks used in the native and managed development environments. Rather than have 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.

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.

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.

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. In order to get around this problem, we have to implement our own attribute mapper which can then be used by our PersistenceMapperDbx instance when performing database operations for string attributes. 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.

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

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.

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

Edit: Due to popular demand (ok, ok… because John Kaster requested it :-)), I’ve converted this blog post into a CDN article . The content is pretty much the same as this blog post, but with some flowery embellishments to make it more ‘articley’

Share This | Email this page to a friend

Posted by David Clegg on March 18th, 2008 under .NET, Database, ECO, Delphi |

Server Response from: dnrh2.codegear.com