Format a date according to ADO provider

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 se开发者_JAVA技巧nd 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.

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;

Larry, i will give you an answer for the SQL Server Part.

you can use the sys.syslanguages system view to get information about the languages installed in the sql server, one of the columns returned by this view is called dateformat which indicate the Date order, for example, DMY.

also using the @@langid (which returns the local language identifier (ID) of the language that is currently being used.) function you can write something like this to obtain the current date format used by the Sql Server.

select dateformat from sys.syslanguages  where langid=@@langid

so now you will have a string which you can parse in delphi to format your date.

Another option is pick one of the predefined formats of SQL Server and use the CONVERT function in your SQL sentence for convert the string to the date.

check this sample which uses the ISO format to convert

//The ISO  format is yyyymmdd so i can use the FormatDateTime function to convert any TdateTime to a Iso format sdatetiem string
DateStr:=FormatDateTime('YYYYMMDD',Now);
//Now construct the sql server sentence
SqlSentence:=Format('UPDATE MyTable SET DateField=CONVERT(DATETIME,%s,112)',QuotedStr(DateStr));

Try to use the ODBC date escape sequence, which may be supported by SQL Server and Access OLEDB providers. For example:

{d '2011-01-14'}

As Marjan suggests, you shoud always use parameters. (try searching for SQL-injection)

Another reason to use parameters is that query plans can be reused on the sql server. If the first statement generated is SELECT * from customer where created>'2010-12-21' and the next statetement generated is SELECT * from customer where created>'2010-12-22', the query optimizer/plan gets generated/compiled both times (different statement). But if the statement both times are SELECT * from customer where created>?, the plan is reused -> (slightly) lower presure on SQL server.

I you just wan't a quick and dirty solution all the SQL (implementations) i have tried (I havn't tries Access) can accept and understand the ISO date format (eg. '2010-12-21')