TdaSql

rbuser

Hi,

Having a look at daSql.pas, I think that this class is used to compose
the queries applied against database server. Is it right?

If so, may I use it in my source code to build my own queries using the
TdaSql join engine? and how can I do it?

Thanks in advance,
Laercio

nicocizik

Hi Laercio,

I am unclear about what you would like to use the daSQL object for. If you
are try to create custom SQL statements inside a report, you can extract the
SQLText object and alter it as you need. See the following example on how
to extract the sql text and use it in your reports.

http://www.digital-metaphors.com/tips/ExtractSQLObject.zip

--

rbuser

Nico,

Your example was very important to give me a way to do what I need for.

But I want to instantiate a TdaSql in a stand-alone way, just to get the
Sql code generated by it.

I tried the following code, but it take me an AV:

procedure TForm1.Button1Click(Sender: TObject);
var
FSql: TdaSql;
begin
FSql := TdaSQL.Create(Self);

FSql.AllowSelfJoin := True;
FSql.DatabaseType := dtOracle;
FSql.SQLType := sqSQL1;
FSql.DataDictionary := ppDataDictionary1;

FSql.AddTable( 'PESPRI' ); <== AV in this line...
FSql.AddTable( 'PESCLI' );
.
end;


I want to use TdaSql to compose a Select statement that take me the join
clause in an automatic way, because I will use this Select against
others database servers. This statement is not linked with a report, I
just want to use it in my app routine.

Is there a way to do this?

Thanks,
Laercio

nicocizik

Hi Laercio,

The TdaSQL object was not really meant to be used separate from
ReportBuilder, mainly because it is not properly documented. Below is a
link to an example that creates a stand alone Query Designer that outputs
the SQL generated from the daSQL object once it is created. You could use
this example and trace into the daSQL code to find out which parts of that
object are useful for your needs. Hope this helps.

http://www.digital-metaphors.com/tips/QueryDesignerViaCode.zip

--

rbuser

Nico,

Thank you very much for your help!
Your sample was very useful.
I think I found the answer that I was looking for.

Please, have a look in my code and tell me if you see any problem in it:

procedure TfForm1.Button1Click(Sender: TObject);
var
FSql: TdaSql;
FSession: TdaSession;
begin
FSession := TdaSQLSession.Create(Self);
FSession.DataOwner := Self;

FSql := TdaSQL.Create(Self);
FSQL.Session := FSession;

try
FSQL.AllowSelfJoin := True;
FSql.DatabaseName := DatMod.fDatMod.dbMod.Name;

FSql.DataDictionary := DatMod.fDatMod.ppDataDictionary1;
FSql.DatabaseType := dtOracle;
FSql.SQLType := sqSQL1;

// EXAMPLE 1:
// Automatic Join, by table field names
FSql.AddTable('TPESPRI');
FSql.AddTable('TPESCLI');
FSql.MatchJoinFieldsByName( FSql.GetTableForSQLAlias( 'TPESPRI' ),
FSql.GetTableForSQLAlias( 'TPESCLI' ) );

FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODEMP');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'DESPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESCLI' ),
'CODCLI');
//


// EXAMPLE 2
// Manual Join
FSql.CreateJoinObjects('TPESPRI','TPESCLI', 'dajtRightOuter',
'CODEMP,CODPES', '=,=','CODEMP,CODPES' );

FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODEMP');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'DESPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESCLI' ),
'CODCLI');
//

// EXAMPLE 3
// Automatic Join, by Dictionary ppJoins info

FSql.AddTable('TPESPRI');

// 31: Table TPESPRI number in AvailableTables
// 0: Index 0 (first table) added using AddTable
FSql.SelectTable( 31, 0);

FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODEMP');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'CODPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESPRI' ),
'DESPES');
FSql.AddSelectField( FSql.GetTableForSQLAlias( 'TPESCLI' ),
'CODCLI');
//

// AddWhere
FSql.AddCriteriaField( FSql.GetTableForSQLAlias( 'TPESPRI'),
'CODPES', dacoBetween,'1,1000' );

ShowMessage( FSql.MagicSQLText.Text );

finally
FSession.Free;
FSql.Free;
end;
end;
-----------
All of 3 examples give me the expected Select statement. I am using only
one of them each time for test.
So I will choose the method that I will use.

Is there something wrong or strange in the code?

Thanks in advance,
Laércio

nicocizik

Hi Laercio,

At first glance, your code looks good to me, especially if it is giving you
the correct output . Good luck with the rest of your application.

--