Locate failure when Date format is different to standard

[m227]

Hi, I changed date format in my system from yyyy-MM-dd to Romanian (dd.MM.yyyy). I use TUniConnection with SQLite provider. Locate function stopped working and finding my datetime values, stored in a field defined as:

Code: Select all

Created      DATETIME          NULL

My key used in a Locate function is complex one and defined as: 'UnitID;Author;Created'.

I tried to change SpecificOptions: NativeDate to false and the other time Specificoptions DateFormat and TimeFormat (to ISO) with no progress.

Is there any help?

[ertank]

Hello,

As to my knowledge, SQLite does not have any "datetime" data type. Datetime information is stored as string, or number according to your way of usaging SQLite. My experience with UniDAC, it is stored as string in your database using "yyyy-mm-dd hh:nn:ss" format *always*.

That means, anything you inserted in your table before changing your system date time format is inserted as 'yyyy-mm-dd hh:nn:ss'. That also means anything you inserted in your database after changing to Romanian datetime format is inserted same as above.

So, you should be passing your date field to Locate() function as TDateTime datatype and it will automatically be converted to proper format by UniDAC'.

My test table was as following:

Code: Select all

create table test(a integer, b text, c datetime)

My Locate code was as following:

Code: Select all

procedure TForm1.Button2Click(Sender: TObject);
var
  ADate: TDateTime;
begin
  ADate := EncodeDate(2018, 4, 1);
  UniQuery1.Locate('a;b;c', VarArrayOf([2, 'def', ADate]), []);
end;

Above code could find my record without any problem on both English and Turkish locales.

I did not change any default TUniConnection parameters. Only set below SpecificOptions to True:
Direct = True
ForceCreateDatabase = True

If your problem continues, it would be better to have code samples clearly showing any variable data type you use as parameters for locating.

Regards,
Ertan

[MaximG]

We tested UniDAC work according to your description and found no problems. We composed a small console application provided below:

Code: Select all

program Project1;

{$APPTYPE CONSOLE}

{$R *.res}

uses
  System.SysUtils,
  System.Variants,
  DateUtils,
  Data.DB,
  DBAccess,
  Uni,
  UniProvider,
  SQLiteUniProvider;

var
  UniConnection: TUniConnection;
  UniQuery: TUniQuery;
begin
  UniConnection := TUniConnection.Create(nil);
  try
    UniConnection.ProviderName := 'SQLite';
    UniConnection.Database := ':memory:';
    UniConnection.SpecificOptions.Values['Direct'] := 'True';
    UniConnection.SpecificOptions.Values['DateFormat'] := 'dd.mm.YYYY';
    UniConnection.SpecificOptions.Values['TimeFormat'] := 'HH:MM:SS';
    UniConnection.Connect;
    UniConnection.ExecSQL('CREATE TABLE DT (UnitID INTEGER, Author TEXT, Created DATETIME, PRIMARY KEY (UnitID, Author, Created));');
    UniQuery := TUniQuery.Create(nil);
    try
      UniQuery.Connection := UniConnection;
      UniQuery.SQL.Text := 'Select * From DT ORDER BY UnitID';
      UniQuery.Open;

      UniQuery.Insert;
      UniQuery.FieldByName('UnitID').AsInteger := 1;
      UniQuery.FieldByName('Author').AsString := 'Value 01';
      UniQuery.FieldByName('Created').AsDateTime := EncodeDateTime(1976, 3, 11, 21, 21, 23, 0);
      UniQuery.Post;

      UniQuery.Insert;
      UniQuery.FieldByName('UnitID').AsInteger := 2;
      UniQuery.FieldByName('Author').AsString := 'Value 02';
      UniQuery.FieldByName('Created').AsDateTime := EncodeDateTime(2018, 4, 6, 13, 30, 22, 0);
      UniQuery.Post;

      UniQuery.Insert;
      UniQuery.FieldByName('UnitID').AsInteger := 3;
      UniQuery.FieldByName('Author').AsString := 'Value 03';
      UniQuery.FieldByName('Created').AsDateTime := EncodeDateTime(2000, 1, 1, 12, 00, 01, 0);
      UniQuery.Post;

      UniQuery.Close;
      UniQuery.Open;
      UniQuery.Locate('UnitID;Author;Created', VarArrayOf([2, 'Value 02', EncodeDateTime(2018, 4, 6, 13, 30, 22, 0)]), []);

      WriteLn(UniQuery.FieldByName('UnitID').AsString);
      ReadLn;

      UniQuery.Close;
    finally
      UniQuery.Free;
    end;
    UniConnection.Disconnect;
  finally
    UniConnection.Free;
  end;
end.

You can test its operability in your environment. If the issue is reproduced, please compose and send us a small sample (using the e-support form devart.com the "Support"\"Request Support" menu), execution of which causes the issue. In addition, we need a file from the database used in this sample.