FireDAC.Comp.Client.TFDCustomQuery.ExecSQL

From RAD Studio API Documentation
Jump to: navigation, search

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(void)/* 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 specified in ASQL, or the statement assigned to the SQL property, if ASQL is empty. Returns the number of updated rows.
FDQuery1.ExecSQL('create table ...');
  • The third overloaded method executes the SQL statement specified in ASQL, or the statement assigned to the SQL property, if ASQL is empty. Returns the number of updated rows. AParams represents 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 fourth overloaded method executes the SQL statement specified in ASQL, or the statement assigned to the SQL property, if ASQL is empty. Returns the number of updated rows. AParams represents 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.

See Also