FilterSQL works different as in earlier versions

[Yves]

Hello,

The property TUniTable.FilterSQL where a GUID is used in a PostgreSQL table has changed. Our existing stable source code doesn't work any more. In Devart version 7.0.2. a '{' and '}' were not allowed to work properly In Devart version 7.1.4. a '{' and '}' are required to work properly

Below a small demo example to show the difference with comment for all details.
To demostrate change the connectionstring in the edit box. A small table with values is generated automatically in the database.

The result of the memo of Devart version 7.0.2:

Connected Provider Name=PostgreSQL;Data Source=127.0.0.1;Port=5432;Database=SomeDatabase;User ID=UserID;Password=1234
sometesttable created.
0A157616-5737-46C6-8DB4-0EEA7F51979A located {0A157616-5737-46C6-8DB4-0EEA7F51979A} not located
1 record(s) found where titleid = 0A157616-5737-46C6-8DB4-0EEA7F51979A
1 record(s) found where titleid = {0A157616-5737-46C6-8DB4-0EEA7F51979A}

The same project compiled with Devart version 7.1.4

Connected Provider Name=PostgreSQL;Data Source=127.0.0.1;Port=5432;Database=SomeDatabase;User ID=UserID;Password=1234
sometesttable created.
C22A0B63-A32C-4D28-8DFD-037F727D52E6 not located
{C22A0B63-A32C-4D28-8DFD-037F727D52E6} located
1 record(s) found where titleid = C22A0B63-A32C-4D28-8DFD-037F727D52E6
1 record(s) found where titleid = {C22A0B63-A32C-4D28-8DFD-037F727D52E6}

Conclusion: the use of '{' and '}' is in conflict between Devart version 7.0.2 and 7.1.4 in the TUniTable.FilterSQL property.

In a lot of projects and units the FilterSQL is used to access only one database row with all fields.
The changed behaviour from 7.0.2 to 7.1.4 results in our projects in a lot of bugs caused by the changed implementation by Devart.

I would really appreciate if the behaviour is turned back as in version 7.0.2. Or maybe better to allow both situation: with and without the use of '{' and '}' just like a parameter of TUniQuery as shown in the demo application.

Demo application sdiapp.dpr

Code: Select all

program Sdiapp;

uses
  Forms,
  SDIMAIN in 'SDIMAIN.pas' {SDIAppForm};

{$R *.RES}

begin
  Application.Initialize;
  Application.CreateForm(TSDIAppForm, SDIAppForm);
  Application.Run;
end.

SDIMAIN.PAS

Code: Select all

unit SDIMAIN;

interface

uses Winapi.Windows, System.Classes, Vcl.Graphics, Vcl.Forms, Vcl.Controls,
  Vcl.Menus, Vcl.Dialogs, Vcl.StdCtrls, Vcl.Buttons, Vcl.ExtCtrls, Vcl.ComCtrls,
  Vcl.ImgList, Vcl.StdActns, Vcl.ActnList, Vcl.ToolWin, Data.DB, MemDS,
  DBAccess, Uni, UniProvider, PostgreSQLUniProvider;

type
  TSDIAppForm = class(TForm)
    Memo1: TMemo;
    UniConnection1: TUniConnection;
    UniTable1: TUniTable;
    EditConnection: TEdit;
    ButtonOpen: TButton;
    ButtonClose: TButton;
    LabelConnection: TLabel;
    EditTitleID: TEdit;
    LabelTitleID: TLabel;
    ButtonLocateTitleID: TButton;
    UniQuery1: TUniQuery;
    ButtonQueryCount: TButton;
    procedure ButtonOpenClick(Sender: TObject);
    procedure ButtonCloseClick(Sender: TObject);
    procedure UniConnection1AfterConnect(Sender: TObject);
    procedure UniConnection1AfterDisconnect(Sender: TObject);
    procedure ButtonLocateTitleIDClick(Sender: TObject);
    procedure ButtonQueryCountClick(Sender: TObject);
    procedure Memo1DblClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  SDIAppForm: TSDIAppForm;

implementation

uses
  System.SysUtils,
  System.Types;

{$R *.dfm}

procedure TSDIAppForm.ButtonCloseClick(Sender: TObject);
begin
  UniConnection1.Close;
end;

procedure TSDIAppForm.ButtonLocateTitleIDClick(Sender: TObject);
var Located: Boolean;
    TitleId: String;
