Executing Commands (FireDAC)
Go Up to Working with Commands (FireDAC)
TFDConnection offers ExecSQL methods. These are simple to use overloaded methods, useful when:
- No result sets are returned.
- A SQL command is executed only once, so it does not need to be stored in prepared state.
- There is no need for advanced parameters setup.
- There is no need for design-time SQL command setup.
For example, to execute DDL commands, use the following:
FDConnection1.ExecSQL('drop table testtab'); FDConnection1.ExecSQL('create table testtab (id integer, name varchar(10))'); FDConnection1.ExecSQL('insert into testtab values (1, ''FireDAC'')');
To execute a parameterized query, use the other overloaded method:
FDConnection1.ExecSQL('insert into testtab values (:id, :name)', [1, 'FireDAC']);
Also, TFDConnection offers ExecSQLScalar methods different from ExecSQL methods, as they are returning a single result set value:
sName := FDConnection1.ExecSQLScalar('select name from testtab where id = :id', );
In general, TFDQuery is set up at design time and/or at runtime.
Setting TFDQuery at Design Time
To set up TFDQuery at design time, drop it on a form. TFDQuery.Connection will be automatically set to point to a TFDConnection on this form, if any. Then double-click TFDQuery to invoke the FireDAC Query Editor:
There, you can specify SQL text, and optionally specify parameter and macro values, and set options.
Press the Execute button to execute the query. If a command returns a result set, it will be displayed on the RecordSet pane, and the result set structure will be displayed on the Structure pane. If DBMS returns any messages or warnings for a command, these will be displayed in the Messages pane. The Next RecordSet button allows you to walk through all the result sets returned by the command.
When you are testing DML commands, such as UPDATE/INSERT/DELETE, you need to mark the Auto Rollback check box to automatically rollback the actions performed by a command.
Press OK to store changes in the TFDQuery.
The parameterized query usage is one of the best practices at SQL database application development. The main benefits are:
- You are able to compose a single SQL command and use it several times with different parameter values. DBMS will build the command execution plan only once. This reduces the DBMS engine load.
- The next time you execute the command, only the parameter values will be transferred to the DBMS engine. That reduces the network load.
- You will not care about correct SQL constant formats, for example, how to properly write a date constant in Microsoft Access SQL.
To put a parameter marker into the SQL text, use the :<name> syntax. Then assign corresponding values using the Params collection. For example:
FDQuery1.SQL.Text := 'select * from tab where code = :Code'; FDQuery1.ParamByName('code').AsString := '123'; FDQuery1.Open;
Before the query execution, each parameter Name, DataType, and ParamType must be filled. Also, Position should be set.
When you assign the SQL property and ResourceOptions.ParamCreate is True, the Params collection is automatically filled in.
- Note: Only TFDParam.Name and TFDParam.Position are set. The DataType, ParamType, and other properties must be set in your application. The parameters in the Params collection appear in the same order as in the SQL command.
Also you can fill the Params collection by code. For that you have to turn off the automatic parameters creation by setting ResourceOptions.ParamCreate to False. For example:
FDQuery1.ResourceOptions.ParamCreate := False; FDQuery1.SQL.Text := 'select * from tab where code = :Code'; with FDQuery1.Params do begin Clear; with Add do begin Name := 'CODE'; DataType := ftString; Size := 10; ParamType := ptInput; end; end;
The parameters can be bound to the SQL command either by name, either by position. Use the Params.BindMode property to control that:
- pbByName. The parameters in the Params collection will be bound by the Name property to the corresponding parameter markers. If the SQL command has some parameter markers with the same name, only one instance will appear in Params.
- pbByNumber. The parameters in the Params collection will be bound by the Position property to the markers in the SQL command text. If the SQL command has some parameter markers with the same name, every occurrence will appear in Params. When Position is not set for parameters, it will be assigned automatically at command preparation using the parameter indexes. The Position starts at 1.
The DataType is specified either explicitly or implicitly, by assigning a parameter value to the Value or AsXxxx property. Otherwise, the FormatOptions.DefaultParamDataType is used.
When ParamType is not specified, the ResourceOptions.DefaultParamType is used. By default, all parameters are input parameters only. In order to use output parameters, ParamType must be explicitly specified.
The input parameter values must be set before the query execution. For this, choose one of the following options:
- Assign explicitly parameter values:
FDQuery1.ParamByName('code').AsString := 'DA1001'; FDQuery1.Open;
- Use one of the overloaded ExecSQL or Open methods:
To set the parameter value to Null, specify the parameter data type, then call the Clear method:
with FDQuery1.ParamByName('name') do begin DataType := ftString; Clear; end; FDQuery1.ExecSQL;
The output parameter values are accessible after the query execution. Some SQL commands and DBMS could require first to process all command result sets, then the output parameter values will be received. To force the output parameter values delivery, use the TFDAdaptedDataSet.GetResults method. Also, read the "RETURNING Unified Support" article about output parameters in the Firebird and Oracle RETURNING phrase.
Preparing the Query
Before a query is executed, it must be prepared. For that, FireDAC automatically performs the following actions:
- Brings the connection to active or online state.
- Checks that all the parameters are set correctly.
- Preprocesses the command text.
- Optionally, applies RecsSkip and RecsMax to the command text.
- Optionally, starts a transaction for Firebird/Interbase DBMS, when there is no active connection.
- Transfers the final command text to the DBMS API. The final text is obtained by using the TFDQuery.Text property.
While the command is prepared, the connection must be active and the query keeps the server resources. To unprepare the query and release all the resources, use the Unprepare or Disconnect method.
When you need to execute a command only once, set ResourceOptions.DirectExecute to True. For some DBMS (SQL Server, SQL Anywhere), this will speed up the execution, as it will avoid the costly operation of the SQL command preparation.
Executing the Query
To execute a query that does not return a result set, use the ExecSQL methods. If a query returns a result set, the [FireDAC][Phys][MSAcc]-310. Cannot execute command returning result sets exception is raised.
To execute a query, return a result set, and open this result set, use the Open methods. If a query returns no result sets, the [FireDAC][Phys][MSAcc]-308. Cannot open / define command that does not return result sets exception is raised.
To execute an ad-hoc query, use the ExecuteOrOpen method.
- Note: The query can be executed asynchronously. If the query is a command batch, check Command Batches for details.
The Parameter data type is changed Exception
When the parameter type was changed after the first query Prepare/ExecSQL /Open call, FireDAC raises an exception on a subsequent call:
[FireDAC][Phys][IB]-338. Parameter [Xxxx] data type is changed. Query must be reprepared.
At the first Prepare/ExecSQL/Open call, FireDAC remembers the parameter data type. The data type can be specified either explicitly, by setting the TFDParam.DataType property, or implicitly, by assigning a value using the TFDParam.AsXxxx or TFDParam.Value property. As the application changes the parameter data type before the next ExecSQL/Open call, when ExecSQL/Open is called again, the "Parameter [xxxxx] data type is changed" exception is raised.
The TFDParam.Value property does not change the parameter data type at the next call. It casts the assigning value to the current data type. So, to avoid the above exception, use:
- The TFDParam.Value property value.
- The TFDParam.AsXxxx property, as Xxxx was used for first time.
Getting DBMS Feedback
Use the TFDQuery.RowsAffected property to get the number of rows processed by the command (for example, the number of deleted rows by the DELETE command.)
- Note: For MS SQL Server, RowsAffected can be unexpectedly equal to -1 when a stored procedure or a table trigger omits SET NOCOUNT ON. Then, use the TFDQuery.RecordCount property to get the number of fetched rows.
- Note: FireDAC does not provide "N rows processed" messages. If needed, the application has to build such messages.
If the command returns an error, an exception is raised. See the Handling Errors topic for more details. The exception can be processed using one of the following three ways:
- Using the try/except/end construction. For example:
try FDQuery1.ExecSQL; except on E: EFDDBEngineException do ; // do something here end;
- Setting the TFDQuery.OnError event handler.
- Setting the TFDConnection.OnError event handler. For example:
procedure TForm1.FDConnection1Error(ASender: TObject; const AInitiator: IFDStanObject; var AException: Exception); begin if (AException is EFDDBEngineException) and (EFDDBEngineException(AException).Kind = ekRecordLocked) then AException.Message := 'Please, try the operation later. At moment, the record is busy'; end; FDConnection1.OnError := FDConnection1Error;
For the Array DML, the error handling is more complex.
Also, the command returns warnings, hints, and messages, depending on the DBMS. To enable messages processing, set ResourceOptions.ServerOutput to True. To process the messages, use the TFDConnection.Messages property. For example:
var i: Integer; begin FDConnection1.ResourceOptions.ServerOutput := True; FDQuery1.ExecSQL; if FDConnection1.Messages <> nil then for i := 0 to FDConnection1.Messages.ErrorCount - 1 do Memo1.Lines.Add(FDConnection1.Messages[i].Message); end;
Some DBMS, like SQL Server, return messages as an additional result set. So, to process messages, the application needs to process multiple result sets. Here is a more complex example, providing status and messages for SQL Server. As you see, we are using TFDMemTable to store result sets with rows.
var i: Integer; begin FDConnection1.ResourceOptions.ServerOutput := True; FDQuery1.FetchOptions.AutoClose := False; FDQuery1.Open('select * from Region; print ''Hello'''); FDMemTable1.Data := FDQuery1.Data; Memo1.Lines.Add(Format('%d rows processed', [FDMemTable1.RecordCount])); FDQuery1.NextRecordSet; if FDConnection1.Messages <> nil then for i := 0 to FDConnection1.Messages.ErrorCount - 1 do Memo1.Lines.Add(FDConnection1.Messages[i].Message); end;
Query Execution and Transactions
By default, all SQL command executions are performed in autocommit mode. This means that, if there is no active transaction right before the command execution, this implicit transaction starts. Similarly, right after the command execution, it is finished:
- by Commit, in case of successful execution.
- by Rollback, in case of failure.
For Firebird and Interbase, an implicit transaction starts right before the query preparation. There is no need to surround a single command execution into explicit transactions, just use the autocommit mode.
Most database applications have back-end administration utilities, which must execute SQL script. These scripts are written using lines in a way appropriate to the DBMS SQL script syntax. To execute SQL scripts, use the TFDScript component.
Sometimes, a database application executes heterogeneous queries by using tables from different databases, or executes SQL commands on TDataSet descendants instead of the database tables. To execute such queries, use the Local SQL engine and the TFDLocalSQL component.