Multi-layer database development 9: querying databases

Chapter 9 query Databases
This chapter describes how to use the tquery component to query databases and how to use SQL statements to retrieve, insert, update, and delete data. SQL is a relational database language that complies with industrial standards. It can be used for remote server-based databases, such as Sybase, Oracle, Interbase, and Microsoft SQL Server, it can also be used for local databases such as paradox, dBase, Foxpro, access, and ODBC-compliant databases.
9.1 use query effectively
To use queries effectively, you must be familiar with the standard SQL language and the limits and extensions of the SQL-92 on the server you are using, and be familiar with BDE.
9.1.1 query a desktop database
As a desktop developer, he must understand the concepts of tables, records, and fields, and skillfully uses the tTable component to access each record and field in the dataset.
You can also use the tTable range and filtering function to select a part of records in the dataset. The former is used to select a continuous record, and the values of these records are within a specific range; the latter is used to select non-consecutive records that meet specific conditions.
The so-called query is very similar to filtering. The difference is that the query requires the tquery component and the SQL attribute, and sometimes the Params attribute. In terms of functions, queries are more complex and powerful than filters, mainly reflected in:
. You can query several tables at the same time.
. Only some fields are included in the query results, and all fields are returned after filtering.
A query can also contain parameters, which are called parameterized queries. The so-called parameter is similar to a variable. Its actual value is assigned by BDE before the SQL statement is executed. The benefit of parameterized query is that you do not need to modify the SQL statement. You only need to modify the parameter value to execute different query functions.
In most cases, the tquery component is used to select some fields and records in the dataset. However, you can also use SQL statements to update, insert, and delete records, this is a difference from the tTable component.
9.1.2 query a remote database
To query remote databases, you must be familiar with SQL statements and server restrictions and extensions on standard SQL.
The SQL attribute of the tquery component is used to specify the SQL statement to be executed, and the Params attribute is used to provide parameters. The functions of the tquery component are not limited to SQL statements and parameters. It is also an interface between BDE and applications.
Applications can manipulate SQL statements and parameters through attributes and methods of the tquery component. The tquery component finally communicates with the remote server through SQL links. The remote server returns the query result to BDE, and then the BDE returns the result to the application.
9.2 which databases can be queried
You can use the tquery component to query the following databases:
First, Paradox or DBASE is implemented through BDE's built-in local SQL. Local SQL is a subset of the SQL-92 standard that supports most DML and DDL.
The second is the local InterBase Server, which is implemented through the Interbase engine.
Third, remote databases such as Oracle, Sybase, MS-SQL server, Informix, DB2, and InterBase, however, the corresponding SQL links driver must be installed. Different servers have different restrictions and extensions for standard SQL. Before querying a remote database, you must check its related documents.
Delphi 4 also supports heterogeneous queries, that is, you can query several different types of databases at the same time.
9.3 general steps for using the tquery component
The first step is to place a tquery component on the data module and set its databasename attribute to specify the database to be accessed. For paradox and DBASE, The databasename attribute can be set to BDE aliases such as dbemos, defaultdd, and iblocal, or a custom alias or the path of the table.
For SQL tables, the databasename attribute can only be set as a BDE alias. If the application uses the tdatabase component to connect to the database, the databasename attribute can also be set as an alias for the application.
Step 2: Set the SQL attribute to specify the SQL statement to be executed. If necessary, you can also set the Params attribute to set parameters for the SQL statement.
Step 3: place the tdatasource component on the data module and set its dataset attribute to specify the tquery component. Put the TDBGrid component on the form and set its datasource attribute to specify the tdatasource component.
Step 4: Execute SQL statements. There are two ways to execute SQL statements. One is to set the active attribute to true during the design period, and the SQL statement is automatically executed when the program starts. Another method is to call open or execsql to execute SQL statements at runtime. If you want to return the query results, call open. If you do not need to return the query results, call execsql. Before calling open or execsql, it is best to call prepare to notify the server to prepare.
The query results returned by executing SQL statements are actually a subset of records that meet specific conditions in the dataset. Only records meeting specific conditions are displayed in the Database Grid.
9.4 specify the SQL statement to be executed
You can set the SQL attribute to specify the SQL statement to be executed. During the design period, as long as the active attribute is set to true, the SQL statement is automatically executed. During the runtime, you must first call prepare to notify the server to prepare and then call open or execsql to execute SQL function statements.
9.4.1 Overview
SQL attributes are a typical tstrings object. The SQL attribute generally contains only one complete SQL statement, but can be divided into several lines for writing. The tquery component automatically combines several lines of strings into one SQL statement.
The advantage of dividing an SQL statement into several lines is that the logic structure of the SQL statement is clear, which is conducive to future maintenance and debugging. Therefore, the Select and where sections of SQL statements are generally not in the same row.
SQL statements can fix field names and values in SQL statements without parameters. For example, the following SQL statement is hard-coded:
Select * from customer where custno = 1231
Note: If you want to query a local database, if the field name in the SQL statement contains spaces or other special symbols, it must be enclosed by quotation marks. The table name and dot must be added before the SQL statement.
If parameters are used, the query is much more flexible, and the application does not need to rewrite the SQL statement itself. As long as the parameter value is modified, the SQL statement can execute different query functions. Before executing an SQL statement, the tquery component automatically replaces the actual value with the parameters in the SQL statement, even if the prepare function is not explicitly called.
The following SQL statement is a typical parameterized query:
Select * from customer where custno =: Number
Number is a parameter and must be preceded by a colon. During runtime, the application must provide the value of the Number Parameter. Each time an SQL statement is executed, the value of the number parameter can be different.
The parameter value is provided through the Params attribute of tquery.
9.4.2 specify the SQL statement during the design period
During the design period, to specify an SQL statement, you can click the ellipsis next to the SQL attribute in the object observer to bring up a string List editor, as shown in Figure 9.1.
Figure 9.1 specify an SQL statement during design
SQL statements can be divided into several lines for writing, but the same word cannot be separated. Generally, the SQL attribute can contain only one complete SQL statement, but some servers allow several SQL statements to be executed at the same time. In this case, you can enter multiple SQL statements.
If you use the client/server or enterprise version of Delphi 4, you can also use the sqlbuilder utility to create an SQL statement. To use SQL builder, right-click the tquery component and choose "SQL builder" from the shortcut menu.
9.4.3 specify an SQL statement at runtime
During the runtime, you must specify three methods for SQL attributes. One is to directly set SQL attributes, the other is to call loadfromfile to read SQL statements from the file, or obtain SQL statements from another tstrings object.
Before directly setting SQL attributes, you must call the close function. If an SQL statement already exists in the SQL attribute, clear the original SQL statement by calling clear.
The following code demonstrates how to directly set SQL attributes at runtime:
With customerquery do
Begin
Close;
With SQL do
Begin
Clear;
Add ('select * From customer ');
Add ('where Company = 'light diver ');
End;
Open;
End;
Sometimes, you may want to modify or add a line based on the original SQL statement. At this time, you cannot call clear to clear the original SQL statement. For example:
Customerquery. SQL [1]: = 'where Company = "Light Diver "';
You can also call loadfromfile to obtain the SQL statement from the file. This is mainly because the tstrings object supports file operations. Loadfromfile automatically clears the original SQL statement.
The following code calls loadfromfile:
Customerquery. close;
Customerquery. SQL. loadfromfile ('C:/orders.txt ');
Customerquery. open;
You can also obtain SQL statements from another tstrings object, which requires calling the assign function of tstrings. Assign automatically clears the original SQL statement.
The following code is an example of calling assign:
Customerquery. close;
Customerquery. SQL. Assign (memo1.lines );
Customerquery. open;
9.5 Parameters
To use parameterized query, you must add parameters to the SQL statement, for example:
Insert into country (name, capital, population)
Values (: name,: Capital,: Population)
Among them, name, capital, and population are three parameters.
Before executing the preceding SQL statement, the application should call the prepare function to notify BDE and the server to allocate resources in advance to speed up the query. The program example is as follows:
With query1 do
Begin
Close;
Unprepare;
Parambyname ('name'). asstring: = 'China ';
Parambyname ('capital '). asstring: = 'beijing ';
Parambyname ('population'). asinteger: = '20140901 ';
Prepare;
Open;
End;
9.5.1 provide parameters during the design period
To provide parameters during the design period, click the ellipsis next to the Params attribute to bring up the editor shown in 9.2.
Figure 9.2 set Params attributes during design
If the SQL statement does not contain any parameters, the editor shown in Figure 9.2 is blank. The toolbar of this editor is always forbidden, which means that only parameters can be added to SQL statements.
Select a parameter (tparam object) to set its attributes in the object observer or create an event handle. The main attributes of tparam are:
The datatype attribute is used to specify the Data Type of a parameter. Its initial value is always ftunknown and the Data Type of each parameter must be set.
The paramtype attribute is used to specify the usage type of a parameter. Its initial value is ptunknown.
The value attribute is used to specify the value of a parameter. Of course, you can also give the parameter value at runtime.
9.5.2 provide parameters during runtime
There are three methods to access parameters during runtime:
First, access parameters by name through the parambyname function.
Second, access parameters by serial number through the Params attribute.
Third, access parameters by name through the paramvalues attribute of the tparams object.
Assume that an SQL statement has three parameters:
Insert into "country. DB"
(Name, capital, continent)
Values (: name,: Capital,: continent)
The following code uses the parambyname function to access the capital parameter: query1.parambyname ('capital '). asstring: = edit1.text;
The following code uses the Params attribute to access the name parameter:
Query1.params [0]. asstring: = edit1.text;
The following code uses the paramvalues attribute of the tparams object to simultaneously access three parameters:
Query1.params. paramvalues ['country; capital; continent']: = vararrayof ([edit1.text, edit2.text, edit3.text]);
9.5.3 obtain parameters from another Dataset
The datasource attribute of the tquery component is used to specify a data source (tdatasource component). If the application neither assigns a value to the parameter during design nor runtime, it searches for a field that matches the parameter name in the data source and uses the value of this field as the parameter value.
Assume that a data module is called linkmodule and the preceding tquery component is ordersquery. Its SQL statement is as follows:
Select custno, orderno, saledate
From orders where custno =: custno
In addition, the data module has the following components:
. A tTable component is called mermerstable, and its tablename attribute is set to customer. DB.
. A tdatasource component is called orderssource, and its dataset attribute is set to ordersquery.
A tdatasource component is called mermerssource, and its dataset attribute is set to customerstable.
The datasource attribute of. ordersquery is also set to mermerssource.
Assume that an application has a form called external query, which has two TDBGrid components. Their datasource attributes specify customerssource and orderssource respectively.
If you compile and run this application, you will see the effect shown in 9.3:
Figure 9.3 obtain parameters from another Dataset
Here is a simple explanation of 9.3. If no value is assigned to the custno parameter in the SQL statement, ordersquery tries to find matching fields from the dataset specified by customerssource. Because customerssource obtains data from customer. DB and there is exactly a custno field in customer. dB, the value of the custno parameter is the value of the custno field. If you select another record in the grid that displays customer. dB, the value of the custno parameter changes accordingly.
9.6 Query
When an SQL statement is specified and parameters are provided, the query can be executed. If this is the first time you execute a query, it is best to call prepare to notify the BDE or server to prepare, which can speed up the query.
Query can be executed either during the design period or during the runtime.
To perform a query during the design period, set the active attribute to true. However, the SQL statements that can be executed during the design period are limited to select statements and cannot be insert, update, or delete statements.
To execute a query at runtime, you can call open or execsql functions. Open is suitable for executing select statements, while execsql is suitable for executing insert, update, or delete statements without returning results.
Before calling open or execsql, you must call close. The program example is as follows:
Customerquery. close;
Customerquery. open;
If you cannot determine whether to return the query results during programming, you can use try... the commit T structure writes the two processes into it. Generally, open is called in the try part, while execsql is called in the commit t part. In this way, execsql can be executed even if the open call fails. The program example is as follows:
Try
Query2.open;
Except
On E: exception do
If not (E is enoresultset) then raise;
End;
As mentioned previously, it is recommended to call prepare before executing a query, although this is not necessary. Precalling prepare can improve application performance. The program example is as follows:
Customerquery. close;
If not (customerquery. Prepared) then
Customerquery. Prepare;
Customerquery. open;
The preceding program first calls close and then checks the prepared attribute. If this attribute returns true, it indicates that it is ready. If this attribute returns false, it indicates that it is not ready. In this case, prepare is called.
9.7 heterogeneous Query
Heterogeneous queries are used to query several different databases at the same time. The types of these databases can be different. For example, you can query the Oracle database, Sybase Database, and local DBASE tables at the same time. When a program executes a heterogeneous query, BDE uses local SQL to analyze and process the query, rather than using specific SQL syntax related to the server.
The general steps for creating a heterogeneous query are as follows:
Step 1: place a tquery component on the form or data module to leave the databasename attribute empty.
Step 2: create a separate BDE alias for each database to be queried.
Step 3: Set the SQL attribute to specify the SQL statement to be executed. In an SQL statement, aliases and colons must be added before the table name and are enclosed in double quotation marks. Add the table name and dot before the field name. For example:
Select customer. custno, orders. orderno
From "oracle1: customer"
Join "sybase1: orders"
On (customer. custno = orders. custno)
Where (customer. custno = 1503)
Step 4: set parameters provided by the Params attribute.
Step 5: Call prepare to notify the BDE or server to prepare, and then call open or execsql to execute the query. If you explicitly use the tdatabase component to connect to the database and set its databasename attribute to define an alias for the application, you can use a special alias to replace the BDE alias in SQL statements.
9.8 query results
By default, the query results are read-only. Applications can use data controls to display query results, but users cannot edit data. How can users edit data?
To enable users to edit data, you must set the requestlive attribute of the tquery component to true. However, setting the requestlive attribute to true does not guarantee that the query results can be modified, because it also depends on whether the query uses local SQL or server-related SQL.
Local SQL is used for queries like paradox, dBase, and heterogeneous queries, while remote servers use server-related SQL. Even if the requestlive attribute is set to true and the local database is queried, BDE returns the read-only query result because the SELECT statement syntax is inappropriate.
Therefore, you must access the canmodify Attribute before editing data. The query result can be edited only when this attribute returns true.