Using Custom Query Builder

Erik van der GaagErik van der Gaag New Zealand
edited 6:43AM in FastReport 4.0
Hi there,

We are wanting to use a different Query Builder other than FastQB.

We have found another Query Builder ( Active Query Builder) which does a very nice job, and also allows me to restrict tables and fields in the builder as I like.

However, How can I use this as my Query Builder instead of FastQB? ? Is there any way to do this?

Thanks,
Erik.

Comments

  • khhkhh
    edited 6:43AM
    It will be great to do this!
  • Erik van der GaagErik van der Gaag New Zealand
    edited 6:43AM
    Hi,

    I have worked out a way to do this, but its a little complicated.

    Basically I have overridden the Query and Editor components, and over rode the Edit function in the SqlProperty editor, and changed the code so that it did what I wanted, rather than the default.

    It works >
  • ouzoouzo
    edited 6:43AM
    Erik van der Gaag wrote: »
    Hi,

    I have worked out a way to do this, but its a little complicated.

    Basically I have overridden the Query and Editor components, and over rode the Edit function in the SqlProperty editor, and changed the code so that it did what I wanted, rather than the default.

    It works >

    Hi Erik,

    it wood be nice if you can post some source about it here.


  • Erik van der GaagErik van der Gaag New Zealand
    edited 6:43AM
    I'll have to see what I did to do this again...

    I'll get back to you.
  • swierzbickiswierzbicki
    edited 6:43AM
    Erik van der Gaag wrote: »
    I'll have to see what I did to do this again...

    I'll get back to you.

    Thank you in advance >
  • Erik van der GaagErik van der Gaag New Zealand
    edited 6:43AM
    In our case, we are using Firebird and we got the IBO components for fast report (cant remember where)

    I then created inherited components from the editor, components and rtti files so I could then override the sql property editor and query editor edit functions and pass them off to a form which has the active query builder components in it via an 'execute' function in that form.

    I have attached the units I created to inherit from the frx ibo components.

    You should be able to work it out from them.

    Note, I added extra properties to the component which we needed, which are, edit sql and user level. This is so we can restrict access to certain tables and field in the query builder according to the user logged in to our application.

    I hope this allows you to do what you need to do.

    unit LS_frxIB_Components;

interface

uses
  SysUtils, Classes, frxClass, frxIB_Components, LS_frxIB_RTTI, LS_frxIB_Editor, frxDsgnIntf;

type
  TLSfrxIB_Components = class(TfrxIB_Components)
  private
    FCanEditSQL: Boolean;
    FOldComponents: TLSfrxIB_Components;
    FUserLevel: Integer;
  public
    constructor Create(AOwner: TComponent); override;
    destructor Destroy; override;
    property CanEditSQL: Boolean read FCanEditSQL write FCanEditSQL;
  published
    property UserLevel: Integer read FUserLevel write FUserLevel;
  end;

  TLSfrxIB_Query = class(TfrxIB_Query)
  public
    class function GetDescription: String; override;
  end;

var
  GlobalIB_Components: TLSfrxIB_Components;

procedure Register;

implementation

procedure Register;
begin
  RegisterComponents('FastReport 4.0', [TLSfrxIB_Components]);
end;

class function TLSfrxIB_Query.GetDescription: String;
begin
  Result:= 'LS_IBO_Query';
end;

constructor TLSfrxIB_Components.Create(AOwner: TComponent);
begin
  inherited;
  FOldComponents:= GlobalIB_Components;
  GlobalIB_Components := Self;
end;

destructor TLSfrxIB_Components.Destroy;
begin
  if GlobalIB_Components = Self then
    GlobalIB_Components := FOldComponents;
  inherited;
end;

initialization
  frxObjects.RegisterObject1(TLSfrxIB_Query, nil, '', 'IB_', 0, 39);
finalization
  frxObjects.UnRegister(TLSfrxIB_Query);
end.

    unit LS_frxIB_Editor;

interface

implementation

uses
  SysUtils,
    Classes,
  Controls,
  IB_Components,
  IBF_Query,
  frxClass,
    frxDsgnIntf,
  frxIB_Components,
  LS_frxIB_Components,
  frxEditIB_QueryParams,
  frxIBOSet,
  frxRes,
  frxEditStrings, frxIB_Editor,
  frm_EndUserReportQueryBuilder;

function GetFirstLine(const AStrings: TStrings): string;
begin
  Result := '';
  if Assigned(AStrings) then begin
    if AStrings.Count = 1 then Result := AStrings[0]
    else if AStrings.Count > 1 then Result := AStrings[0] + ' ...';
  end;
