Direct SQL (Delphi)
Note
This demo is available in your FlexCel installation at <FlexCel Install Folder>\Demo\Delphi\Modules\20.Reports\92.Direct SQL and also at https://github.com/tmssoftware/TMS-FlexCel.VCL-demos/tree/master/Delphi/Modules/20.Reports/92.Direct SQL
Overview
In normal FlexCel reports, you create the data layer in code and the template in Excel. This allows your user to easily modify the presentation layer with Excel and without needing to modify the code.
While this is a very good choice for most reports (as the data layer does not change much, and the presentation does), sometimes you might want to allow your users to directly modify the data layer from Excel. This is when you can use Direct SQL in templates.
Concepts
By default, you can't use Direct SQL on reports. To be able to use it, you need to add a connection to the report with TFlexCelReport.AddConnection in the code.
Allowing the final user to specify the SQL directly on the report might carry big security risks.
A user might use the SQL to modify the data. For example, instead of a normal SQL like "select * from customer" he might write "delete from customers" and erase all information. While FlexCel does a little validation on the SQL string, (for example it will not allow SQLs not starting with "SELECT") there are always ways to modify the data.
A user might get access to data you don't intend him to. For example, he might know the user passwords are on the table "Users" and use an existing unrelated report to get the data from this table.
To keep your application secure, it is recommended that:
You give Read Only access rights to the connection you add to the report, and only give access to the tables the report needs to use
You enable Direct SQL on cases where you can control the templates. For example on a web application (where the templates are always on the server) or a GUI App where templates are kept on a server. Or, of course, if you don't care about the data because it is not important, or the customer has administrator access to all the database anyway.
Do not use Encrypted templates to give security to the system. While this can give some extra security, encrypted xls files can be cracked with tools available in Internet.
To use Direct SQL on the templates, you write SQL(connection, sqlstring) on the "Source name" column in the config sheet.
You can't use expressions inside an SQL statement. The "sqlstring" you pass as parameter to the SQL() command will not allow any expression replacement. Again, this is a security decision to avoid SQL Injection attacks. (If you don't know what this is, you can search for "SQL injection" in Internet to get a more in-depth description of the problem)
As you can't use expressions inside the SQL, you need to be able to pass parameters to it. To pass a parameter to the SQL:
In SQL databases you can normally use two types of parameters: positional ("?") and named ("@param" or ":param"). On order to keep your templates database-independent, FlexCel Templates will always use named parameters starting with "@". Even if you are accessing ODBC, you should write "SELECT * from table where field = @field" and never "SELECT * from table where field = ?" in the template. The only supported syntax in templates is "@" parameters; FlexCel will convert the parameters to what's needed when calling the actual SQL.
You then can set the parameters in the code, by using TFlexCelReport.AddSqlParameter This is not database independent, and you need to add the correct type of parameter for the actual database here.
Files
UMainForm.pas
unit UMainForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics,
FlexCel.VCLSupport, FlexCel.Core, FlexCel.XlsAdapter, FlexCel.Report, FlexCel.Render,
{$if CompilerVersion >= 23.0} System.UITypes, {$IFEND}
ShellApi,
Controls, Forms, Dialogs, StdCtrls, ExtCtrls, ComCtrls, DB,
ADODB;
type
TMainForm = class(TForm)
btnCancel: TButton;
btnGo: TButton;
SaveDialog: TSaveDialog;
Label1: TLabel;
dtStartDate: TDateTimePicker;
dtEndDate: TDateTimePicker;
Label2: TLabel;
ADOConnection: TADOConnection;
procedure btnCancelClick(Sender: TObject);
procedure btnGoClick(Sender: TObject);
procedure FormCreate(Sender: TObject);
private
procedure RunReport;
function GetDataPath: string;
procedure SetupConnection(const Report: TFlexCelReport);
{ Private declarations }
public
{ Public declarations }
end;
var
MainForm: TMainForm;
implementation
uses IOUtils;
{$R *.dfm}
procedure TMainForm.btnCancelClick(Sender: TObject);
begin
Close;
end;
procedure TMainForm.btnGoClick(Sender: TObject);
begin
RunReport;
end;
function DBFile: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..\..\SharedData\Northwind.mdb');
end;
procedure TMainForm.FormCreate(Sender: TObject);
begin
ADOConnection.ConnectionString := StringReplace(ADOConnection.ConnectionString, 'Northwind.mdb', DbFile, []);
end;
function TMainForm.GetDataPath: string;
begin
Result := TPath.Combine(TPath.GetDirectoryName(ParamStr(0)), '..\..');
end;
procedure TMainForm.SetupConnection(const Report: TFlexCelReport);
begin
Report.AddConnection('Northwind',
function (const sql: string): TDataSet //We need to return a new TDataSet here, FlexCel will free it.
var
ds: TADODataSet;
begin
ds := TADODataSet.Create(nil);
ds.Connection := ADOConnection;
ds.CommandText := sql;
Result := ds;
end);
Report.AddSqlParameter('StartDate',
procedure (const ParamName: string; const DataSet: TDataSet)
begin
(DataSet as TADODataSet).Parameters.ParamValues[ParamName] := dtStartDate.DateTime;
(DataSet as TADODataSet).Parameters.ParamByName(ParamName).DataType := ftDateTime;
end);
Report.AddSqlParameter('EndDate',
procedure (const ParamName: string; const DataSet: TDataSet)
begin
(DataSet as TADODataSet).Parameters.ParamValues[ParamName] := dtEndDate.DateTime;
(DataSet as TADODataSet).Parameters.ParamByName(ParamName).DataType := ftDateTime;
end);
end;
procedure TMainForm.RunReport;
var
Report: TFlexCelReport;
begin
if not SaveDialog.Execute then exit;
Report := TFlexCelReport.Create(true);
try
Report.SetValue('ReportCaption', 'Sales by Country and Employee');
SetupConnection(Report);
Report.Run(
TPath.Combine(GetDataPath, 'Direct SQL.template.xls'),
SaveDialog.FileName);
finally
Report.Free;
end;
if MessageDlg('Do you want to open the generated file?', mtConfirmation, [mbYes, mbNo], 0) = mrYes then
begin
ShellExecute(0, 'open', PCHAR(SaveDialog.FileName), nil, nil, SW_SHOWNORMAL);
end;
end;
end.