Example

In this example, the Params settings are sent from the Crystal component to the Crystal Reports Print Engine DLL. This is normally handled automatically in the Execute method:

Crpe1.SQL.Params.Send;

SQL Query property

Declaration

property Query: TCrpeString;

Type

TCrpeString = class(TStringList)

Description

The Query property is used to retrieve and send the SQL Query statement used by a specified Report. This will only be available if the Report is based off SQL databases, or ODBC Data Sources. Query does not apply to PC-type databases (dBase, FoxPro, Access, Paradox, etc.) using native drivers.

The SELECT part of the Query cannot be changed, since it is determined by the fields that are placed in the Report. Although the property requires that the entire SQL Query statement be entered, the SELECT section must not be different from the original Query. If an ORDER BY clause will be included, it must be preceded with CHR(13) and CHR(10) (carriage return/line feed).

NOTE: Crystal Reports will attempt to translate any Selection formula and SortFields sort order into the WHERE and ORDER BY parts of the SQL Query when the Report is run. For this reason, it may be easier to pass values into these properties instead of modifying the SQL Query directly. Conversely, modifying the SQL Query will cause the Selection formula in a Report to be removed when the Report is run.

Example

The following example uses the Query property to pass a new SQL statement to the Report:

Crpe1.ReportName := 'C:\Company.rpt'; {Set the LogOn} Crpe1.Connect.Retrieve; Crpe1.Password := 'Aesop';

{Set the SQL Query}

Crpe1.SQL.Query[0] := 'SELECT DEPT."DEPTNO", DEPT."DNAME", DEPT."LOC"'; Crpe1.SQL.Query[1] := 'FROM "SCOTT"."DEPT" DEPT';

Crpe1.SQL.Query[2] := 'WHERE DEPT."DEPTNO" = 10';

Crpe1.SQL.Query[3] := 'ORDER BY DEPT."DNAME" ASC'; Crpe1.Output := toWindow;

Crpe1.Execute;

VCL Reference

646

SQL Methods

SQL Clear method

Declaration

procedure Clear;

Description

This method can be used to clear the Query property of the SQL object. It is the same as:

Crpe1.SQL.Query.Clear;

It is called automatically if the Clear method is called for the main component, or if a new Report name is assigned to the ReportName property, but may be called in code as desired.

The Clear method is only applied to the SQL Query of the current Report/Subreport specified in the Subreports object. Therefore, to clear all the SQL Query of all the Subreports will require a looping procedure that goes through each Subreport and applies the Clear method.

Example

This code clears the SQL Query for the main Report (assuming the default state of Subreports[0]):

Crpe1.SQL.Clear;

This code clears the SQL Query for all the Subreports:

for cnt := 1 to (Crpe1.Subreports.Count - 1) do begin

Crpe1.Subreports[cnt];

Crpe1.SQL.Clear; end;

SQL CopyFrom method

Declaration

function CopyFrom(Source: TCrpeSQL): boolean;

VCL Reference

647

Description

The CopyFrom method copies the SQL property values from another TCrpeSQL object. It is similar to Delphi's Assign method. It is useful for transferring or temporary storage of values.

NOTE: At this time, the CopyFrom for SQL only copies the Query. If you want to copy Params, or Expressions, you will have to copy them separately.

Example

The following example copies all the SQL property values to a temporary TCrpeSQL object:

var

tempSQL : TCrpeSQL; begin

tempSQL := TCrpeSQL.Create; tempSQL.CopyFrom(Crpe1.SQL);

{...later, when finished with the temp object...} tempSQL.Free;

end;

SQL Create method

Declaration

constructor Create;

Description

The Create method is used internally in the Crystal component to create the SQL object, and does not need to be called in code.

SQL Destroy method

Declaration

destructor Destroy; override;

Description

The Destroy method is used internally in the Crystal component and does not need to be called in code.

VCL Reference

648

SQL Retrieve method

Declaration

function Retrieve: boolean;

Description

The Retrieve method obtains the SQL Query information from the Report and stores it in the SQL.Query property of the component. If an SQL Query was not found, or a connection could not be made to the SQL Server (which is required to retrieve the Query), the call returns False. Be aware that calling Retrieve will first cause the SQL .Query property in the VCL to be cleared, so any current information stored in it will be removed.

NOTE: Since the SQL object also applies to Subreports, the Retrieve method will obtain the SQLQuery for the Report (or Subreport) that the Subreports object index is currently pointing to. If you want to obtain the SQLQuery information for the main Report and all of the Subreports, you will have to step through the Subreports object and call Retrieve for each item. The VCL will store separate SQL.Query information for each Subreport (see Example).

Example

The following code illustrates the use of the Retrieve method for the main Report:

Crpe1.ReportName := 'C:\Company.rpt';

Crpe1.SQL.Retrieve;

The following code illustrates the use of the Retrieve method for the main Report and any Subreports:

var

cnt: integer; begin

Crpe1.ReportName := 'C:\Company.rpt'; Crpe1.Subreports.Retrieve;

{Loop through Reports and Retrieve}

for cnt := 0 to (Crpe1.Subreports.Count - 1) do begin

Crpe1.Subreports[cnt];

Crpe1.SQL.Retrieve; end;

{Set Crpe component back to main Report} Crpe1.Subreports[0];

end;

VCL Reference

649