FireDAC.Comp.Client.TFDCustomQuery.ExecSQL
Delphi
procedure ExecSQL; overload;
function ExecSQL(AExecDirect: Boolean): LongInt; overload;
function ExecSQL(const ASQL: String): LongInt; overload;
function ExecSQL(const ASQL: String; const AParams: array of Variant): LongInt; overload;
function ExecSQL(const ASQL: String; const AParams: array of Variant;
const ATypes: array of TFieldType): LongInt; overload;
C++
void __fastcall ExecSQL()/* overload */;
int __fastcall ExecSQL(bool AExecDirect)/* overload */;
int __fastcall ExecSQL(const System::UnicodeString ASQL)/* overload */;
int __fastcall ExecSQL(const System::UnicodeString ASQL, const System::Variant *AParams, const int AParams_High)/* overload */;
int __fastcall ExecSQL(const System::UnicodeString ASQL, const System::Variant *AParams, const int AParams_High, const Data::Db::TFieldType *ATypes, const int ATypes_High)/* overload */;
Properties
| Type | Visibility | Source | Unit | Parent |
|---|---|---|---|---|
procedure function |
public | FireDAC.Comp.Client.pas FireDAC.Comp.Client.hpp |
FireDAC.Comp.Client | TFDCustomQuery |
Description
Executes the SQL statement.
Call ExecSQL to execute the SQL statement currently assigned to the SQL property.
This method is overloaded:
- The first overloaded method executes the SQL statement for the query.
For SELECT statements and other statements which return cursors, call Open instead of ExecSQL. If FireDAC has recognized a command as returning a cursor, then "[FireDAC][Phys]-310. Cannot execute command returning results set." is raised on ExecSQL. This may be incorrect, for example on batch commands, such as:
SELECT f1 INTO @v1 FROM myTab1 WHERE ...; INSERT INTO myTab2 VALUES (@v1 + 1, ...)
// standard parameterized SQL execution
FDQuery1.SQL.Text := 'insert into mytab values (:id, :name)';
FDQuery1.Params[0].AsInteger := 100;
FDQuery1.Params[0].AsString := 'qwe';
FDQuery1.ExecSQL;
// avoid [FireDAC][Phys]-310
FDQuery1.SQL.Clear;
FDQuery1.SQL.Add('SELECT f1 INTO @v1 FROM myTab1 WHERE ...;');
FDQuery1.SQL.Add('INSERT INTO myTab2 VALUES (@v1 + 1, ...)');
FDQuery1.Command.CommandKind := skInsert;
FDQuery1.ExecSQL;
- The second overloaded method executes the SQL statement assigned to the SQL property.
AExecDirectspecifies the query execution mode. See TFDResourceOptions.DirectExecute for details. Returns the number of updated rows.
function ExecSQL(AExecDirect: Boolean): LongInt; overload;
- The third overloaded method executes the SQL statement specified in
ASQL, or the statement assigned to the SQL property, ifASQLis empty. Returns the number of updated rows.
FDQuery1.ExecSQL('create table ...');
- The fourth overloaded method executes the SQL statement specified in
ASQL, or the statement assigned to the SQL property, ifASQLis empty. Returns the number of updated rows.AParamsrepresents an open array of parameter values.
//Example 1
FDQuery1.ExecSQL('insert into mytab (f1, f2) values (:f1, :f2)', [100, 'qweqwe']);
//Example 2
FDQuery1.SQL.Text := 'insert into mytab (f1, f2) values (:f1, :f2)';
FDQuery1.Params[0].DataType := ftInteger;
FDQuery1.Params[1].DataType := ftWideString;
FDQuery1.ExecSQL('', [100, 'qweqwe']);
FDQuery1.ExecSQL('', [101, 'asdasd']);
FDQuery1.ExecSQL('', [102, 'zxczxc']);
- The fifth overloaded method executes the SQL statement specified in
ASQL, or the statement assigned to the SQL property, ifASQLis empty. Returns the number of updated rows.AParamsrepresents an open array of parameter values.
ATypes represents the open array of parameter data types that will be assigned for the query.
FDQuery1.ExecSQL('insert into mytab (f1, f2) values (:f1, :f2)',
[100, 'qweqwe'], [ftInteger, ftWideString]);
Use ExecSQL to execute queries that do not return a cursor to data (such as INSERT, UPDATE, DELETE, CREATE TABLE).
For Array DML, call Execute instead of ExecSQL.
ExecSQL prepares the statement in the SQL property for execution if it has not already been prepared. To speed performance, an application should ordinarily call Prepare before calling ExecSQL for the first time.
Use ResourceOptions.CmdExecMode to control the asynchronous execution mode. And ResourceOptions.CmdExecTimeout to set the maximum command execution time. After that time command, the execution is canceled and an exception is raised.
To cancel the command execution, use TFDAdaptedDataSet.AbortJob.
Before the command execution, the BeforeExecute event is fired. If the server returns a command execution error, then FireDAC raises an exception. It can be analyzed in the OnError event. After the command execution is finished, the AfterExecute event is fired.