This chapter describes the TQuery dataset component which enables you to use SQL statements to access data. It assumes you are familiar with the general discussion of datasets and data sources in "Understanding datasets."
A query component encapsulates an SQL statement that is used in a client application to retrieve, insert, update, and delete data from one or more database tables. SQL is an industry-standard relational database language that is used by most remote, server-based databases, such as Sybase, Oracle, InterBase, and Microsoft SQL Server. Query components can be used with remote database servers (if your version of Delphi includes SQL links), with Paradox, dBASE, FoxPro, and Access, and with ODBC-compliant databases.
To use the query component effectively, you must be familiar with
If you are an experienced desktop database developer moving to server-based applications, see "Queries for desktop developers" for an introduction to queries before reading the remainder of this chapter. If you are new to SQL, you may want to purchase one of the many fine third party books that cover SQL in-depth. One of the best is Understanding the New SQL: A Complete Guide, by Jim Melton and Alan R. Simpson, Morgan Kaufmann Publishers.
If you are an experienced database server developer, but are new to building Delphi clients, then you are already familiar with SQL and your server, but you may be unfamiliar with the BDE. See "Queries for server developers" for an introduction to queries and the BDE before reading the remainder of this chapter.
As a desktop developer you are already familiar with the basic table, record, and field paradigm used by Delphi and the BDE. You feel very comfortable using a TTable component to gain access to every field in every data record in a dataset. You know that when you set a table's TableName property, you specify the database table to access.
Chances are you have also used a TTable's range methods and filter property to limit the number of records available at any given time in your applications. Applying a range temporarily limits data access to a block of contiguously indexed records that fall within prescribed boundary conditions, such as returning all records for employees whose last names are greater than or equal to "Jones" and less than or equal to "Smith". Setting a filter temporarily restricts data access to a set of records that is usually noncontiguous and that meets filter criteria, such as returning only those customer records that have a California mailing address.
A query behaves in many ways very much like a table filter, except that you use the query component's SQL property (and sometimes the Params property) to identify the records in a dataset to retrieve, insert, delete, or update. In some ways a query is even more powerful than a filter because it lets you access
Queries can be verbatim, or they can contain replaceable parameters. Queries that use parameters are called parameterized queries. When you use parameterized queries, the actual values assigned to the parameters are inserted into the query by the BDE before you execute, or run, the query. Using parameterized queries is very flexible, because you can change a user's view of and access to data on the fly at runtime without having to alter the SQL statement.
Most often you use queries to select the data that a user should see in your application, just as you do when you use a table component. Queries, however, can also perform update, insert, and delete operations instead of retrieving records for display. When you use a query to perform insert, update, and delete operations, the query ordinarily does not return records for viewing. In this way a query differs from a table.
To learn more about using the SQL property to write an SQL statement, see "Specifying the SQL statement to execute". To learn more about using parameters in your SQL statements, see "Setting parameters". To learn about executing a query, see "Executing a query".
As a server developer you are already familiar with SQL and with the capabilities of your database server. To you a query is the SQL statement you use to access data. You know how to use and manipulate this statement and how to use optional parameters with it.
The SQL statement and its parameters are the most important parts of a query component. The query component's SQL property is used to provide the SQL statement to use for data access, and the component's Params property is an optional array of parameters to bind into the query. However, a query component is much more than an SQL statement and its parameters. A query component is also the interface between your client application and the BDE.
A client application uses the properties and methods of a query component to manipulate an SQL statement and its parameters, to specify the database to query, to prepare and unprepare queries with parameters, and to execute the query. A query component's methods call the BDE, which, in turn, processes your query requests, and communicates with the database server, usually through an SQL Links driver. The server passes a result set, if appropriate, back to the BDE, and the BDE returns it to your application through the query component.
When you work with a query component, you should be aware that some of the terminology used to describe BDE features can be confusing at first because it has very different meanings than what you are familiar with as an SQL database programmer. For example, the BDE commonly uses the term "alias" to refer to a shorthand name for the path to the database server. The BDE alias is stored in a configuration file, and is set in the query component's DatabaseName property. (You can still use table aliases, or "table correlation names," in your SQL statements.)
Similarly, the BDE help documentation, available online in \Borland\Common Files\BDE\BDE32.HLP, often refers to queries that use parameters as "parameterized queries" where you are more likely to think of SQL statements that use bound variables or parameter bindings.
Note: This chapter uses BDE terminology because you will encounter it throughout Borland's documentation. Whenever confusion may result from using BDE terms, explanations are provided.
To learn more about using the SQL property to write an SQL statement, see "Specifying the SQL statement to execute". To learn more about using parameters in your SQL statements, see "Setting parameters". To learn about preparing a query, see "Preparing a query", and to learn more about executing a query, see "Executing a query".
A TQuery component can access data in:
Delphi also supports heterogeneous queries against more than one server or table type (for example, data from an Oracle table and a Paradox table). When you create a heterogeneous query, the BDE uses Local SQL to process the query. For more information, see "Creating heterogeneous queries".
To use a query component in an application, follow these steps at design time:
To execute a query for the first time at runtime, follow these steps:
After you execute a query for the first time, then as long as you do not modify the SQL statement, an application can repeatedly close and reopen or re-execute a query without preparing it again. For more information about reusing a query, see "Executing a query".
Use the SQL property to specify the SQL query statement to execute. At design time a query is prepared and executed automatically when you set the query component's Active property to true. At runtime, a query is prepared with a call to Prepare, and executed when the application calls the component's Open or ExecSQL methods.
The SQL property is a TStrings object, which is an array of text strings and a set of properties, events, and methods that manipulate them. The strings in SQL are automatically concatenated to produce the SQL statement to execute. You can provide a statement in as few or as many separate strings as you desire. One advantage to using a series of strings is that you can divide the SQL statement into logical units (for example, putting the WHERE clause for a SELECT statement into its own string), so that it is easier to modify and debug a query.
The SQL statement can be a query that contains hard-coded field names and values, or it can be a parameterized query that contains replaceable parameters that represent field values that must be bound into the statement before it is executed. For example, this statement is hard-coded:
SELECT * FROM Customer WHERE CustNo = 1231
Hard-coded statements are useful when applications execute exact, known queries each time they run. At design time or runtime you can easily replace one hard-code query with another hard-coded or parameterized query as needed. Whenever the SQL property is changed the query is automatically closed and unprepared.
Note: In queries made against the BDE engine using local SQL, when column names in a query contain spaces or special characters, the column name must be enclosed in quotes and must be preceded by a table reference and a period. For example, BIOLIFE."Species Name". For information about valid column names, see your server's documentation.
A parameterized query contains one or more placeholder parameters, application variables that stand in for comparison values such as those found in the WHERE clause of a SELECT statement. Using parameterized queries enables you to change the value without rewriting the application. Parameter values must be bound into the SQL statement before it is executed for the first time. Query components do this automatically for you even if you do not explicitly call the Prepare method before executing a query.
This statement is a parameterized query:
SELECT * FROM Customer WHERE CustNo = :Number
The variable Number, indicated by the leading colon, is a parameter that fills in for a comparison value that must be provided at runtime and that may vary each time the statement is executed. The actual value for Number is provided in the query component's Params property.
Tip: It is a good programming practice to provide variable names for parameters that correspond to the actual name of the column with which it is associated. For example, if a column name is "Number," then its corresponding parameter would be ":Number". Using matching names ensures that if a query uses its DataSource property to provide values for parameters, it can match the variable name to valid field names.
You can specify the SQL property at design time using the String List editor. To invoke the String List editor for the SQL property:
You can enter an SQL statement in as many or as few lines as you want. Entering a statement on multiple lines, however, makes it easier to read, change, and debug. Choose OK to assign the text you enter to the SQL property.
Normally, the SQL property can contain only one complete SQL statement at a time, although these statements can be as complex as necessary (for example, a SELECT statement with a WHERE clause that uses several nested logical operators such as AND and OR). Some servers also support "batch" syntax that permits multiple statements; if your server supports such syntax, you can enter multiple statements in the SQL property.
Note: With some versions of Delphi, you can also use the SQL Builder to construct a query based on a visible representation of tables and fields in a database. To use the SQL Builder, select a query component, right-click it to invoke the context menu, and choose Graphical Query Editor. To learn how to use the SQL Builder, open it and use its online help.
There are three ways to set the SQL property at runtime. An application can set the SQL property directly, it can call the SQL property's LoadFromFile method to read an SQL statement from a file, or an SQL statement in a string list object can be assigned to the SQL property.
To directly set the SQL property at runtime,
The following code illustrates building an entire SQL statement from nothing.
with CustomerQuery do begin
Close; { close the query if it's active }
with SQL do begin
Clear; { delete the current SQL statement, if any }
Add('SELECT * FROM Customer'); { add first line of SQL... }
Add('WHERE Company = "Sight Diver"'); { ... and second line }
end;
Open; { activate the query }
end;
The code below demonstrates modifying only a single line in an existing SQL statement. In this case, the WHERE clause already exists on the second line of the statement. It is referenced via the SQL property using an index of 1.
CustomerQuery.SQL[1] := 'WHERE Company = "Kauai Dive Shoppe"';
Note: If a query uses parameters, you should also set their initial values and call the Prepare method before opening or executing a query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.
You can also use the LoadFromFile method to assign an SQL statement in a text file to the SQL property. The LoadFromFile method automatically clears the current contents of the SQL property before loading the new statement from file. For example:
CustomerQuery.Close;
CustomerQuery.SQL.LoadFromFile('c:\orders.txt');
CustomerQuery.Open;
Note: If the SQL statement contained in the file is a parameterized query, set the initial values for the parameters and call Prepare before opening or executing the query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.
You can also use the Assign method of the SQL property to copy the contents of a string list object into the SQL property. The Assign method automatically clears the current contents of the SQL property before copying the new statement. For example, copying an SQL statement from a TMemo component:
CustomerQuery.Close; CustomerQuery.SQL.Assign(Memo1.Lines); CustomerQuery.Open;
Note: If the SQL statement is a parameterized query, set the initial values for the parameters and call Prepare before opening or executing the query. Explicitly calling Prepare is most useful if the same SQL statement is used repeatedly; otherwise it is called automatically by the query component.
A parameterized SQL statement contains parameters, or variables, the values of which can be varied at design time or runtime. Parameters can replace data values, such as those used in a WHERE clause for comparisons, that appear in an SQL statement. Ordinarily, parameters stand in for data values passed to the statement. For example, in the following INSERT statement, values to insert are passed as parameters:
INSERT INTO Country (Name, Capital, Population) VALUES (:Name, :Capital, :Population)
In this SQL statement, :name, :capital, and :population are placeholders for actual values supplied to the statement at runtime by your application. Before a parameterized query is executed for the first time, your application should call the Prepare method to bind the current values for the parameters to the SQL statement. Binding means that the BDE and the server allocate resources for the statement and its parameters that improve the execution speed of the query.
with Query1 do begin
Close;
Unprepare;
ParamByName('Name').AsString := 'Belize';
ParamByName('Capital').AsString := 'Belmopan';
ParamByName('Population').AsInteger := '240000';
Prepare;
Open;
end;
At design time, parameters in the SQL statement appear in the parameter collection editor. To access the TParam objects for the parameters, invoke the parameter collection editor, select a parameter, and access the TParam properties in the Object Inspector. If the SQL statement does not contain any parameters, no TParam objects are listed in the collection editor. You can only add parameters by writing them in the SQL statement.
For queries that do not already contain parameters (the SQL property is empty or the existing SQL statement has no parameters), the list of parameters in the collection editor dialog is empty. If parameters are already defined for a query, then the parameter editor lists all existing parameters.
Note: The TQuery component shares the TParam object and its collection editor with a number of different components. While the right-click context menu of the collection editor always contains the Add and Delete options, they are never enabled for TQuery parameters. The only way to add or delete TQuery parameters is in the SQL statement itself.
As each parameter in the collection editor is selected, the Object Inspector displays the properties and events for that parameter. Set the values for parameter properties and methods in the Object Inspector.
The DataType property lists the BDE data type for the parameter selected in the editing dialog. Initially the type will be ftUnknown. You must set a data type for each parameter. In general, BDE data types conform to server data types. For specific BDE-to-server data type mappings, see the BDE help in \Borland\Common Files\BDE\BDE32.HLP.
The ParamType property lists the type of parameter selected in the editing dialog. Initially the type will be ptUnknown. You must set a type for each parameter.
Use the Value property to specify a value for the selected parameter at design-time. This is not mandatory when parameter values are supplied at runtime. In these cases, lease Value blank.
To create parameters at runtime, you can use the
For all of the examples below, assume the SQL property contains the SQL statement below. All three parameters used are of data type ftString.
INSERT INTO "COUNTRY.DB" (Name, Capital, Continent) VALUES (:Name, :Capital, :Continent)
The following code uses ParamByName to assign the text of an edit box to the Capital parameter:
Query1.ParamByName('Capital').AsString := Edit1.Text;
The same code can be rewritten using the Params property, using an index of 1 (the Capital parameter is the second parameter in the SQL statement):
Query1.Params[1].AsString := Edit1.Text;
The command line below sets all three parameters at once, using the Params.ParamValues property:
Query1.Params.ParamValues['Country;Capital;Continent'] := VarArrayOf([Edit1.Text, Edit2.Text, Edit3.Text]);
If parameter values for a parameterized query are not bound at design time or specified at runtime, the query component attempts to supply values for them based on its DataSource property. DataSource specifies a different table or query component that the query component can search for field names that match the names of unbound parameters. This search dataset must be created and populated before you create the query component that uses it. If matches are found in the search dataset, the query component binds the parameter values to the values of the fields in the current record pointed to by the data source.
You can create a simple application to understand how to use the DataSource property to link a query in a master-detail form. Suppose the data module for this application is called, LinkModule, and that it contains a query component called OrdersQuery that has the following SQL property:
SELECT CustNo, OrderNo, SaleDate FROM Orders WHERE CustNo = :CustNo
The LinkModule data module also contains:
Suppose, too, that this application has a form, named LinkedQuery that contains two data grids, a Customers Table grid linked to CustomersSource, and an Order Query grid linked to OrdersSource.
The following figure illustrates how this application appears at design time.
Note: If you build this application, create the table component and its data source before creating the query component.
If you compile this application, at runtime the :CustNo parameter in the SQL statement for OrdersQuery is not assigned a value, so OrdersQuery tries to match the parameter by name against a column in the table pointed to by CustomersSource. CustomersSource gets its data from CustomersTable, which, in turn, derives its data from the CUSTOMER.DB table. Because CUSTOMER.DB contains a column called "CustNo," the value from the CustNo field in the current record of the CustomersTable dataset is assigned to the :CustNo parameter for the OrdersQuery SQL statement. The grids are linked in a master-detail relationship. At runtime, each time you select a different record in the Customers Table grid, the OrdersQuery SELECT statement executes to retrieve all orders based on the current customer number.
After you specify an SQL statement in the SQL property and set any parameters for the query, you can execute the query. When a query is executed, the BDE receives and processes SQL statements from your application. If the query is against local tables (dBASE and Paradox), the BDE SQL engine processes the SQL statement and, for a SELECT query, returns data to the application. If the query is against an SQL server table and the TQuery.RequestLive property is set to False, the SQL statement is not processed or interpreted by the BDE, but is passed directly to the database system. If the RequestLive property is set to True, the SQL statement will need to abide by local SQL standards as the BDE will need to use it for conveying data changes to the table.
Note: Before you execute a query for the first time, you may want to call the Prepare method to improve query performance. Prepare initializes the BDE and the database server, each of which allocates system resources for the query. For more information about preparing a query, see "Preparing a query".
The following sections describe executing both static and dynamic SQL statements at design time and at runtime.
To execute a query at design time, set its Active property to true in the Object Inspector.
The results of the query, if any, are displayed in any data-aware controls associated with the query component.
Note: The Active property can be used only with queries that returns a result set, such as by the SELECT statement.
To execute a query at runtime, use one of the following methods:
Note: If you do not know at design time whether a query will return a result set at runtime, code both types of query execution statements in a try..except block. Put a call to the Open method in the try clause. This allows you to suppress the error message that would occur due to using an activate method not applicable to the type of SQL statement used. Check the type of exception that occurs. If it is other than an ENoResult exception, the exception occurred for another reason and must be processed. This works because an action query will be executed when the query is activated with the Open method, but an exception occurs in addition to that.
try Query2.Open; except on E: Exception do if not (E is ENoResultSet) then raise; end;
To execute a query that returns a result set (a query that uses a SELECT statement), follow these steps:
CustomerQuery.Close;
CustomerQuery.Open; { query returns a result set }
For information on navigating within a result set, see "Disabling bi-directional cursors". For information on editing and updating a result set, see "Working with result sets".
To execute a query that does not return a result set (a query that has an SQL statement such as INSERT, UPDATE, or DELETE), call ExecSQL to execute the query.
CustomerQuery.ExecSQL; { query does not return a result set }
Preparing a query is an optional step that precedes query execution. Preparing a query submits the SQL statement and its parameters, if any, to the BDE for parsing, resource allocation, and optimization. The BDE, in turn, notifies the database server to prepare for the query. The server, too, may allocate resources for the query. These operations improve query performance, making your application faster, especially when working with updatable queries.
An application can prepare a query by calling the Prepare method. If you do not prepare a query before executing it, then Delphi automatically prepares it for you each time you call Open or ExecSQL. Even though Delphi prepares queries for you, it is better programming practice to prepare a query explicitly. That way your code is self-documenting, and your intentions are clear. For example:
CustomerQuery.Close; if not (CustomerQuery.Prepared) then CustomerQuery.Prepare; CustomerQuery.Open;
This example checks the query component's Prepared property to determine if a query is already prepared. Prepared is a Boolean value that is true if a query is already prepared. If the query is not already prepared, the example calls the Prepare method before calling Open.
The UnPrepare method sets the Prepared property to false. UnPrepare
CustomerQuery.UnPrepare;
Note: When you change the text of the SQL property for a query, the query component automatically closes and unprepares the query.
Delphi supports heterogeneous queries, that is, queries made against tables in more than one database. A heterogeneous query may join tables on different servers, and even different types of servers. For example, a heterogeneous query might involve a table in a Oracle database, a table in a Sybase database, and a local dBASE table. When you execute a heterogeneous query, the BDE parses and processes the query using Local SQL. Because BDE uses Local SQL, extended, server-specific SQL syntax is not supported.
To perform a heterogeneous query, follow these steps:
For example, suppose you define an alias called Oracle1 for an Oracle database that has a CUSTOMER table, and Sybase1 for a Sybase database that has an ORDERS table. A simple query against these two tables would be:
SELECT Customer.CustNo, Orders.OrderNo FROM ":Oracle1:CUSTOMER" JOIN ":Sybase1:ORDERS" ON (Customer.CustNo = Orders.CustNo) WHERE (Customer.CustNo = 1503)
As an alternative to using a BDE alias to specify the database in a heterogeneous query, you can use a TDatabase component. Configure the TDatabase as normal to point to the database, set the TDatabase.DatabaseName to an arbitrary but unique value, and then use that value in the SQL statement instead of a BDE alias name.
Following are steps you can take to improve query execution speed:
The UniDirectional property determines whether or not BDE bi-directional cursors are enabled for a query. When a query returns a result set, it also receives a cursor, or pointer to the first record in that result set. The record pointed to by the cursor is the currently active record. The current record is the one whose field values are displayed in data-aware components associated with the result set's data source.
UniDirectional is false by default, meaning that the cursor for a result set can navigate both forward and backward through its records. Bi-directional cursor support requires some additional processing overhead, and can slow some queries. To improve query performance, you may be able to set UniDirectional to true, restricting a cursor to forward movement through a result set.
If you do not need to be able to navigate backward through a result set, you can set UniDirectional to true for a query. Set UniDirectional before preparing and executing a query. The following code illustrates setting UniDirectional prior to preparing and executing a query:
if not (CustomerQuery.Prepared) then begin
CustomerQuery.UniDirectional := True;
CustomerQuery.Prepare;
end;
CustomerQuery.Open; { returns a result set with a one-way cursor }
By default, the result set returned by a query is read-only. Your application can display field values from the result set in data-aware controls, but users cannot edit those values. To enable editing of a result set, your application must request a "live" result set.
To request a result set that users can edit in data-aware controls, set a query component's RequestLive property to true. Setting RequestLive to true does not guarantee a live result set, but the BDE attempts to honor the request whenever possible. There are some restrictions on live result set requests, depending on whether or not a query uses the local SQL parser or a server's SQL parser. Heterogeneous joins and queries executed against Paradox or dBASE are parsed by the BDE using local SQL. Queries against a remote database server are parsed by the server.
If an application requests and receives a live result set, the CanModify property of the query component is set to true.
If an application requests a live result set, but the SELECT statement syntax does not allow it, the BDE returns either
For queries that use the local SQL parser, the BDE offers expanded support for updatable, live result sets in both single table and multi-table queries. The local SQL parser is used when a query is made against one or more dBASE or Paradox tables, or one or more remote server tables when those table names in the query are preceded by a BDE database alias. The following sections describe the restrictions that must be met to return a live result set for local SQL.
A live result set for a query against a single table or view is returned if the query does not contain any of the following:
For queries that use passthrough SQL, which includes all queries made solely against remote database servers, live result sets are restricted to the standards defined by SQL-92 and any additional, server-imposed restrictions.
A live result set for a query against a single table or view is returned if the query does not contain any of the following:
If a query returns a live result set, you may not be able to update the result set directly if the result set contains linked fields or you switch indexes before attempting an update. If these conditions exist, you may be able to treat the result set as a read-only result set, and update it accordingly.
Applications can update data returned in a read-only result set if they are using cached updates. To update a read-only result set associated with a query component:
For more information about using cached updates, see Chapter 25, "Working with cached updates."
pubsweb@inprise.com
Copyright © 1999, Inprise Corporation. All rights reserved.