begin
  TitleId := EditTitleID.Text;

  //Without '{' '}'
  if (Length(TitleId) = 38) then
    TitleId := Copy(TitleID, 2, 36); //Comment A: '{' and '}' are not allowed in Devart 7.0.2
  UniTable1.FilterSQL := Format('TitleID = ''%s''', [TitleId]);
  Located := UniTable1.Locate('TitleID',  TitleId, [loCaseInsensitive]);
  if Located then
    Memo1.Lines.Add(TitleId + ' located')
  else
    Memo1.Lines.Add(TitleId + ' not located');

  //With '{' '}'
  TitleId := '{' + TitleId + '}'; //Comment B: '{' and '}' are required in Devart 7.1.4
  UniTable1.FilterSQL := Format('TitleID = ''%s''', [TitleId]);
  Located := UniTable1.Locate('TitleID',  TitleId, [loCaseInsensitive]);
  if Located then
    Memo1.Lines.Add(TitleId + ' located')
  else
    Memo1.Lines.Add(TitleId + ' not located');

// Conclusion of both comments:
// Comment A for Devart version 7.0.2 is in conflict with comment B Devart version 7.1.4
end;

procedure TSDIAppForm.ButtonOpenClick(Sender: TObject);
var s: string;
    i: integer;
    G: TGUID;
begin
  UniConnection1.ConnectString := EditConnection.Text;
  UniConnection1.Open;

  s := 'INSERT INTO sometesttable (titleid, firstfield, secondfield) VALUES' + sLineBreak;
  for i := 1 to 50 do
  begin
    G := G.NewGuid;
    if (i = 25) then
      EditTitleID.Text := G.ToString;
    s := Format('%s  (''%s'', ''%d'', ''%d''),' + sLineBreak, [s, G.ToString, i, 100 + i]);
  end;
  s := Format('%s (''%s'', ''51'', ''151'');', [s, G.NewGuid.ToString]) + sLineBreak;

  UniQuery1.SQL.Text :=
    'DO $$' + sLineBreak +
    'BEGIN' + sLineBreak +
    'DROP TABLE IF EXISTS sometesttable;' + sLineBreak +
    'CREATE TABLE sometesttable' + sLineBreak +
    '(' + sLineBreak +
    '  titleid          UUID NOT NULL PRIMARY KEY,' + sLineBreak +
    '  firstfield       CHARACTER VARYING,' + sLineBreak +
    '  secondfield      CHARACTER VARYING' + sLineBreak +
    ');' + sLineBreak +
    '' + sLineBreak +
    s +
    'END;' + sLineBreak +
    '$$';
 UniQuery1.ExecSQL;
 Memo1.Lines.Add('sometesttable created.');

  // Always is only 1 record requiered with all fields to prevent all records are loaded
  UniTable1.FilterSQL := 'titleid = ''{00000000-0000-0000-0000-000000000000}''';
  UniTable1.Open;
end;

procedure TSDIAppForm.ButtonQueryCountClick(Sender: TObject);
var TitleId: string;
    Count: Integer;
begin
  TitleId := EditTitleID.Text;

  //Without '{' '}'
  if (Length(TitleId) = 38) then
    TitleId := Copy(TitleID, 2, 36);

  UniQuery1.SQL.Text :=
    'SELECT count(*) FROM sometesttable' + sLineBreak +
    'WHERE titleid = :P0' + sLineBreak;
  UniQuery1.Params[0].DataType := ftGuid;
  UniQuery1.Params[0].Value := TitleId;
  UniQuery1.Open;
  Count := UniQuery1.Fields[0].AsInteger;
  UniQuery1.Close;
  Memo1.Lines.Add(IntToStr(Count) + ' record(s) found where titleid = ' + TitleId);

  //With '{' '}'
  TitleId := '{' + TitleId + '}';
  UniQuery1.Params[0].DataType := ftGuid;
  UniQuery1.Params[0].Value := TitleId;
  UniQuery1.Open;
  Count := UniQuery1.Fields[0].AsInteger;
  UniQuery1.Close;
  Memo1.Lines.Add(IntToStr(Count) + ' record(s) found where titleid = ' + TitleId)
end;

procedure TSDIAppForm.Memo1DblClick(Sender: TObject);
begin
  Memo1.Lines.Clear;
