Sending Commands to the Server
Go Up to Connecting to Databases Index
All database connection components except TIBDatabase let you execute SQL statements on the associated server by calling the Execute method. Although Execute can return a cursor when the statement is a SELECT statement, this use is not recommended. The preferred method for executing statements that return data is to use a dataset.
The Execute method is very convenient for executing simple SQL statements that do not return any records. Such statements include Data Definition Language (DDL) statements, which operate on or create a database's metadata, such as CREATE INDEX, ALTER TABLE, and DROP DOMAIN. Some Data Manipulation Language (DML) SQL statements also do not return a result set. The DML statements that perform an action on data but do not return a result set are: INSERT, DELETE, and UPDATE.
The syntax for the Execute method varies with the connection type:
- For TDatabase, Execute takes four parameters: a string that specifies a single SQL statement that you want to execute, a Data.DB.TParams object that supplies any parameter values for that statement, a boolean that indicates whether the statement should be cached because you will call it again, and a pointer to a BDE cursor that can be returned (It is recommended that you pass nil).
- For TADOConnection, there are two versions of Execute. The first takes a WideString that specifies the SQL statement and a second parameter that specifies a set of options that control whether the statement is executed asynchronously and whether it returns any records. This first syntax returns an interface for the returned records. The second syntax takes a WideString that specifies the SQL statement, a second parameter that returns the number of records affected when the statement executes, and a third that specifies options such as whether the statement executes asynchronously. Note that neither syntax provides for passing parameters.
- For TSQLConnection, Execute takes three parameters: a string that specifies a single SQL statement that you want to execute, a Data.DB.TParams object that supplies any parameter values for that statement, and a pointer that can receive a TCustomSQLDataSet that is created to return records.
Note: Execute can only execute one SQL statement at a time. It is not possible to execute multiple SQL statements with a single call to Execute, as you can with SQL scripting utilities. To execute more than one statement, call Execute repeatedly.
It is relatively easy to execute a statement that does not include any parameters. For example, the following code executes a CREATE TABLE statement (DDL) without any parameters on a TSQLConnection component:
procedure TForm1.CreateTableButtonClick(Sender: TObject);
var
SQLstmt: String;
begin
SQLConnection1.Connected := True;
SQLstmt := 'CREATE TABLE NewCusts ' +
'( ' +
' CustNo INTEGER, ' +
' Company CHAR(40), ' +
' State CHAR(2), ' +
' PRIMARY KEY (CustNo) ' +
')';
SQLConnection1.Execute(SQLstmt, nil, nil);
end;
To use parameters, you must create a Data.DB.TParams object. For each parameter value, use the TParams.CreateParam method to add a TParam object. Then use properties of TParam to describe the parameter and set its value.
This process is illustrated in the following example, which uses TDatabase to execute an INSERT statement. The INSERT statement has a single parameter named: StateParam. A TParams object (called stmtParams) is created to supply a value of "CA" for that parameter.
procedure TForm1.INSERT_WithParamsButtonClick(Sender: TObject);
var
SQLstmt: String;
stmtParams: TParams;
begin
stmtParams := TParams.Create;
try
Database1.Connected := True;
stmtParams.CreateParam(ftString, 'StateParam', ptInput);
stmtParams.ParamByName('StateParam').AsString := 'CA';
SQLstmt := 'INSERT INTO "Custom.db" '+
'(CustNo, Company, State) ' +
'VALUES (7777, "Robin Dabank Consulting", :StateParam)';
Database1.Execute(SQLstmt, stmtParams, False, nil);
finally
stmtParams.Free;
end;
end;
If the SQL statement includes a parameter but you do not supply a TParam object to provide its value, the SQL statement may cause an error when executed (this depends on the particular database back-end used). If a TParam object is provided but there is no corresponding parameter in the SQL statement, an exception is raised when the application attempts to use the TParam.