TADConnection offers ExecSQL methods. These are simple to use overloaded methods useful when:

• no result sets are returned;
• a SQL command will be executed only once, so does not need to be stored in prepared state;
• no need for advanced parameters setup;
• no need for design-time SQL command setup.

For example to execute few DDL commands: 

ADConnection1.ExecSQL('drop table testtab');
ADConnection1.ExecSQL('create table testtab (id integer, name varchar(10))');
ADConnection1.ExecSQL('insert into testtab values (1, ''anydac'')');

To execute a parameterized query use the other overloaded method: 

ADConnection1.ExecSQL('insert into testtab values (:id, :name)', [1, 'anydac']);

Also, TADConnection offers ExecSQLScalar methods different from ExecSQL methods, that they are returning a single result set value: 

sName := ADConnection1.ExecSQLScalar('select name from testtab where id = :id', [1]);

In general, the TADQuery may be setup at design-time and / or at run-time. 

To setup it at design-time, drop TADQuery on a form. TADQuery.Connection will be automatically set to point to a TADConnection on this form, if any. Then double click on TADQuery to invoke the AnyDAC Query Editor: 

There you can specify SQL text, optionally specify parameter and macro values and set options. Press "Query Builder" button to invoke the graphical SQL query builder tool. Note, to enable syntax highlighting check QU4 at GUI Questions. 

Press "Execute" button to execute the query. If a command returns a result set, then it will be output on "RecordSet" pane and the result set structure on the "Structure" pane. If DBMS returns any messages or warnings for a command, they will be put to "Messages" pane. The "Next RecordSet" button allows to walk through all result sets returned by command. 

When you are testing DML commands, like a UPDATE / INSERT / DELETE, consider to mark "Auto Rollback" check box to automatically rollback actions performed by a command. 

Press OK to store changes in the TADQuery. 

The parameterized query usage is one of the best practices at SQL database application development. Main benefits are:

• you may compose single SQL command and use it few times with different parameter values. DBMS will build command execution plan only once. That reduces the DBMS engine load.
• when you will execute the command next time, only parameter values will be transferred to the DBMS engine. That reduces 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 :<name> syntax. Then assign corresponding values using Params collection. For example: 

ADQuery1.SQL.Text := 'select * from tab where code = :Code';
ADQuery1.ParamByName('code').AsString := '123';
ADQuery1.Open;

Before query will be executed each parameter Name, DataType and ParamType must be filled. Also Position may be need to set. 

When you assign SQL property and ResourceOptions.ParamCreate is True, the Params collection will be automatically filled in. Note, that TADParam.Name and TADParam.Position only will be set. The DataType, ParamType and other properties must be set by application. The parameters in Params collection appears in the same order as in the SQL command. 

Also you can fill the Params by code. For that you have to turn off automatic parameters creation by setting ResourceOptions.ParamCreate to False. For example: 

ADQuery1.ResourceOptions.ParamCreate := False;
ADQuery1.SQL.Text := 'select * from tab where code = :Code';
with ADQuery1.Params do begin
  Clear;
  with Add do begin
    Name := 'CODE';
    DataType := ftString;
    Size := 10;
    ParamType := ptInput;
  end;
end;

The parameters may be bind to the SQL command either by name, either by position. Use the Params.BindMode property to control that:

• pbByName - the parameters in Params collection will be bind by the Name property to the corresponding parameter markers. If SQL command has few the same named parameter markers, then only single instance will appear in Params.
• pbByNumber - the parameters in Params collection will be bind by the Position property to the markers in SQL command text. If SQL command has few the same named parameter markers, then every one occurrence will appear in Params. When Position is not set for parameters, it will be assigned automatically at command preparation using parameter indexes. The Position starts from 1.

The DataType may be specified either explicitly, either implicitly by assigning a parameter value to the Value or AsXxxx properties. Otherwise, the FormatOptions.DefaultParamDataType will be used. 

When ParamType is not specified, the ResourceOptions.DefaultParamType will be used. By default all parameters are input only. If some of the parameters are output, then this must be specified explicitly. 

The input parameter values must be set before query execution. That may be done using one of the ways:

• by explicitly assigning parameter values:

ADQuery1.ParamByName('code').AsString := 'DA1001';
ADQuery1.Open;

• by using one of the overloaded ExecSQL or Open methods:

ADQuery1.Open('', ['DA1001']);

To set parameter value to Null, specify parameter data type, then call Clear method: 

with ADQuery1.ParamByName('name') do begin
  DataType := ftString;
  Clear;
end;
ADQuery1.ExecSQL;

The output parameter values are accessible after query is executed. Some SQL commands and DBMS may require first to process all command result sets, then output parameter values will be received. To force the output parameter values delivery use the TADAdaptedDataSet.GetResults method. Also, read "RETURNING unified support" article about output parameters in Firebird and Oracle RETURNING phrase. 

Before a query will be executed it must be prepared. For that AnyDAC automatically performs the following actions:

• brings the connection to active or online mode;
• checks that all parameters are set correctly;
• preprocesses command text;
• optionally applies RecsSkip and RecsMax to the command text;
• optionally starts a transaction for Firebird / Interbase DBMS, if there is no one active;
• transfers the final command text to the DBMS API. The final text may be get using TADQuery.Text property.