end;

procedure TSDIAppForm.UniConnection1AfterConnect(Sender: TObject);
begin
  Memo1.Lines.Add('Connected ' + UniConnection1.ConnectString);
end;

procedure TSDIAppForm.UniConnection1AfterDisconnect(Sender: TObject);
begin
  Memo1.Lines.Add('Disconnected');
end;

end.

SDIMAIN.dfm

Code: Select all

object SDIAppForm: TSDIAppForm
  Left = 197
  Top = 111
  Caption = 'Devart UniDac UniTable.Locate vs Recordcount Query'
  ClientHeight = 386
  ClientWidth = 984
  Color = clBtnFace
  Font.Charset = DEFAULT_CHARSET
  Font.Color = clWindowText
  Font.Height = -11
  Font.Name = 'Tahoma'
  Font.Style = []
  OldCreateOrder = True
  DesignSize = (
    984
    386)
  PixelsPerInch = 96
  TextHeight = 13
  object LabelConnection: TLabel
    Left = 188
    Top = 11
    Width = 54
    Height = 13
    Caption = 'Connection'
    FocusControl = EditConnection
  end
  object LabelTitleID: TLabel
    Left = 8
    Top = 42
    Width = 31
    Height = 13
    Caption = 'TitleID'
    FocusControl = EditTitleID
  end
  object Memo1: TMemo
    Left = 8
    Top = 68
    Width = 968
    Height = 310
    Anchors = [akLeft, akTop, akRight, akBottom]
    ReadOnly = True
    ScrollBars = ssBoth
    TabOrder = 0
    OnDblClick = Memo1DblClick
  end
  object EditConnection: TEdit
    Left = 248
    Top = 8
    Width = 704
    Height = 21
    Anchors = [akLeft, akTop, akRight]
    TabOrder = 1
    Text = 
      'Provider Name=PostgreSQL;Data Source=127.0.0.1;Port=5432;Databas' +
      'e=SomeDatabase;User ID=UserID;Password=1234'
  end
  object ButtonOpen: TButton
    Left = 8
    Top = 6
    Width = 75
    Height = 25
    Caption = 'Open'
    TabOrder = 2
    OnClick = ButtonOpenClick
  end
  object ButtonClose: TButton
    Left = 96
    Top = 6
    Width = 75
    Height = 25
    Caption = 'Close'
    TabOrder = 3
    OnClick = ButtonCloseClick
  end
  object EditTitleID: TEdit
    Left = 45
    Top = 39
    Width = 257
    Height = 21
    TabOrder = 4
    Text = '005210b4-a29f-41d3-aa76-6d12c66668ff'
  end
  object ButtonLocateTitleID: TButton
    Left = 308
    Top = 37
    Width = 121
    Height = 25
    Caption = 'Locate'
    TabOrder = 5
    OnClick = ButtonLocateTitleIDClick
  end
  object ButtonQueryCount: TButton
    Left = 435
    Top = 37
    Width = 121
    Height = 25
    Caption = 'Query count TitleID'
    TabOrder = 6
    OnClick = ButtonQueryCountClick
  end
  object UniConnection1: TUniConnection
    AfterConnect = UniConnection1AfterConnect
    AfterDisconnect = UniConnection1AfterDisconnect
    Left = 40
    Top = 120
  end
  object UniTable1: TUniTable
    TableName = 'sometesttable'
    Connection = UniConnection1
    Left = 136
    Top = 120
  end
  object UniQuery1: TUniQuery
    Connection = UniConnection1
    Left = 216
    Top = 120
  end
end

Best regards

Yves

[azyk]

Thank you for the information. We've reproduced the specified behavior and will try to fix in the shortest time.

[Yves]

Thank you for the information. In what term can a fix be expected? Unfortunately, at the moment I have no other choice then to downgrade to version 7.0.2. what I don't preffer.

[azyk]

We fixed the Locate method behavior for uuid type. Now, it locates both values with braces and without them, i.e.

Code: Select all

0A157616-5737-46C6-8DB4-0EEA7F51979A
{0A157616-5737-46C6-8DB4-0EEA7F51979A}

UniDAC release with this fix is already available for download.

[Yves]

Great, thank you very much!

[ViktorV]

Thank you for the interest in our products.
If you have any questions during using our products, please don't hesitate to contact us - and we will try to help you solve them.