The ADO components encapsulate the functionality of the ADO framework. ADO, or Microsoft ActiveX Data Objects, is a set of data objects that provide an application the ability to access data through an OLE DB provider. The Delphi ADO components encapsulate the functionality of these ADO objects and present their functionality in the context of Delphi components. The ADO objects that figure most prominently are the Connection, Command, and Recordset objects. These ADO objects are directly represented in the TADOConnection, TADOCommand, and ADO dataset components. There are other "helper" objects in the ADO framework, like the Field and Properties objects, but they are generally not used directly by the Delphi programmer and not represented by dedicated components.
Using ADO and the ADO components allows the Delphi programmer to create database applications that are not dependent on the Borland Database Engine (BDE), using instead ADO for the data access.
This chapter presents each of the ADO components and discusses how they differ from their BDE-based counterparts. References are given to topics covering aspects of the BDE-based connection and dataset components that are the same in the ADO equivalents.
In addition to the connection and dataset components based on the Borland Database Engine (BDE), Delphi provides a set of components for use with ADO. These components allow the programmer to connect to an ADO data store and then to execute commands and retrieve data from tables in databases.
These ADO-centric data access components connect to ADO data stores and operate on data using only the ADO framework. The BDE is not employed at all in this process. Use the ADO components when ADO is available and you do not want to use the BDE. ADO 2.1 (or higher) must be installed on the host computer. Additionally, client software for the target database system (such as Microsoft SQL Server) must be installed as well as an OLE DB driver or ODBC driver specific to the particular database system.
Most of the ADO connection and dataset components are analogous to one of the BDE-based connection or dataset components. The TADOConnection component is functionally analogous to the TDatabase component in BDE-based applications. TADOTable is equivalent to TTable, TADOQuery to TQuery, and TADOStoredProc to TStoredProc. Use these ADO components in the same manner and context as you would the BDE-based data equivalents. TADODataSet has no direct BDE equivalent, but provides many of the same functions as TTable and TQuery. Similarly, there is no BDE component comparable to TADOCommand, which serves a specialized purpose in the Delphi/ADO environment.
The ADO components comprise the following classes.
Before commands can be executed or data retrieved, an application must establish a connection to a data store. While each individual ADO command and dataset component in an application can establish its own connection, a TADOConnection can be used and its single connection shared by other ADO components.
When connecting ADO command and dataset components to a data store, they can all use a shared connection or the components can each establish their own connections. Each approach has its own advantages and disadvantages.
This section covers the tasks involved in establishing and using a connection to an ADO data store.
One or more ADO dataset and command components can share a single connection to a data store. To do this, the application must have one TADOConnection component to make each data store connection. Then, the dataset and command components are associated with the connection component through their Connection properties.
In addition to providing the means for dataset and command components to connect to a data store, connection components provide properties and methods for activating and deactivating the connection, accessing the ADO connection object directly, and for determining what activity (if any) a connection component is engaged in at any given time.
Each ADO command and dataset component in an application may be connected directly to a data store. However, when numerous command and dataset components are used, it is most often easier to maintain the connection using a single TADOConnection to establish the connection and then sharing that connection between the command and dataset components. See the section "Connecting to a data store using ADO dataset components" for more information on connecting individual command and dataset directly to a data store.
Using a TADOConnection component to establish the connection offers more control over the connection versus connecting each command or dataset component individually. This greater control is provided by the properties, methods, and event of the TADOConnection, the functionality of which is not available otherwise.
To use a TADOConnection component to supply a shared connection for ADO dataset and command components, first establish the connection. Do this by supplying specific connection information in the ConnectionString property of the connection component. At design-time, invoke the connection string editor dialog by clicking the ellipsis button for the ConnectionString property in the Object Inspector. This dialog, supplied by the ADO system itself, allows you to interactively build a connection string by selecting connection elements (like the provider and server) from lists. At runtime, assign a String value to the ConnectionString property with the connection information. Setting the Connected property of the connection component to True would activate the connection. However, it is not essential to do so at this point. At design-time this is a good test of the connection, though.
ADOConnection1.ConnectionString := 'Provider=ProviderName;Remote Server=ServerReference';
The ConnectionString property can contain a number of connection parameters, each separated by semi-colons. These parameters can include the name of a provider, a user name and password (for login purposes), and a reference identifying a remote server. The ConnectionString property can also contain the name of a file containing the connection parameters. Such a file has the same contents as the ConnectionString property: one or more parameters, each with a value assignment and separated from other parameters by a semi-colon. See the VCL help topic for the ConnectionString property for a list of ADO-supported parameters.
Once the connection information has been provided in the connection component, associate dataset and command components with the connection component. Do this by assigning a reference to the connection component to each dataset or command component's Connection property. At design-time, select the desired connection component from the drop-down list for the Connection property in the Object Inspector. At runtime, assign the reference to the Connection property. For example, the command below associates a TADODataSet component with a TADOConnection component.
ADODataSet1.Connection := ADOConnection1;
If you do not explicitly activate the connection by setting the connection component's Connected property to True, it will happen automatically when the first dataset component is activated or the first time a command is executed with a command component.
Use the ConnectionObject property of TADOConnection to access the underlying ADO connection object directly. Using this reference it is possible to access properties and call methods of the underlying ADO Connection object from an application.
Use of ConnectionObject to directly access the underlying ADO Connection object requires a good working knowledge of ADO objects in general and the ADO Connection object in specific. It is not recommended that you use the Connection object directly unless familiar with Connection object operations. Consult the Microsoft Data Access SDK help for specific information on using ADO Connection objects.
To activate an ADO connection component, set the TADOConnection.Active property to True or call the TADOConnection.Open method.
ADOConnection1.Active := True;
For the connection to be successful, the connection information provided in the TADOConnection.ConnectionString property must define a valid connection. For more information on providing connection information, see "Specifying the connection".
Activating an ADO connection component will trigger the OnWillConnect and OnConnectComplete events of the ADO connection component and execute handlers for these events if they have been assigned.
If a connection component has not already been activated, it will automatically be activated if an associated dataset or command component is enabled. Dataset components cause this when they are activated. Command components do this when a command is executed. For information on associating dataset components with a connection component, see "Connecting to a data store using TADOConnection".
To deactivate an ADO connection component, either set its Active property to False or call its Close method.
ADOConnection1.Close;
Four things happen when a connection component is deactivated, using either the Active property or the Close method:
At any time during the existence of a TADOConnection component, query its State property to determine what action, if any, in which the connection component is currently engaged.
A TObjectStates value of stClosed in the TADOConnection.State property indicates that the connection object is currently inactive. The TADOConnection.Active property contains a value of False and no associated command or dataset components are active.
A value of stOpen indicates that the connection component is active. A connection with an ADO data store has been successfully established, its Active property contains a value of True, and any one or more associated command or dataset components might be active.
A value of stConnecting indicates the connection component is currently attempting to establish a connection to the ADO data store specified in the TADOConnection.ConnectionString property. While still in this state, the Cancel method may be called to abort the connection attempt.
When a TADOConnection component is used to make the connection to a data store for an application's ADO command and dataset components, you have a greater degree of control over the conditions and attributes of the connection. These aspects are implemented using properties and event handlers of TADOConnection to fine-tune the connection.
Use the TADOConnection.ConnectOptions property to optionally force the connection to be asynchronous. By default, ConnectionOptions is set to coConnectUnspecified which allows the server to decide the best type of connection. To explicitly make the connection asynchronous, set ConnectOptions to coAsyncConnect.
To set up a connection as asynchronous or to delegate the choice to the server, assign one of the TConnectOption constants to the connection component's ConnectOptions property. Then activate the connection component by calling its Open method, setting the Connected property to True, or by activating an associated command or dataset component. The example routines below respectively enable and disable asynchronous connections in the specified connection component.
procedure TForm1.AsyncConnectButtonClick(Sender: TObject); begin with ADOConnection1 do begin Close; ConnectOptions := coAsyncConnect; Open; end; end; procedure TForm1.ServerChoiceConnectButtonClick(Sender: TObject); begin with ADOConnection1 do begin Close; ConnectOptions := coConnectUnspecified; Open; end; end;
Use the TADOConnection.Attributes property to control the connection component's use of retaining commits and retaining aborts. Attributes can contain one, both, or neither of the constants xaCommitRetaining and xaAbortRetaining. This makes controlling retaining commits and retaining aborts mutually exclusive using the same property.
Check whether either retaining commits or retaining aborts is enabled using the in operator with one of the constants. Enable one feature by adding the constant to the attributes property; disable one by subtracting the constant. The example routines below respectively enable and disable retaining commits in the specified connection component.
procedure TForm1.RetainingCommitsOnButtonClick(Sender: TObject); begin with ADOConnection1 do begin Close; if not (xaCommitRetaining in Attributes) then Attributes := (Attributes + [xaCommitRetaining]) Open; end; end; procedure TForm1.RetainingCommitsOffButtonClick(Sender: TObject); begin with ADOConnection1 do begin Close; if (xaCommitRetaining in Attributes) then Attributes := (Attributes - [xaCommitRetaining]); Open; end; end;
Control the period of time before attempted commands and connections are considered failed and are aborted using the TADOConnection.ConnectionTimeout property and the TADOConnection.CommandTimeout property.
ConnectionTimeout establishes the amount of time before an attempt to connect to the data store times-out. If the connection initiated by a call to the Open method has not successfully completed prior to expiration of the time specified in ConnectionTimeout, the connection attempt is cancelled. Set ConnectionTimeout to the number of seconds after which connection attempts time-out.
with ADOConnection1 do begin
ConnectionTimeout = 10 {seconds};
Open;
end;
CommandTimeout establishes the amount of time before attempted commands time-out. If the command initiated by a call to the Execute method has not successfully completed prior to expiration of the time specified in CommandTimeout, the command is cancelled and ADO generates an exception. Set CommandTimeout to the number of seconds after which commands time-out.
with ADOConnection1 do begin
CommandTimeout = 10 {seconds};
Execute('DROP TABLE Employee1997', []);
end;
An attempt to connect to a data store using a connection component triggers a security login event, OnLogin. One manifestation of this event is the appearance of a login dialog prompting for a user name and password. If desired, this dialog may be suppressed and the user name and password information supplied programmatically.
To suppress the default login dialog, first set the LoginPrompt property of the connection component to False. Then, prior to activating the connection component, supply all necessary login information via a vehicle such as the ConnectionString property.
with ADOConnection1 do begin Close; LoginPrompt := False; ConnectionString := 'Provider=NameOfYourProvider;Remote Server=NameOfYourServer;' + 'User Name=JaneDoe;Password=SecretWord'; Connected := True; end;
The login information can also be conveyed to the target data store as parameters for the connection component's Open method.
with ADOConnection1 do begin
Close;
LoginPrompt := False;
ConnectionString := 'Provider=NameOfYourProvider;Remote Server=NameOfYourServer';
Open('JaneDoe', 'SecretWord');
end;
The second routine above is functionally equivalent to the first. The difference is that in the second routine the user name and password are not expressed in the ConnectionString property, but passed as parameters for the Open method. This is useful in situations where the connection specifications (like provider and server) are the same for all users and only the information particular to individual users changes. The application might, for instance, obtain the user-specific information via a custom login dialog, and the provider and server information form a static source like Windows Registry entries.
If, after supplying the login information programmatically, the login attempt is unsuccessful, an exception of type EOleException is raised.
The TADOConnection component provides properties for retrieving lists of the tables and stored procedures available through the connection. It also provides properties for accessing the dataset and command components associated with the connection component.
The DataSets and DataSetCount properties of TADOConnection allow a program to sequentially reference each dataset component associated with a connection component. Dataset components operated on by the DataSets and DataSetCount properties include TADODataSet, TADOQuery, and TADOStoredProc. For working with a connection's command components, use the Commands and CommandCount properties.
DataSets is a zero-based array of references to ADO dataset components. Use an index with DataSets representing the position within the array of a particular dataset. For instance, use an index of 3 to reference the fourth dataset component in DataSets.
ShowMessage(ADOConnection1.DataSets[3].Name);
As DataSets provides a reference of type TCustomADODataSet, typecast this reference as a descendent class type to access a property or call a method only available in a descendent class. For instance, TCustomADODataSet does not have an SQL property, but the descendent class TADOQuery does. So, to access the SQL property of the dataset referenced through the DataSets property, typecast it as a TADOQuery.
with (ADOConnection1.DataSets[10] as TADOQuery do begin
SQL.Clear;
SQL.Add('SELECT * FROM Species');
Open;
end;
The DataSetCount property provides a total count of all of the datasets associated with a connection component. You can use the DataSetCount property as the basis for a loop with the DataSets property to sequentially visit all of the dataset components associated with a connection.
var i: Integer begin for i := 0 to (ADOConnection4.DataSetCount) do ADOConnection4.DataSets[i].Open; end;
The Commands and CommandCount properties of TADOConnection act in much the same manner as the DataSets and DataSetCount properties. The difference is that Commands and CommandCount provide references to all of the TADOCommand components associated with the connection component. To work with all of the connection's dataset components, use the DataSets and DataSetCount properties.
Commands is a zero-based array of references to ADO command components. Use an index with Commands representing the position within the array of a particular command. For instance, use an index of 1 to reference the second command component in Commands.
Memo1.Lines.Text := ADOConnection1.Commands[1].CommandText;
The CommandCount property provides a total count of all of the commands associated with a connection component. You can use the CommandCount property as the basis for a loop with the Commands property to sequentially visit all of the command components associated with a connection.
var i: Integer begin for i := 0 to (ADOConnection1.CommandCount) do ADOConnection1.Commands[i].Execute; end;
To get a listing of all of the tables contained in the database accessed via the connection object, use the GetTableNames method. This method copies a list of table names to an already-existing string list object. Use individual elements from this list for such things as the value for the TableName property of a TADOTable component or the name of a table in an SQL statement executed by a TADOQuery.
ADOConnection1.GetTableNames(ListBox1.Items, False);
The example below traverse a list of table names created using the GetTableNames method. For each table, the routine makes an entry in another table with the table's name and number of records.
procedure TForm1.Button1Click(Sender: TObject);
var
SL: TStrings;
index: Integer;
begin
SL := TStringList.Create;
try
ADOConnection1.GetTableNames(SL, False);
for index := 0 to (SL.Count - 1) do begin
Table1.Insert;
Table1.FieldByName('Name').AsString := SL[index];
ADOTable1.TableName := SL[index];
ADOTable1.Open;
Table1.FieldByName('Records').AsInteger :=
ADOTable1.RecordCount;
Table1.Post;
end;
finally
SL.Free;
ADOTable1.Close;
end;
end;
To get a listing of all of the stored procedures contained in the database accessed via the connection object, use the GetProcedureNames method. This method copies a list of stored procedure names to an already-existing string list object. One of the elements in the resulting list can be used for such things as the value for the ProcedureName property of a TADOStoredProc component.
ADOConnection1.GetProcedureNames(ListBox1.Items);
In the example below, a list of stored procedure names retrieved with GetProcedureNames is used to execute all of the stored procedures from the associated database.
procedure TDataForm.ExecuteProcsButtonClick(Sender: TObject); var SL: TStrings; index: Integer; begin SL := TStringList.Create; try ADOConnection1.GetProcedureNames(SL); if (SL.Count > 0) then for index := 0 to (SL.Count - 1) do begin ADOStoredProc1.ProcedureName := SL[index]; ADOStoredProc1.ExecProc; end; finally SL.Free; end; end;
The TADOConnection component includes a number of methods and events for working with transactions. These transaction capabilities are shared by all of the ADO command and dataset components using the data store connection.
Use the methods BeginTrans, CommitTrans, and RollbackTrans to perform transaction processing. BeginTrans starts a transaction in the data store associated with the ADO connection component. CommitTrans commits a currently active transaction, saving changes to the database and ending the transaction. RollbackTrans cancels a currently active transaction, abandoning all changes made during the transaction and ending the transaction. Read the InTransaction property to determine at any given point whether the connection component has a transaction open.
A transaction started by the connection component is shared by all command and dataset components that use the connection established by the TADOConnection component.
The ADO connection component provides a number of events for detecting when transaction-related processes have been completed. These events indicate when a transaction process initiated by a BeginTrans, CommitTrans, and RollbackTrans method have been successfully completed at the data store.
The OnBeginTransComplete event is triggered when the data store has successfully started a transaction after a call to the connection component's BeginTrans method. The OnCommitTransComplete event is triggered after a transaction is successfully committed due to a call to CommitTrans. And OnRollbackTransComplete is triggered after a transaction is successfully committed due to a call to RollbackTrans.
The ADO dataset components provided in Delphi are analogous to the BDE-based dataset components. For instance, the TADOTable component is functionally equivalent to the TTable component. The main difference is that the ADO dataset components use underlying ADO objects for their data access and are not dependent on the Borland Database Engine for this.
The ADO dataset and BDE-based components have the TDataSet class as a common ancestor. Because of this, they share a common functionality in inherited or similar properties, methods, and events. This section primarily discusses areas of the ADO dataset components that differ from the corresponding generic dataset components. For more information on functionality common between the two sets of dataset components, see the descriptions for the generic and BDE-based dataset components:
This section contains information pertaining to functionality that differs in the ADO versions of the dataset components from their generic counterparts. This information is divided into the areas:
Certain aspects of the ADO dataset components function exactly the same in all of the different components. Except as cited, these functional areas are used in exactly the same manner no matter which ADO dataset component is in use.
Accessing columns in ADO dataset components and modifying data is done in the exact same manner as in the generic dataset components. Use dataset methods like Edit and Insert to put the dataset in edit mode prior to changing data. Use the Post method to finalize data changes.
Use the dynamic TField references provided by the Fields property and FieldByname method of the dataset components. From there use the properties and methods of the TField class and descendents to do such things as setting or getting a column's value, validating data, and determining a column's data type.
For information on modifying data through dataset components, see "Modifying data". For information on using table columns and persistent field objects, see "Working with field components".
Navigating between rows in an ADO dataset is done in the same way as in generic dataset components. Use methods like First, Next, Last, and Prior to move the record pointer in the dataset component from one table row to another. Loops can be based on the Eof and Bof properties so that they operate on all of the rows that make up a dataset.
ADOTable1.First;
while not ADOTable1.Eof do begin
{ Process each record here }
...
ADOTable1.Next;
end;
For information on navigating between table rows in dataset components, see "Navigating datasets".
The dataset provided by an ADO dataset component can be made available in an application using data-aware controls. Such datasets include the rows returned by a TADOTable component, the result set returned by a SELECT statement in a TADOQuery, and stored procedures that return a result set executed from a TADOStoredProc component.
To make these datasets available in data-aware controls:
For example, creating this relationship between ADO dataset component, datasource component, and data-aware control programmatically:
DBGrid1.DataSource := DataSource1; DataSource1.DataSet := ADOQuery1; ADOQuery1.Open;
ADO dataset components can connect to an ADO data store either collectively or individually.
When connecting dataset components collectively, set the Connection property of each dataset component to a TADOConnection component. Each dataset component then uses the connection established by that connection component.
ADODataSet1.Connection := ADOConnection1; ADODataSet2.Connection := ADOConnection1; ...
Among the advantages of connecting dataset components collectively are:
When connecting dataset components individually, set the ConnectionString property of each dataset component. The information needed to connect to the data store must be set for each dataset component. Each dataset component establishes its own connection to the data store, totally independent of any other dataset connection in the application.
ADODataSet1.ConnectionString := 'Provider=YourProvider;Password=SecretWord;' + 'User ID=JaneDoe;SERVER=PURGATORY;UID=JaneDoe;PWD=SecretWord;' + 'Initial Catalog=Employee'; ADODataSet2.ConnectionString := 'Provider=YourProvider;Password=SecretWord;' + 'User ID=JaneDoe;SERVER=PURGATORY;UID=JaneDoe;PWD=SecretWord;' + 'Initial Catalog=Employee'; ...
For more information on using a TADOConnection to connect to a data store see "Connecting to a data store using TADOConnection".
In addition to the means for navigating between records and modifying data shared by all ADO dataset components, there are a number of other properties and methods for operating on record sets.
The RecordSet property provides direct access to the ADO recordset object underlying the dataset component. Using this object, it is possible to access properties and call methods of the recordset object from an application. Use of RecordSet to directly access the underlying ADO recordset object requires a good working knowledge of ADO objects in general and the ADO recordset object in specific. It is not recommended that you use the recordset object directly unless familiar with recordset object operations. Consult the Microsoft Data Access SDK help for specific information on using ADO recordset objects.
Use the RecordSetState property to determine the current state of the dataset component. RecordSetState implements the State property of the ADO recordset object, and so reflects the current state of the underlying recordset object. The RecordSetState property will contain one of the values: stExecuting or stFetching. A value of stExecuting indicates the dataset component is currently in the process of executing a command. A value of stFetching indicates the dataset component is in the process of fetching rows from the associated table (or tables).
Use these values to perform actions dependent on the current state of the dataset. For example, a routine that updates data might check the RecordSetState property to see whether the dataset is active and not in the process of other activities such as connecting or fetching data.
ADO dataset components provide the ability to cache changes to the dataset and then either apply all of the changes as a batch operation or to cancel one or all of the changes. Batch updates can serve as a sort of transaction control, but at the dataset component level. (Ordinarily, transactions are handled as methods of the ADO connection component.)
Using the batch updates features of ADO dataset components is a matter of:
To open an ADO dataset in batch update mode, it must meet these criteria:
Before activating the dataset component, set the CursorType and LockType properties to the values indicated above. Assign a SELECT statement to the component's CommandText property (for TADODataSet) or the SQL property (for TADOQuery). For TADOStoredProc components, set the ProcedureName to the name of a stored procedure that returns a result set. These properties can be set at design-time through the Object Inspector or programmatically at runtime. The example below shows the preparation of a TADODataSet component for batch update mode.
with ADODataSet1 do begin CursorLocation := clUseServer; CursorType := ctKeyset; LockType := ltBatchOptimistic; CommandType := cmdText; CommandText := 'SELECT * FROM Employee'; Open; end;
After a dataset has been opened in batch update mode, all changes to the data are cached rather than applied directly to the base tables.
Determine the update status of a given row by making it current and then inspecting the RecordStatus property of the ADO data component. RecordStatus reflects the update status of the current row and only that row.
case ADOQuery1.RecordStatus of rsUnmodified: StatusBar1.Panels[0].Text := 'Unchanged record'; rsModified: StatusBar1.Panels[0].Text := 'Changed record'; rsDeleted: StatusBar1.Panels[0].Text := 'Deleted record'; rsNew: StatusBar1.Panels[0].Text := 'New record'; end;
Filter a recordset to show only those rows that belong to a group of rows with the same update status using the FilterGroup property. Set FilterGroup to the TFilterGroup constant that represents the update status of rows to display. A value of fgNone (the default value for this property) specifies that no filtering is applied and all rows are visible regardless of update status (except rows marked for deletion). The example below causes only pending batch update rows to be visible.
FilterGroup := fgPendingRecords; Filtered := True;
For the FilterGroup property to have an effect, the ADO dataset component's Filtered property must be set to True.
Apply pending data changes that have not yet been applied or canceled by calling the UpdateBatch method. Rows that have been changed and are applied have their changes put into the base tables on which the recordset is based. A cached row marked for deletion causes the corresponding base table row to be deleted. A record insertion (exists in the cache but not the base table) is added to the base table. Modified rows cause the columns in the corresponding rows in the base tables to be changed to the new column values in the cache.
Used alone with no parameter, UpdateBatch applies all pending updates. A TUpdateBatchOptions value can optionally be passed as the parameter for UpdateBatch. If any value except ubAffectAll is passed, only a subset of the pending changes are applied. Passing ubAffectAll is the same as passing no parameter at all and causes all pending updates to be applied. The example below applies only the currently active row to be applied:
ADODataSet1.UpdateBatch(ubAffectCurrent);
Cancel pending data changes that have not yet been canceled or applied by calling the CancelBatch method. Rows that have been changed and are canceled have their columns values reverted back to the values that existed prior to the last call to CancelBatch or UpdateBatch, if either has been called, or prior to the current pending batch of changes.
Used alone with no parameter, CancelBatch cancels all pending updates. A TUpdateBatchOptions value can optionally be passed as the parameter for CancelBatch. If any value except ubAffectAll is passed, only a subset of the pending changes are canceled. Passing ubAffectAll is the same as passing no parameter at all and causes all pending updates to be canceled. The example below cancels all pending changes:
ADODataSet1.Cancel;
The data retrieved via an ADO dataset component can be saved to a file for later retrieval on the same or a different computer. Save the data to a file using the SaveToFile method. Retrieve the data from file using the LoadFromFile method. The data is saved in one of two proprietary formats: ADTG and XML. Indicate which of these two formats to use for the save file with one of the TPersistFormat constants pfADTG or pfXML in the Format parameter of the SaveToFile method.
In the example below, the first procedure saves the dataset retrieved by the TADODataSet component ADODataSet1 to a file. The target file is an ADTG file named SaveFile, saved to a local drive. The second procedure loads this saved file into the TADODataSet component ADODataSet2.
procedure TForm1.SaveBtnClick(Sender: TObject);
begin
if (FileExists('c:\SaveFile')) then begin
DeleteFile('c:\SaveFile');
StatusBar1.Panels[0].Text := 'Save file deleted!';
end;
ADODataSet1.SaveToFile('c:\SaveFile', pfADTG);
end;
procedure TForm1.LoadBtnClick(Sender: TObject);
begin
if (FileExists('c:\SaveFile')) then
ADODataSet2.LoadFromFile('c:\SaveFile')
else
StatusBar1.Panels[0].Text := 'Save file does not exist!';
end;
The saving and loading dataset components need not be on the same form as above, in the same application, or even on the same computer. This allows for the briefcase-style transfer of data from one computer to another.
On calling the LoadFromFile method, the dataset component is automatically activated.
If the file specified in the FileName parameter of the SaveToFile method already exists, an EOleException exception is raised. Similarly, if the file specified in the FileName parameter of LoadFromFile does not exist, an EOleException exception is raised.
The two save file formats ADTG and XML are the only formats supported by ADO. Even so, both formats are not necessarily supported in all versions of ADO. Consult the ADO documentation for the actual version in use to determine what save file formats are supported.
Using parameters in commands and SQL statements executed as commands using ADO dataset components requires that you:
For each token in the SQL statement identified as a parameter, one TParameter component is automatically created and added to the dataset component's Parameters property (a TParameters array of TParameter components). At design-time, access the parameter components to set their values using the property editor for the Parameters property. To invoke the property editor, click the ellipsis button for the Parameters property in the Object Inspector.
At runtime, access parameter components to set or get their values using the Parameters property of the dataset component. Specify an index number with Parameters that is the ordinal position of a specific parameter in the SQL statement (relative to other parameters). This index is zero based, so the first parameter is referenced with an index of zero, the second with an index of one, and so on. Alternately, use the TParameters reference provided by the Parameters property and call its ParamByName method to refer to the parameter by its name.
{ reference the first parameter with an index }
ADOQuery1.Parameters[0].Value := 'telephone';
{ reference a parameter by its name }
ADOQuery1.Parameters.ParamByName('Amount').Value := 123;
In the example below, the following SQL statement is used in a TADOQuery component.
SELECT CustNo, Company, State FROM CUSTOMER WHERE (State = :StateParam)
This statement has one parameter: StateParam. The routine below closes the ADO query component, sets the value of the StateParam parameter through the Parameters property, and then reopens the ADO query component. The Parameters property requires a parameter be identified by a number representing the parameter's ordinal position in the statement, relative to other parameters. Parameters is zero-based, so the first parameter is identified with a Parameters property index of zero, the second with a one, and so on. As StateParam is the first parameter in the statement, an index of zero is used to identify it.
procedure TForm1.GetCaliforniaBtnClick(Sender: TObject);
begin
with ADOQuery1 do begin
Close;
Parameters[0].Value := 'CA';
Open;
end;
end;
The procedure below performs essentially the same purpose, but uses the TParameters.ParamByName method to set the parameter's value. The ParamByName method requires a parameter be identified by its name as used in the SQL statement (sans the colon).
procedure TForm1.GetFloridaBtnClick(Sender: TObject);
begin
with ADOQuery1 do begin
Close;
Parameters.ParamByName('StateParam').Value := 'FL';
Open;
end;
end;
The TADODataSet component provides Delphi applications the ability to access data from one or multiple tables in a database accessed via ADO. Tables accessed are specified using the CommandText property of the ADO dataset component, either by name or using an SQL statement.
The database is accessed using a data store connection established by the ADO dataset component using its ConnectionString property or through a separate TADOConnection component specified in the Connection property. See "Connecting to a data store using ADO dataset components" for more information on this.
Using data provided by a TADODataSet component in visual controls, navigating through the rows, and programmatically modifying the data is the same as for the rest of the ADO dataset components. See "Features common to all ADO dataset components" for more information on features common to all dataset components.
The TADODataSet component is capable of retrieving data from a single table using the name of a table. It can also retrieve data from one or multiple tables using a valid SQL statement. In either case, the table name or SQL statement is executed as a command.
Specify the name of a table or an SQL statement in the CommandText property and activate the component. At design-time, you can use the Command Text Editor to build the command. To invoke this editor, click the ellipsis button in the CommandText property in the Object Inspector. At runtime, assign a command to CommandText as a String.
ADODataSet1.CommandText := 'SELECT * FROM Customer';
Use the CommandType property to indicate the type of command being executed: cmdTable (or cmdTableDirect) if the command is a table name or cmdText for SQL statements. You can also specify cmdUnknown if the command type is not known at time or execution or you wish ADO to make a guess at the command type based on the contents of CommandText. At design-time, select the desired value for CommandType from the drop-down list in the Object Inspector. At runtime, assign a value of type TCommandType.
ADODataSet1.CommandType := cmdText;
Activate the TADODataSet by calling its Open method or by assigning a value of True to the Active property.
with ADODataSet1 do begin Connection := ADOConnection1; CommandType := cmdText; CommandText := 'SELECT * FROM Customer'; Open; end;
The TADOTable component provides Delphi applications the ability to access data from a single table in a database accessed via ADO. The table accessed is specified in the TableName property of the ADO table component.
The database is accessed using a data store connection established by the ADO table component using its ConnectionString property or through a separate TADOConnection component specified in the Connection property. See "Connecting to a data store using ADO dataset components" for more information on this.
Using data provided by a TADOTable component in visual controls, navigating through the rows, and programmatically modifying the data is the same as for the rest of the ADO dataset components. See "Features common to all ADO dataset components" for more information on features common to all dataset components.
Once a TADOTable component has a valid connection to a database, it can access tables contained in that database. Specify a single table of the database in the TableName property. When the ADO table component is activated, the table and its data become accessible through the TADOTable.
At design-time, if the TADOTable component has a valid data store connection, the property editor for the TableName property lists the names of available tables. Select one table from this list. At runtime, assign a String value containing a table name to the TableName property.
ADOTable1.TableName := 'Orders';
If a TADOConnection component is used to connect to the data store, you can use its GetTableNames method to retrieve a list of available tables. GetTableNames fills an already-existing string list object with the names of the tables available through the connection.
For example, the first routine below fills a TListBox component named ListBox1 with the names of tables available through the TADOConnection component ADOConnection1. The second routine is a handler for the OnDblClick event of ListBox1. In this event handler, the currently selected table name in ListBox1 is assigned to the TableName property of the TADOTable called ADOTable1. The ADO table component is then activated.
procedure TForm1.ListTablesButtonClick(Sender: TObject); begin ADOConnection1.GetTableNames(ListBox1.Items, False); end; procedure TForm1.ListBox1DblClick(Sender: TObject); begin with ADOTable1 do begin Close; TableName := (Sender as TListBox).Items[(Sender as TListBox).ItemIndex]; Open; end; end;
The TADOQuery component provides Delphi applications the ability to access data from one or multiple tables from an ADO database using SQL. Specify the SQL statement to use with the ADO query component in the SQL property. TADOQuery can either retrieve data using data manipulation language (DML) or create and delete metadata objects using data definition language (DDL). The SQL used in a TADOQuery component must be acceptable to the ADO driver in use. Delphi performs no evaluation of the SQL and does not execute it. The SQL statement is merely passed to the database back-end for execution. If the SQL statement produces a result set, it is passed from the database back-end through Delphi to the TADOQuery for use by the application.
The database is accessed using a data store connection established by the ADO query component using its ConnectionString property or through a separate TADOConnection component specified in the Connection property. See "Connecting to a data store using ADO dataset components" for more information on this.
Using data provided by a TADOQuery component in visual controls, navigating through the rows, and programmatically modifying the data is the same as for the rest of the ADO dataset components. See "Features common to all ADO dataset components" for more information on features common to all dataset components.
At design-time, invoke the property editor for the SQL property by clicking the ellipsis button in the Object Inspector. In the editor dialog, enter the SQL statement for the TADOQuery.
At runtime, assign a value to the SQL property. As is the case with the standard querying component, TQuery, the TADOQuery.SQL property is a string list object. Use properties and methods of the string list class to assign values to the SQL property.
In the example below, a SELECT statement is assigned to the SQL property of a TADOQuery component named ADOQuery1.
with ADOQuery1 do begin
Close;
with SQL do begin
Clear;
Add('SELECT Company, State');
Add('FROM CUSTOMER');
Add('WHERE State = ' + QuotedStr('HI'));
Add('ORDER BY Company');
end;
Open;
end;
A TADOQuery with a valid SQL statement in its SQL property can be executed in one of two ways. Which way is you use is predicated on the type of SQL statement the ADO query component is to execute.
If the SQL statement is one that returns a result set, the ADO query component should be activated by calling its Open method or by settings its Active property to True. Only SELECT statements return result sets, so a TADOQuery with a SELECT statement in its SQL property will always be activated using this approach.
ADOQuery1.SQL.Text := 'SELECT * FROM TrafficViolations'; ADOQuery1.Open;
Note that because methods cannot be called while designing an application in the Delphi IDE, only the Active property can be used to activate this kind of query at design-time. This is functionally the same as calling the Open method (at runtime).
Execute an SQL statement that does not return a result set by calling the ExecSQL method of the TADOQuery component. All SQL statements except SELECT fall into this category: INSERT, DELETE, UPDATE, CREATE INDEX, ALTER TABLE, and so on. A TADOQuery component with on of these SQL statements in its SQL property will always be activated using this approach.
ADOQuery1.SQL.Text := 'DELETE FROM TrafficViolations WHERE (TicketID = 1099)'; ADOQuery1.ExecSQL;
The TADOCommand component can be used to execute SQL statements like the one above that do not return result sets.
The TADOStoredProc component provides Delphi applications the ability to execute stored procedures in a database accessed through an ADO data store. The stored procedure executed is specified in the ProcedureName property of the ADO stored procedure component.
The database is accessed using a data store connection established by the stored procedure component using its ConnectionString property or through a separate TADOConnection component specified in the Connection property. See "Connecting to a data store using ADO dataset components" for more information on this.
Result sets retrieved by a TADOStoredProc component are made available to an application in the same manner as the standard, BDE-centric query component TStoredProc. Use the ADO stored procedure component for the DataSet property of a standard TDataSource component. The TDataSource then acts as the data conduit between the ADO stored procedure component and data-aware controls. See "Using visual data-aware controls" for more information on this.
Using data provided by a TADOStoredProc component in visual controls, navigating through the rows, and programmatically modifying the data is the same as for the rest of the ADO dataset components. See "Features common to all ADO dataset components" for more information on features common to all dataset components.
Once a TADOStoredProc component has a valid connection to a database, it can execute stored procedures contained in that database. Specify the name of a stored procedure from the database in the ProcedureName property. Activate the ADO stored procedure component using its Open method (if it returns a result set) or its ExecProc method (if it does not).
At design-time, if the TADOStoredProc component has a valid data store connection, the property editor for the ProcedureName property lists the names of available stored procedures. Select a stored procedure from this list. At runtime, assign a String value containing a stored procedure name to the ProcedureName property.
ADOStoredProc1.ProcedureName := 'DeleteEmployee';
If a TADOConnection component is used to connect to the data store, you can use its GetProcedureNames method to retrieve a list of available stored procedures. GetProcedureNames fills an already-existing string list object with the names of the stored procedures available through the connection.
For example, the first routine below fills a TListBox component named ListBox1 with the names of stored procedures available through the TADOConnection component ADOConnection1. The second routine is a handler for the OnDblClick event of ListBox1. In this event handler, the currently selected table name in ListBox1 is assigned to the ProcedureName property of the TADOStoredProc called ADOStoredProc1. The ADO stored procedure component is then executed using the ExecProc method.
procedure TForm1.ListProceduresButtonClick(Sender: TObject); begin ADOConnection1.GetProcedureNames(ListBox1.Items); end; procedure TForm1.ListBox1DblClick(Sender: TObject); begin with ADOStoredProc1 do begin ProcedureName := TListBox(Sender).Items[TListBox(Sender).ItemIndex]; ExecProc; end; end;
A TADOStoredProc with the name of an existing stored procedure in its ProcedureName property can be executed in one of two ways. Which way is you use is predicated on whether or not the stored procedure returns a result set.
If the stored procedure is one that returns a result set, the ADO stored procedure component should be activated by calling its Open method or by settings its Active property to True.
ADOStoredProc1.ProcedureName := 'ShowPurebreds'; ADOStoredProc1.ExecProc;
Note that as methods cannot be called while designing an application in the Delphi IDE, only the Active property can be used to activate this kind of stored procedure at design-time.
Execute a stored procedure that does not return a result set by calling the ExecProc method of the TADOStoredProc component. All SQL statements except SELECT fall into this category: INSERT, DELETE, UPDATE, CREATE INDEX, ALTER TABLE, and so on. A TADOQuery component with on of these SQL statements in its SQL property will always be activated using this approach.
ADOStoredProc1.ProcedureName := 'DeletePoodles'; ADOStoredProc1.ExecProc;
The TADOStoredProc component is capable of accommodating three types of parameters (not all of which may be supported by all database types). A parameter may be for input, for output, or for returning a result set. This section describes using stored procedure parameters in these three roles. It is possible for a parameter to serve two purposes, such as being both an input and an output parameter. This is merely a variation on the three basic roles. Dual use of a parameter like this is a matter of combining two of the functional approaches described here.
The direction or purpose of a parameter is defined in the stored procedure when it is created. This direction cannot later be changed by a front-end application. For instance, you cannot use Delphi code to turn an input parameter into an output parameter. The stored procedure would need to be dropped and recreated, giving the parameter a new role in the process. The direction of a particular parameter is indicated in the TParameter.Direction property, which can be read either at design-time in the Object Inspector or programmatically at runtime.
Use an input parameter to supply a value to a stored procedure before executing that stored procedure. Such values are typically used in the WHERE clause of a stored procedure's SQL statement to limit the number of table rows affected. Assign the parameter a value before activating the TADOStoredProc by calling its Open method or setting it Active property to True (for stored procedures that return a result set) or before executing the TADOStoredProc with its ExecProc method.
At design-time, access parameters through the Object Inspector. With focus in the cell for the Parameters property, click the ellipsis button. This invokes the parameters editor dialog. Enter a value of the appropriate type in the Value property.
At runtime, assign a value to the Value property of the TParameter component for the target parameter. Use the TParameter reference provided by the Parameters property of the TADOStoredProc.
with ADOStoredProc1 do begin Close; Parameters[1].Value := 1; Open; end;
Use an input parameter to return a single value from a stored procedure. While a result set might consist of multiple rows of multiple columns, this output parameter must be the equivalent of one row containing one column. If the stored procedure uses a SELECT statement to retrieve this value, an attempt to return multiple values results in an exception.
An output parameter only contains a value after the stored procedure has been activated or executed. Note that not all database systems support returning both a result set and output parameters. Check the documentation for the particular database system you are using to verify what it supports in this regard. If a database system supports both returning a result set and output parameter values, the TADOStoredProc can be activated using either its Open method (or Active property) or its ExecProc. If the database system does not support both, you can only use output parameters to retrieve values by calling the ExecProc method.
At design-time, access parameters through the Object Inspector. With focus in the cell for the Parameters property, click the ellipsis button. This invokes the parameters editor dialog. If the TADOStoredProc is activated using its Active property (the only way to activate it at design-time), inspect the output parameter's Value property to see the value returned.
At runtime, assign a value to the Value property of the TParameter component for the target parameter. Use the TParameter reference provided by the Parameters property of the TADOStoredProc. For example, the routine below returns a String value through the output parameter named @OutParam1.
with ADOStoredProc1 do begin
Close;
ShowMessage(VarToStr(Parameters.ParamByName('@OutParam1').Value));
ExecProc;
end;
Return value parameters need not be accessed directly. Instead, the result set returned through a return value parameter should be accessed as you would any dataset.
To make the result set available through visual data-aware controls, use a reference to the TADOStoredProc component as the value for the DataSet property of a TDataSource component. The TDataSource then acts as a conduit between the TADOStoredProc component and the data-aware controls. At design-time, this is done through the Object Inspector. In the DataSet property of the TDataSource, select the TADOStoredProc component from the drop-down list. At runtime, assign a reference to the TADOStoredProc to the DataSet property.
ADOStoredProc1.Close; DataSource1.DataSet := ADOStoredProc1; ADOStoredProc1.Open;
Alternately, the result set can be accessed and manipulated using navigation and editing properties and methods inherited from TDataSet. For information on modifying data through dataset components, see "Modifying data". For information on navigating between table rows in dataset components, see "Navigating datasets".
The set of ADO components provided in Delphi allows an application to execute commands. This section describes how to execute commands and what components to use to do so.
In the ADO environment, commands are textual representations of provider-specific action requests. Typically, they are Data Definition Language (DDL) and Data Manipulation Language (DML) SQL statements. The language used in commands is provider-specific, but usually compliant with the SQL-92 standard for the SQL language.
Commands can be executed from more than one Delphi component. Each command-capable component executes commands in a slightly different way, with varying strengths and weaknesses. Which component you should use for a particular command is predicated on the type of command and whether it returns a result set. In general, for commands that do not return a result set use the TADOCommand component (though the TADOQuery component can also execute these commands). For commands that do return a result set, either execute the command from a TADODataSet or use the command's statement in the SQL property of a TADOQuery.
The TADOCommand component provides the ability to execute commands, one command at a time. It is designed primarily for executing those commands that do not return result sets, such as Data Definition Language (DDL) SQL statements. Through an overloaded version of its Execute method, though, it is capable of returning a result set that can be used through an ADO dataset component.
Specify commands in the CommandText property. A command can optionally be described using the CommandType property. If no specific type is specified, the server is left to decide as best it can based on the command in CommandText. Commands used with an ADO command component can contain parameters for which values are substituted before execution of the command. Before a command can be executed, the ADO command component must have a valid connection to an ADO data store.
Specify the command to execute using the ADO command component in its CommandText property. At design-time, enter the command (an SQL statement, a table name, or the name of a procedure) in the CommandText property through the Object Inspector. At runtime, assign a String value containing the command to the CommandText property.
If desired, explicitly define the type of command being executed in the CommandType property. Among the constants for CommandType are: cmdText (used if the command is an SQL statement), cmdTable (if it is a table name), and cmdStoredProc (if the command is the name of a stored procedure). At design-time, select the appropriate command type from the list in the Object Inspector. At runtime, assign a value of type TCommandType to the CommandType property.
with ADOCommand1 do begin CommandText := 'AddEmployee'; CommandType := cmdStoredProc; ... end;
Before the command can be executed using an ADO command component, the TADOCommand must have a valid connection to a data store. See "Connecting to a data store using ADO dataset components" for more information this.
To execute the command call the Execute method of the ADO command component. For commands that do not require any parameters or execution options, call the simple overloaded version of Execute without any method parameters at all.
with ADOCommand1 do begin CommandText := 'UpdateInventory'; CommandType := cmdStoredProc; Execute; end;
For information on executing commands that return a result set, see "Retrieving result sets with commands".
After an attempt to execute a command has been initiated (with the Execute method of a TADOCommand component), it can be aborted by calling the Cancel method.
procedure TDataForm.ExecuteButtonClick(Sender: TObject); begin ADOCommand1.Execute; end; procedure TDataForm.CancelButtonClick(Sender: TObject); begin ADOCommand1.Cancel; end;
The Cancel method only has an effect if there is a command pending and the command was executed asynchronously (eoAsynchExecute is in the ExecuteOptions parameter of the Execute method). A command is said to be pending if the Execute method has been called and the command has not yet been completed or timed out. If a command has not been aborted or completed before the number of seconds specified in the CommandTimeout property have expired, the command times out. If a timeout period of other than the default 30 seconds is desired, set the CommandTimeout property prior to calling the Execute method.
Executing a command that returns a result set is exactly the same as for those that do not, except that a pre-existing ADO dataset component must represent the result set. The Execute method of TADOCommand returns an ADO recordset object. Assign this return value to the RecordSet property of an ADO dataset component such as a TADODataSet.
In the example below, the ADO record set produced by a call to the Execute method of a TADOCommand component (ADOCommand1) is assigned to the Recordset property of a TADODataSet component (ADODataSet1).
with ADOCommand1 do begin
CommandText := 'SELECT Company, State ' +
'FROM customer ' +
'WHERE State = :StateParam';
CommandType := cmdText;
Parameters.ParamByName('StateParam').Value := 'HI';
ADODataSet1.Recordset := Execute;
end;
As soon as this assignment is made to the ADO dataset component's Recordset property, the dataset component is activated (automatically) and the data is available. Use methods and properties of the dataset component to access the data programmatically. To make the data available using visual data-aware controls, use a TDataSource component as a conduit between the ADO dataset and the data-aware controls.
For information on executing commands that do not return a result set, see "Executing commands".
Executing a command that has parameters is exactly the same as for those that do not, except that values must be assigned to the parameters before the command is executed.
For each parameter in the command, one TParameter object is automatically added to the Parameters property of the TADOCommand component. At design-time use the Parameter Editor to access parameters, which is invoked by clicking the ellipsis button for the Parameters property in the Object Inspector. At runtime, use properties and methods of TParameter to set (or get) the values of each parameter.
with ADOCommand1 do begin
CommandText := 'INSERT INTO Talley ' +
'(Counter) ' +
'VALUES (:NewValueParam)';
CommandType := cmdText;
Parameters.ParamByName('NewValueParam').Value := 57;
Execute
end;
Access single TParameter objects in Parameters by a number representing the relative position (to each other) in the command using the Parameters property of the TADOCommand component. Reference the TParameter objects by their names using the TParameters.ParamByName method.
pubsweb@inprise.com
Copyright
© 1999, Inprise Corporation. All rights reserved.