While the command is prepared the connection must be active and the query will keep server resources. To unprepare query and release all resources use Unprepare or Disconnect methods. 

When you need to execute a command only once, you may set ResourceOptions.DirectExecute to True. For some DBMS (SQL Server, SQL Anywhere) that will speed up execution, because will avoid the costly operation of SQL command preparation. 

To execute a query, which does not return a result set, use the ExecSQL methods. If a query returns a result set, then exception "[AnyDAC][Phys][MSAcc]-310. Cannot execute command returning result sets" will be raised. 

To execute a query, returning a result set and open this result set, use the Open methods. If a query returns no result sets, then exception "[AnyDAC][Phys][MSAcc]-308. Cannot open / define command, which does not return result sets" will be raised. 

To execute an add-hock query, use ExecuteOrOpen method. Note, that query may be executed asynchronously. If the query is a command batch, then check "Command Batches" for details. 

When the parameter type was changed after the first query Prepare / ExecSQL / Open call, then AnyDAC will raise an exception on a subsequent call: 

[AnyDAC][Phys][IB]-338. Parameter [Xxxx] data type is changed.
Query must be reprepared.

AnyDAC at first Prepare / ExecSQL / Open call remembers the parameter data type. The data type may be specified either explicitly by setting TADParam.DataType property, either implicitly by assigning value using TADParam.AsXxxx or TADParam.Value properties. When before next ExecSQL / Open call an application changes parameter data type, then on the next ExecSQL / Open call "Parameter [xxxxx] data type is changed" will be raised. 

The TADParam.Value property does not change parameter data type on the consequent calls. It casts the assigning value to the current data type. So, to avoid the above exception use:

• TADParam.Value property value;
• TADParam.AsXxxx property, as Xxxx was used for first time.

Use the TADQuery.RowsAffected property to get the number of rows processed by the command. For example, the number of deleted rows by the DELETE command. Note, at MS SQL Server RowsAffected may be unexpectedly equal to -1 when a stored procedure or a table trigger omits SET NOCOUNT ON. And use the TADQuery.RecordCount property to get the number of fetched rows. Note, AnyDAC does not provide "N rows processed" messages, if needed the application has to build it. 

If the command will return an error, an exception will be raised. See the "Handling Errors" topic for more details. The exception may be processed using one of 3 ways:

• using try / except / end construction. For example:

try
  ADQuery1.ExecSQL;
except
  on E: EADDBEngineException do
    ; // do something here
end;

• setting TADQuery.OnError event handler;
• setting TADConnection.OnError event handler. For example:

procedure TForm1.ADConnection1Error(ASender: TObject; const AInitiator: IADStanObject;
  var AException: Exception);
begin
  if (AException is EADDBEngineException) and (EADDBEngineException(AException).Kind = ekRecordLocked) then
    AException.Message := 'Please, try the operation later. At moment, the record is busy';
end;

ADConnection1.OnError := ADConnection1Error;

Note, in case of the Array DML, the error handling may be more complex. 

Also the command may return warnings, hints and messages, depending on a DBMS. To enable messages processing set ServerOutput to True. To process them use the TADConnection.Messages property. For example: 

var
  i: Integer;
begin
  ADConnection1.ResourceOptions.ServerOutput := True;
  ADQuery1.ExecSQL;
  if ADConnection1.Messages <> nil then
    for i := 0 to ADConnection1.Messages.ErrorCount - 1 do
      Memo1.Lines.Add(ADConnection1.Messages[i].Message);
end;

Some DBMS, like SQL Server, returns messages as an additional result set. So, to process messages the application will need to process multiple result sets. And more complex example, providing status and messages for SQL Server. As you see, we are using TADMemTable to store result set with rows. 

var
  i: Integer;
begin
  ADConnection1.ResourceOptions.ServerOutput := True;
  ADQuery1.FetchOptions.AutoClose := False;
  ADQuery1.Open('select * from Region; print ''Hello''');
  ADMemTable1.Data := ADQuery1.Data;
  Memo1.Lines.Add(Format('%d rows processed', [ADMemTable1.RecordCount]));
  ADQuery1.NextRecordSet;
  if ADConnection1.Messages <> nil then
    for i := 0 to ADConnection1.Messages.ErrorCount - 1 do
      Memo1.Lines.Add(ADConnection1.Messages[i].Message);
end;

By default all SQL command executions are performed in auto-commit mode. That means, when right before command execution there was no active transaction, then it will be started. And right after command execution this implicit transaction will be finished:

• by Commit for successful execution;
• by Rollback for failure.

In case of Firebird and Interbase an implicit transaction will be started right before query preparation. Note, that there is no need to surround a single command execution into explicit transaction, just use the auto-commit mode. 

Most database applications have backend administration utilities, which must execute SQL script. These scripts are written using lines appropriate to the DBMS SQL script syntax. To execute SQL scripts use TADScript component. 

Sometimes a database application may need to execute heterogeneous queries using tables from different databases or to execute SQL commands on TDataSet descendants instead of the database tables. To execute such queries use Local SQL engine and TADLocalSQL component.

Executing Stored Procedure, Browsing Table, Command Batches, Asynchronous Execution, Local SQL, TADQuery Class