Home DADE

Creating Custom DataView

edited April 2010 in DADE
Hello RB-Team,

I'm using RB 11.08 Enterprise and Delphi2009. Database is Oracle 11.
I developed a Custom DataView with your instruction behind this link:
http://www.digital-metaphors.com:8080/DADE/Custom_Dataviews

Instead of this code
lTable := lSQL.AddTable('clients');
lSQL.AddSelectField(lTable, 'Last_Name');
lSQL.AddSelectField(lTable, 'First_Name');
I' ve tried this one
{modify the local version}
lSQL.SQLText.Text := 'Select 'A' As FIELD1, 'B' As FIELD2 From DUAL';
lSQL.EditSQLAsText := True;

The report designer opens and I switch to the DADE-Tab. The DataView is
visible and
it's possible to preview the date.

Then I create a new DataView on with the designer. Menu => File => New... =>
Query Editor. After choosing a table and all fields there is a second
DataView.
Next I try to create a DataLink between these two DataViews via drag&drop
and it occours an AV in TdaQueryDataView.CreateLinkObject, because
lMasterFiled is NIL.

There's another way to do it. Open the Edit SQL window of the first DataView
and close it without doing anything. If I try to create the DataLink then it
works.

It seems that the table and field aliases were not set. The DUAL table of
Oracle contains only the 'DUMMY' field, but I use it as described above.

Could you help me, please?

Best Regards,
Hartmut