end;

type
  TLSfrxIB_SqlProperty = class(TfrxClassProperty)
  public
    function GetAttributes: TfrxPropertyAttributes; override;
    function Edit: Boolean; override;
    function GetValue: string; override;
  end;

type
  TLSfrxIB_QueryEditor = class(TfrxComponentEditor)
  public
    function Edit: Boolean; override;
    function HasEditor: Boolean; override;
  end;

{ TLSfrxIB_SqlProperty }

function TLSfrxIB_SqlProperty.Edit: Boolean;
var
  l: TStrings;
  SQLString : String;
begin
  with TfrmEndUserReportQueryBuilder.Create(Designer) do
  begin
    l := TStrings(GetOrdValue);
    SQLString := l.Text;
    Result := Execute(SQLString, GlobalIB_Components.UserLevel, GlobalIB_Components.CanEditSQL);
    if Result then
    begin
      l.Text := SQLString;
      TfrxIB_Query(frComponent).UpdateDataTree;
    end;
    Free;
  end;
end;

function TLSfrxIB_SqlProperty.GetAttributes: TfrxPropertyAttributes;
begin
  Result := [paDialog, paReadOnly];
end;

function TLSfrxIB_SqlProperty.GetValue: string;
begin
  Result := GetFirstLine(TStrings(GetOrdValue));
end;

(** Show SQL Query Builder on Double Click*)
function TLSfrxIB_QueryEditor.Edit: Boolean;
var
  SQLString : String;
begin
  with TfrmEndUserReportQueryBuilder.Create(Designer) do
  begin
    SQLString := TfrxIB_Query(Self.Component).SQL.Text;
    Result := Execute(SQLString, GlobalIB_Components.UserLevel, GlobalIB_Components.CanEditSQL);
    if Result then
    begin
      TfrxIB_Query(Self.Component).SQL.Text := SQLString;
      TfrxIB_Query(Self.Component).UpdateDataTree;
    end;
    Free;
  end;
end;

function TLSfrxIB_QueryEditor.HasEditor: Boolean;
begin
  Result := True;
end;



initialization
  frxComponentEditors.Register(TLSfrxIB_Query, TLSfrxIB_QueryEditor);

  frxPropertyEditors.Register(TypeInfo(TStrings), TLSfrxIB_Query, 'SQL', TLSfrxIB_SqlProperty);

  (** @see frxCustomDBEditor *)
  frxHideProperties(TfrxIB_Query, 'DataSet;DataSource;Enabled;OpenDataSource;Tag');

end.

    unit LS_frxIB_RTTI;

interface

{$I frx.inc}

implementation

uses
  Windows, Classes, fs_iinterpreter, LS_frxIB_Components, frxIBOSetRTTI
{$IFDEF Delphi6}
, Variants
{$ENDIF};


type
  TFunctions = class(TfsRTTIModule)
  private
    function CallMethod(Instance: TObject; ClassType: TClass; const MethodName: String; Caller: TfsMethodHelper): Variant;
  public
    constructor Create(AScript: TfsScript); override;
  end;

{ TFunctions }

constructor TFunctions.Create(AScript: TfsScript);
begin
  inherited Create(AScript);
  with AScript do
  begin
    with AddClass(TLSfrxIB_Query, 'TfrxIBODataset') do
    begin
      AddMethod('procedure ExecSQL', CallMethod);
    end;
  end;
end;

function TFunctions.CallMethod(Instance: TObject; ClassType: TClass; const MethodName: String; Caller: TfsMethodHelper): Variant;
begin
  Result := 0;
  if ClassType = TLSfrxIB_Query then
  begin
    if MethodName = 'EXECSQL' then
    begin
      TLSfrxIB_Query(Instance).DataSet.ExecSQL;
    end;
  end
end;


initialization
  fsRTTIModules.Add(TFunctions);

finalization
  if fsRTTIModules <> nil then
    fsRTTIModules.Remove(TFunctions);

end.


  • ouzoouzo
    edited 6:43AM
    Eric, thanks a lot, I will study your code.

Leave a Comment

Rich Text Editor. To edit a paragraph's style, hit tab to get to the paragraph menu. From there you will be able to pick one style. Nothing defaults to paragraph. An inline formatting menu will show up when you select text. Hit tab to get into that menu. Some elements, such as rich link embeds, images, loading indicators, and error messages may get inserted into the editor. You may navigate to these using the arrow keys inside of the editor and delete them with the delete or backspace key.

Home • FastReport 4.0Comment As ...

© Vanilla Keystone Theme 2024