Query Designer & Query Wizard

rbuser

Is there a way to use the Query Designer and Query Wizard in my application
without actually using the report design? I use RB for all reports. I have
a seperate area where user creates custom queries. I would like for them to
have same designer and wizard as they use for reporting. Is there a way to
run either and extract the SQL without actually opening full report
interface?

Thanks,
Bob

nicocizik

Hi Bob,

Take a look at the following article.

http://www.digital-metaphors.com/rbWiki/DADE/Program_Control/How_To...Launch_the_DADE_Query_Designer_in_Code

--
Regards,

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

rbuser

Do you have an example that shows how to do this with ADO and the Data
dictionary that is already being used for the report builder on the form?

Thanks,
Bob

rbuser

Been working on this and have it very close. Problem is that I can't run it
unless I have a report open first. If report is open then the query
designer will run independently just fine. But if I run query designer
without report open, I connect login prompt althought the ADO component is
connected and I am connected to a database. Below is my code. Any
suggestions?

Again, at this point when I click on button to run query designer, I am
already connected to the server / database via the ADO1 component. I can't
figure out why it works once a report has been opened but not before.

private
{ Private declarations }
FSQL: TdaSQL;
FSession: TdaADOSession;

procedure TfrmMain.FormCreate(Sender: TObject);
begin
{code to allow RB Query Builder}
FSession := TdaADOSession.Create(Self);
FSQL := TdaSQL.Create(Self);
FSQL.Session := FSession;
FSQL.AllowSelfJoin := True;
FSQL.DatabaseName :='ADO1';
FSQL.DatabaseType :=dtMSSQLServer;
FSQL.SQLType :=sqSQL2;
end;

procedure TfrmMain.btnQueryBuilderClick(Sender: TObject);
var
lDialog: TdaQueryDesignerForm;
begin
lDialog := TdaQueryDesignerForm.Create(Application);
lDialog.SQLClass := TdaSQL;
lDialog.SQL.Assign(FSQL);
lDialog.Initialize;
if lDialog.ShowModal=mrOk then
begin
FSQL.Assign(lDialog.SQL);
memoQuery.Lines.Text :=LDialog.SQL.SQLText.Text;
end;
lDialog.Free;
end;

rbuser

D7, RB 10.07

Have tried everything I can think of. Still get the same results. If I run
a report, then the code below works fine. But without first running a
report, I get a connection prompt for the DefaultADOConnection. Even with a
login it doesn't connect.

I am desperate to get this resolved. I downloaded the BDE example and it
works fine with BDE.

Your help will be greatly appreciated.

Thanks,
Bob

nicocizik

Hi Bob,

Are the ADO connection other DB components active when you click the button?
Perhaps the reason it is running after you run a report is that the report
is activating these components.

--
Regards,

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

rbuser

I have a ppDictionary, ppDesigner and ppReport. Also have ADOQuery
components and dbpipelines for Tables, Fields, Joins and Reports. I have a
function titled RefreshReports that opens all of these before running a
report. I have tried running that same function before running the designer
dialog. It makes no difference.

Honestly, I don't see where the code below has anything to do with the
actual ppReport, Dictionary and Designer components. I don't see where
running a report has anything to do with FSQL, lDialog or FSession.

Does the code below appear correct to you?
I have tried with TdaADOConnection and TdaConnection with same results.
I can prepare a sample app to test if you would like.

rbuser

Below is a simple standalone program I just put together with the same
results. There is no report component in the program. I am calling just
the designer. I am first connecting ADO and the designer still prompts for
the connection info. I suspect this is a pain for you but I really
appreciate the help. It is important to me that my users have the same
interface for building queries in reports and otherwise.

Thanks,
Bob

unit Unit1;

interface

uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
Dialogs, StdCtrls, Buttons, DB, ADODB, daSQL, daDB, DaADO,
daQueryDesigner, ppTypes;

type
TForm1 = class(TForm)
ADO1: TADOConnection;
btnRunDesigner: TButton;
procedure FormCreate(Sender: TObject);
procedure btnRunDesignerClick(Sender: TObject);
private
{ Private declarations }
FSQL: TdaSQL;
FSession: TdaADOSession;
public
{ Public declarations }
end;

var
Form1: TForm1;

implementation

{$R *.dfm}
{$R rbDade.Res}

procedure TForm1.FormCreate(Sender: TObject);
begin
{code to allow RB Query Builder}
FSession := TdaADOSession.Create(Self);
FSQL := TdaSQL.Create(Self);
FSQL.Session := FSession;
FSQL.AllowSelfJoin := True;
FSQL.DatabaseName :='ADO1';
FSQL.DatabaseType :=dtMSSQLServer;
FSQL.SQLType :=sqSQL2;
end;

procedure TForm1.btnRunDesignerClick(Sender: TObject);
var
lDialog: TdaQueryDesignerForm;
vADO: TADOConnection;
begin
try
ADO1.Connected :=True;
except
end;
if ADO1.Connected=False then
begin
showmessage('ADO1 not connected.');
exit;
end;
lDialog := TdaQueryDesignerForm.Create(Application);
lDialog.SQLClass := TdaSQL;
lDialog.SQL.Assign(FSQL);
lDialog.Initialize;
if lDialog.ShowModal=mrOk then
begin
FSQL.Assign(lDialog.SQL);
showmessage(lDialog.SQL.SQLText.Text);
end;
lDialog.Free;
end;

nardmoseley

I added an ADO example to the rbWiki page.

http://www.digital-metaphors.com/rbWiki/DADE/Program_Control/How_To...Launch_the_DADE_Query_Designer_in_Code


--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com

rbuser

That worked perfect. The thing that made the difference was
FSession.DataOwner :=Self;

One more question about this. Is it possible to do the reverse. In other
words, take and sql statement and generate the query designer with the sql
populated with the sql statement?

I am guessing I will have to manually program to fill in the different
aspects but not sure.

Please let me know. Regardless of the answer, thank you so much for the
help. This is one of the reasons I will never leave Report Builder and will
continue to update.

Thanks,
Bob

nardmoseley

The DataOwner is used internally to find the TADOConnection component. (i.e.
the DataOwner is the Form/DataModule upon which the TADOConnection component
resides).

There is no built-in functionality that can parse SQL text and construct the
TdaSQL object automatically.

The TdaSQLBuilder class provides a run-time interface for building a TdaSQL
object via code. See the TdaSQLBuilder topic in the RBuilder help and the
TdaSQLBuilder section of rbWiki for examples.

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


Thanks for the kind words and for supporting RB!

--
Nard Moseley
Digital Metaphors
www.digital-metaphors.com