Format a date according to ADO provider

Question

I have a Delphi 2010 application using ADO to support a database that can be either SQL Server or MS Access. When sending SQL to the database using parameterized queries differences in date representation are handled correctly. But I occasionally have the need to form dynamic SQL and send that to the database as well.

Is there any way to either have the TADOConnection format my date into a text string appropriate for the current database, or to interrogate the connection to learn how I should format the date? Otherwise I'm stuck building a table of provider names and date formatting functions.

Answer 1

You should be able to use parameters with dynamic sql as well. I have done this in several versions of my own OPF framework.

Just write a SQL statement using parameters, assign that as a string to the SQL text of a TAdoQuery (or TAdoCommand). The component should then parse your text and set up the parameters collections for you. After that you should be able to assign the values to your parameters and call Open or Execute...

To give you an idea:

  DS := DatasetClass.Create( self.Connection );
  try
    DS.QueryText := SQL.Text;
    FillSelectParams( DS );
    DS.Open;
    try
      ...
    finally
      DS.Close;
    end;
  finally
    DS.Free;
  end;

In which the FillSelectParams calls the following FillParams procedure:

procedure TSQLDataManager.FillParams(ADS: TCustomDataset);
var
  i: integer;
  ParamName: string;
  Attr: TCustomDomainAttribute;
  Ref: TCustomDomainObject;
  Value: Variant;
begin
  for i := 0 to ADS.ParamCount - 1 do begin
    Value := Null;
    ParamName := ADS.ParamName[i];
    if ParamName = 'Id' then begin
      ParamName := 'Identity';
    end;
    Attr := CDO.AttrByName[ParamName];
    if Attr <> nil then begin
      Value := ADS.AdjustParamValue( Attr );
    end else begin
      Ref := CDO.ReferenceByName[ParamName];
      if ( Ref <> nil ) and ( Ref.Identity <> C_UnassignedIdentity ) then begin
        Value := Ref.Identity;
      end;
    end;
    if Value <> Null then begin
      ADS.ParamValue[i] := Value;
    end;
  end;
end;

In this case the param values are taken from a custom domain object (CDO), but you can substitute your own flavour here.

The AdjustParamValue function takes care of a couple of conversions. It has been implemented in the ADO version of the TCustomDataSet class descendant used, to take care of component variations with different TDataSet descendants, but nowhere does the SQL database type come into play:

function TADODCDataset.AdjustParamValue(Attr: TCustomDomainAttribute): Variant;
begin
  if Attr is TIdentityAttribute then begin
    if Attr.AsInteger = 0 then begin
      Result := Null;
    end else begin
      Result := Attr.Value;
    end;
  end else if Attr is TBooleanAttribute then begin
    if Attr.AsBoolean then begin
      Result := Integer( -1 );
    end else begin
      Result := Integer( 0 );
    end;
  end else if Attr is TDateTimeAttribute then begin
    if Attr.AsDateTime = 0 then begin
      Result := Null;
    end else begin
      Result := Attr.Value;
    end;
  end else if Attr is TEnumAttribute then begin
    Result := Attr.AsString
  end else begin
    Result := Attr.Value;
  end;
end;