Advantage Developer Zone

 
 
 

Executing SQL Statements in Delphi Without the Overhead of a TAdsQuery Component

Monday, October 14, 2002

It is often useful to be able to execute certain SQL statements without the overhead of declaring a TAdsQuery instance. In these situations, the TAdsConnection.Execute method can be used to execute any SQL statement that does not return a result set.

The TAdsConnection.Execute method supports parameterized queries and uses a statement caching scheme to enhance performance when multiple queries are run.

Using the TAdsConnection.Execute method can reduce the number of TAdsQuery components in your application, often making the code more readable and easier to maintain.

Syntax
function Execute( const SQL: string; Params: TParams = nil; Cache: Boolean = False; Cursor: PADSHANDLE = nil): Integer; overload; virtual;
function Execute(oAdsDatasetOptions : TAdsDatasetOptions; const SQL: string; Params: TParams = nil; Cache: Boolean = False; Cursor: PADSHANDLE = nil): Integer; overload; virtual;
TAdsDatasetOptions = record
    musAdsLockType : UNSIGNED16; { Specific table lock type }
    musAdsCharType : UNSIGNED16; { Specific char type }
    musAdsRightsCheck : UNSIGNED16; { rights checking is enabled }
    musAdsTableType : UNSIGNED16; { Table type }
end;

Parameters
SQL is a string value containing the statement to be executed.

Params is a TParams object containing the parameter values to use in the statement.

Cache is a boolean value used to specify whether a cached statement handle should be re-used when executing this statement. If executing the same statement multiple times, setting this parameter to TRUE can significantly enhance performance. If this parameter is TRUE and you are executing multiple statements that are different (different SQL string) you may quickly consume server resources.

oAdsDatasetOptions is a record that can be used to pass statement options to the Execute method. This record is only necessary if you require options other than the defaults. The option values are defined in ace.pas

The Cursor parameter is used internally and should never be passed to this function by an application developer.

Example
procedure Tform1.Test ( Cache : boolean );
var
    i : integer;
    Params : tparams;
begin
    AdsConnection1.Connect;
    Params := TParams.Create();
    Params.CreateParam( ftInteger, ’empid’, ptInput ); f
    or i := 0 to 0 do
    begin
        Params[0].Value := i;
        AdsConnection1.Execute( ’insert into demo10 (empid) values (:empid)’, Params, Cache );
        if ( i mod 50 ) = 0 then
        begin
            Label1.Caption := IntToStr( i );
            Application.Processmessages;
        end;
    end;
    AdsConnection1.Disconnect;
end;