Comments

  • edited April 2010
    Hi Hartmut,

    Rather than editing the SQL text manually (which limits the linking
    ability), I recommend creating the query using the TdaSQLBuilder class. See
    the following articles and help topic on the subject on how this class is to
    be used. Using the SQLBuilder rather than creating your own manual SQL code
    will run more efficiently as well.

    http://www.digital-metaphors.com/rbWiki/DADE/SQLBuilder

    Also a helpful example...

    http://www.digital-metpahors.com/tips/CreateDataModCustOrderLinkedDataViews.zip

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited April 2010
    Hi Nico,

    thank you for your reply.
    I think the main problem is that my SQL is not a static one.
    Our end users are able to write their own SQL, for example in a TMemo.
    We take the SQL and copy it into a Query component.
    Here is my whole procedure:

    procedure TfTest.Button1Click(Sender: TObject);
    var i : Integer;
    FReport : TppReport;
    FDesigner : TppDesigner;

    FSQL : TdaSQL;
    FDataModule : TdaDataModule;
    FDataView : TdaSDQueryDataView;
    FPipeLine : TppDBPipeline;
    FSQLBuilder : TdaSQLBuilder;
    qrDrucken : TSDQuery;
    sl : TStringList;
    FTable : TdaTable;
    begin
    FReport := TppReport.Create(Self);
    FReport.CreateDefaultBands;
    FDesigner := TppDesigner.Create(Self);
    // TSDQuery is a substitution for BDE TQuery
    qrDrucken := TSDQuery.Create(Self);
    sl := TStringList.Create;
    try
    qrDrucken.DatabaseName := 'Your DatabaseName';
    qrDrucken.SQL.Clear;
    qrDrucken.SQL.Add('Select ''A'' As F1, ''B'' As F2 From DUAL');
    qrDrucken.Open;
    // Database as connection from DADE-Plugin
    FSDDatabase := TSDDatabase.Create(nil);
    FSDDatabase.Assign(qrDrucken.Database);

    FDesigner.DataSettings.SessionType := 'SDSession';
    FDesigner.DataSettings.DatabaseName := FSDDatabase.RemoteDatabase;
    FDesigner.DataSettings.DatabaseType :=
    TdaSDSession.GetCnxDatabaseType(qrDrucken.Database);
    FDesigner.DataSettings.AllowEditSQL := True;
    FDesigner.DataSettings.SQLType := sqSQL1;

    if FileExists('c:\Temp\Test.rtm') then
    begin
    FReport.Template.FileName := 'c:\Temp\Test.rtm';
    FReport.Template.LoadFromFile;
    end;
    FDesigner.Report := FReport;

    {+++ Create DataView +++}
    {create a datamodule - note: this is only necessary if you need to
    stream the report definition to an .rtm or database}
    FDataModule := TdaDataModule.CreateForReport(FReport);

    {create a query dataview}
    FDataView := TdaSDQueryDataView.Create(FDataModule);
    FDataView.Parent := FDataModule;
    FDataView.Name := 'Nexum';
    {initialize the dataview}
    FDataView.Init;

    FPipeline := TppDBPipeline(FDataView.DataPipelines[0]);
    FPipeline.Name := 'plNexum';
    FPipeline.UserName := 'plNexum';

    FSQL := FDataView.SQL;
    FSQL.DatabaseName := FSDDatabase.RemoteDatabase;;
    FSQL.DatabaseType :=
    TdaSDSession.GetCnxDatabaseType(qrDrucken.Database);;
    FSQL.SQLType := sqSQL1;
    FSQL.Session := FDataView.Session;
    FSQL.DataPipelineName := 'plNexum';

    FSQLBuilder := TdaSQLBuilder.Create(FSQL);
    FSQLBuilder.SQL.SQLText.Text := qrDrucken.SQL.Text;
    FSQLBuilder.SQL.ValidateSQLText(FSQLBuilder.SQL.SQLText);
    FSQLBuilder.SQL.EditSQLAsText := True;
    FSQLBuilder.ApplyUpdates;

    {get a reference to the dataivew's pipeline and assign a Name to the
    pipeline}
    // FDataView.OutOfSync;
    // FDataView.Sync;

    // Without this line, there are no fields visible
    FPipeline.AutoCreateFields := True;
    {connect report to datapipeline}
    FReport.DataPipeline := FPipeline;

    FDesigner.ShowModal;

    FSQL.Free;
    FSQLBuilder.Free;
    finally
    qrDrucken.Close;
    qrDrucken.Free;
    FDesigner.Free;
    FReport.Free;
    sl.Free;
    end;
    end;

    Thanks in advance for your help,
    Hartmut

  • edited April 2010
    Hi Hartmut,

    There is now a patch available for RB 11.08 that solves this issue. Please
    contact support@digital-metaphors.com to receive the patch. Also, once you
    apply the patch there is no need to assign the Pipeline.AutoCreateFields
    property to see the selected fields in the dataview.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited April 2010
    Hi Nico,


    it works with your patch.
    Thank you very much!

    Best regards,
    Hartmut

  • edited May 2010
    Hi Nico,

    here we go again. I modified this procedure a liitle bit and I've got nearly
    the same problems.
    New in this procedure is that there's no SQLText or Query available, but a
    ClientDataSet which is the master.

    Again:
    // Without this line, there are no fields visible
    FPipeline.AutoCreateFields := True;

    The Problem while creating links in the Data-Tab has occured.

    Here's the source:
    procedure TfReport.BitBtn1Click(Sender: TObject);
    var oReport : TppReport;
    oDesigner : TppDesigner;
    oSQL : TdaSQL;
    oDataModule : TdaDataModule;
    oDataView : TdaSDQueryDataView;
    oPipeLine : TppDBPipeline;
    oSQLBuilder : TdaSQLBuilder;
    sSQL : String;
    i : Integer;
    cds : TClientDataSet;
    begin
    oReport := TppReport.Create(Self);
    oReport.CreateDefaultBands;
    oDesigner := TppDesigner.Create(Self);

    try
    oDesigner.DataSettings.SessionType := 'SDSession';
    oDesigner.DataSettings.DatabaseName := AktDatabaseName;
    oDesigner.DataSettings.DatabaseType :=
    TdaSDSession.GetCnxDatabaseType(AktServerType);
    oDesigner.DataSettings.AllowEditSQL := True;
    oDesigner.DataSettings.SQLType := sqSQL1;
    if FileExists('c:\Temp\Test1.rtm') then
    begin
    oReport.Template.FileName := 'c:\Temp\Test1.rtm';
    oReport.Template.LoadFromFile;
    end;
    {+++ Create DataView +++}
    {create a datamodule - note: this is only necessary if you need to
    stream the report definition to an .rtm or database}
    oDataModule := TdaDataModule.CreateForReport(oReport);
    {create a query dataview}
    oDataView := TdaSDQueryDataView.Create(oDataModule);
    oDataView.Parent := oDataModule;
    oDataView.Name := 'Nexum';

    {initialize the dataview}
    oDataView.Init;

    { Later...I want to create a Pipeline to every available ClientDataSet}
    for i := 0 to ComponentCount - 1 do
    begin
    if Components[i] is TClientDataSet then
    begin
    cds := TClientDataSet(Components[i]);
    end;
    end;

    oPipeline := TppDBPipeline(oDataView.DataPipelines[0]);
    oPipeline.Name := 'plNexum';
    oPipeline.UserName := 'plNexum';
    { ClientDataSet... }
    oPipeLine.DataSource.DataSet := cds;
    { Without this line there are no fields in DataView visible}
    oPipeLine.AutoCreateFields := True;

    gMetaDataManager.Clear;
    oSQL := oDataView.SQL;
    oSQL.DatabaseName := AktDatabaseName;
    oSQL.DatabaseType := TdaSDSession.GetCnxDatabaseType(AktServerType);
    oSQL.SQLType := sqSQL1;
    oSQL.Session := oDataView.Session;
    oSQL.DataPipelineName := 'plNexum';
    // oSQLBuilder := TdaSQLBuilder.Create(oSQL);
    // oSQLBuilder.SQL.SQLText.Text := sSQL;
    // oSQLBuilder.SQL.ValidateSQLText(oSQLBuilder.SQL.SQLText);
    // oSQLBuilder.SQL.EditSQLAsText := True;
    // oSQLBuilder.ApplyUpdates;
    {connect report to datapipeline}
    oReport.DataPipeline := oPipeline;

    oDesigner.Report := oReport;
    oDesigner.ShowModal;
    oReport.Free;
    finally
    oDesigner.Free;
    end;
    end;

    Thank you in advance and regards,
    Hartmut

  • edited May 2010
    Hi Hartmut,

    Looking at your code below it seems that you are trying to connect the
    pipeline of the DADE dataview to an external dataset (TClientDataset). This
    is simply not the way DADE is designed to work. The QueryDataView by
    definition is a container for the entire data retrieval process (SQL -
    Dataset - Datasource - Datapipeline).

    The idea of connecting a dataview to an existing external dataset is
    something we may consider for a later release. Currently you will need to
    connect your TClientDataset to a TppDBPipeline object on your form (or in
    code) to use that data inside the report.

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
  • edited June 2010
    Hi Nico,

    we haven't found a solution for this problem.
    Maybe, is there another way to do it? The thing is the master I have is only
    a DataSet, but not a DataSource. Also it could be a TSDQuery (similar to
    TQuery) object as master. But this contains a SQL with parameters, which
    can't be used like this:
    SDQuery.SQL.Add('Select RecNo From DUAL Where RecNo = :RECNO');
    SDQUery.ParamByName('RECNO').AsInteger := 1;
    FSQLBuilder.SQL.SQLText.Text := SDQuery.SQL.Text;


    Or is there an event where I can use ReportParameter while navigating
    through the DataSet?

    I want to give master data to DADE and end-user should be able to create
    SQL-DataViews and link them with the master.

    Best regards,
    Hartmut


  • edited June 2010
    Hi Hartmut,


    Ideally you will do all of this in DADE. There is no need to "give" DADE
    any data, you simply create an manipulate the query inside DADE, then your
    users can add-on, link to, or remove anything they would like.

    With DADE there is no need for a SDQuery object on your form, DADE will
    create it on its own using the SQLDirect plugin available from their website
    and you can use it as you would normally from within ReportBuilder.

    Note that it is also possible to perform all the parameter operations you
    mention below in DADE with RB 11 or later.

    http://www.sqldirect-soft.com/download.html

    --
    Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com

    Best Regards,

    Nico Cizik
    Digital Metaphors
    http://www.digital-metaphors.com
This discussion has been closed.