Using Delphi Queries With ADO

Using Delphi Queries With ADO The TADOQuery component provides Delphi developers the ability to fetch data from one or multiple tables from an ADO database using SQL. These SQL statements can either be DDL (Data Definition Language) statements such as CREATE TABLE, ALTER INDEX, and so forth, or they can be DML (Data Manipulation Language) statements, such as SELECT, UPDATE, and DELETE. The most common statement, however, is the SELECT statement, which produces a view similar to that available using a Table component. Note: even though executing commands using the ADOQuery component is possible, the  ADOCommandcomponent is more appropriate for this purpose. It is most often used to execute DDL commands or to execute a stored procedure (even though you should use theTADOStoredProc  for such tasks) that does not return a result set. The SQL used in a ADOQuery component must be acceptable to the ADO driver in use. In other words you should be familiar with the SQL writing differences between, for example, MS Access and MS SQL. As when working with the ADOTable component, the data in a database is accessed using a data store connection established by the ADOQuery component using itsConnectionString  property or through a separate ADOConnection component specified in the  Connectionproperty. To make a Delphi form capable of retrieving the data from an Access database with the ADOQuery component simply drop all the related data-access and data-aware components on it and make a link as described in the previous chapters of this course. The data-access components: DataSource, ADOConnection along with ADOQuery (instead of the ADOTable) and one data-aware component like DBGrid is all we need.  As already explained, by using the Object Inspector set the link between those components as follows: DBGrid1.DataSource DataSource1 DataSource1.DataSet ADOQuery1 ADOQuery1.Connection ADOConnection1 //build the ConnectionString ADOConnection1.ConnectionString ... ADOConnection1.LoginPrompt False Doing a SQL query The TADOQuery component doesnt have a  TableNameproperty as the TADOTable does. TADOQuery has a property (TStrings) called  SQL  which is used to store the SQL statement. You can set the SQL propertys value with the Object Inspector at design time or through code at runtime. At design-time, invoke the property editor for the SQL property by clicking the ellipsis button in the Object Inspector.  Type the following SQL statement: SELECT * FROM Authors. The SQL statement can be executed in one of two ways, depending on the type of the statement. The Data Definition Language statements are generally executed with the  ExecSQL  method. For example to delete a specific record from a specific table you could write a DELETE DDL statement and run the query with the ExecSQL method.The (ordinary) SQL statements are executed by setting the  TADOQuery.Active  property to  True  or by calling theOpen  method (essentialy the same). This approach is similar to retrieving a table data with the TADOTable component. At run-time, the SQL statement in the SQL property can be used as any StringList object: with  ADOQuery1  do begin  Close; SQL.Clear; SQL.Add:SELECT * FROM Authors SQL.Add:ORDER BY authorname DESC Open;   end; The above code, at run-time, closes the dataset, empties the SQL string in the SQL property, assigns a new SQL command and activates the dataset by calling the Open method. Note that obviously creating a persistent list of field objects for an ADOQuery component does not make sense. The next time you call the Open method the SQL can be so different that the whole set of filed names (and types) may change. Of course, this is not the case if we are using ADOQuery to fetch the rows from just one table with the constant set of fields - and the resulting set depends on the WHERE part of the SQL statement. Dynamic Queries One of the great properties of the TADOQuery components is the  Params  property. A parameterized query is one that permits flexible row/column selection using a parameter in the WHERE clause of a SQL statement. The Params property allows replacable parameters in the predefined SQL statement. A parameter is a placeholder for a value in the WHERE clause, defined just before the query is opened. To specify a parameter in a query, use a colon (:) preceding a parameter name.  At design-time use the Object Inspector to set the SQL property as follows: ADOQuery1.SQL : SELECT * FROM Applications WHERE type    :apptype When you close the SQL editor window open the Parameters window by clicking the ellipsis button in the Object Inspector. The parameter in the preceding SQL statement is namedapptype. We can set the values of the parameters in the Params collection at design time via the Parameters dialog box, but most of the time we will be changing the parameters at runtime. The Parameters dialog can be used to specify the datatypes and default values of parameters used in a query. At run-time, the parameters can be changed and the query re-executed to refresh the data. In order to execute a parameterized query, it is necessary to supply a value for each parameter prior to the execution of the query. To modify the parameter value, we use either the Params property or ParamByName method. For example, given the SQL statement as above, at run-time we could use the following code: with ADOQuery1 do begin Close; SQL.Clear; SQL.Add(SELECT * FROM Applications WHERE type :apptype); ParamByName(apptype).Value:multimedia; Open; end; As like when working with the ADOTable component the ADOQuery returns a set or records from a table (or two or more). Navigating through a dataset is done with the same set of methods as described in the Behind data in datasets chapter. Navigating and Editing the Query In general ADOQuery component should not be used when editing takes place. The SQL based queries are mostly used for reporting purposes. If your query returns a result set, it is sometimes possible to edit the returned dataset. The result set must contain records from a single table and it must not use any SQL aggregate functions.  Editing  of a dataset returned by the ADOQuery is the same as editing the ADOTAbles dataset. Example To see some ADOQuery action well code a small example. Lets make a query that can be used to fetch the rows from various tables in a database. To show the list of all the tables in a database we can use the  GetTableNamesmethod of the  ADOConnection  component. The GetTableNames in the OnCreate event of the form fills the ComboBox with the table names and the Button is used to close the query and to recreate it to retrieve the records from a picked table. The () event handlers should look like: procedure TForm1.FormCreate(Sender: TObject); begin ADOConnection1.GetTableNames(ComboBox1.Items); end; procedure TForm1.Button1Click(Sender: TObject); var tblname : string; begin if ComboBox1.ItemIndex then Exit; tblname : ComboBox1.Items[ComboBox1.ItemIndex]; with ADOQuery1 do begin Close; SQL.Text : SELECT * FROM tblname; Open; end; end; Note that all this can be done by using the ADOTable and its TableName property.