Chapter 9 Query Database This chapter describes how to query the database with a TQUERY component, how to retrieve, insert, update, and delete data through SQL statements. SQL is a relational database language that meets industry standards, which can be used for remote server-based databases such as Sybase, Oracle, Interbase, and Microsoft SQL Server, can also be used for local databases such as PARADOX, DBASE, FOXPRO and Access, and in line with ODBC. database.
9.1 Effectively use inquiry
To effectively use queries, you must be familiar with the standard SQL language and the server used for SQL-92, while you must be familiar with BDE.
9.1.1 Query Desktop Database
As a desktop developer, the concept of decking form, record, and fields is understood, but also skilled using the TTable component to access each record in the data set and each field.
You can also use the TTable range and filtering function to select a part of the record in the data set, the former is used to select a continuous record, the value of these records is within a specific range; the latter is used to select a discontinuous record, these records meet specific conditions of.
The so-called query is very similar to filtration, different, querying to use the TQuery component and SQL properties, sometimes it is possible to use the params property. From functional, query is complicated than filtration, which is mainly reflected in:
. You can query a few tables at the same time
You can allow only partial fields in the query result, and filtering will return all fields.
The query can also be used as a parameter, and it is called a parameterized query. The so-called parameter, similar to the variable, its actual value is assigned by BDE before executing the SQL statement. The benefits of parameterized queries are that there is no need to modify the SQL statement. You can perform different query functions as long as the value of the parameter is modified.
In most cases, using the TQuery component is to select a part of the fields and records in the data set, but can also use the SQL statement to implement update, insert, and delete records, which is a difference with the TTable component.
9.1.2 Query Remote Database
To query the remote database, you must be familiar with the SQL statement and the server's 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 property is used to provide parameters. The function of the TQUERY component is not limited to SQL statements and parameters, which is also an interface between BDE and the application.
The application can manipulate the SQL statements and parameters through the properties and methods of the TQUERY component. The TQUERY component is ultimately communicating with the remote server via SQL Links, and the remote server returns the query results to the BDE, and then returns to the application by BDE.
9.2 What database can be queried?
Use the TQuery component to query the following databases:
One is Paradox or DBase, which is implemented by the Local SQL built in BDE. Local SQL is a subset of SQL-92 standards that support most DML and DDL.
The second is Local Interbase Server, which is implemented through the InterBase engine.
The third is a remote database, such as Oracle, Sybase, MS-SQL Server, Informix, DB2, and InterBase, but the corresponding SQL Links driver must be installed. Different servers have different restrictions and extensions for standard SQL. To query the remote database, it is necessary to check the relevant documents.
Delphi 4 also supports heterogeneous inquiry, that is, several different types of databases can be queried.
9.3 General steps using the TQUERY component
The first step is to put a TQUERY component on the data module and set its DatabaseName property to specify the database to access. For PARADOX and DBASE, the DatabaseName property can be set to BDE alias such as dbemos, defaultdd, iblocal, etc., or a custom alias or a path where the table is located. For SQL tables, the DatabaseName property can only be set to BDE alias. If the application uses the TDatabase component to connect to the database, the DatabaseName property can also be set as an app-specific alias.
The second step is to set the SQL attribute to specify the SQL statement to be executed, and if necessary, set the params attribute to set the parameters for the SQL statement.
The third step is to put the TDataSource component on the data module and set its DataSet property to specify the TQUERY component. Place the TDBGRID member on the form and set its DataSource property to specify the TDataSource component.
The fourth step is to execute the SQL statement. To perform a SQL statement, there are two ways. First, the SQL statement will be automatically executed when the Active property is set to TRUE in the design period. Another way is to execute the SQL statement in the run call Open or EXECSQL. If you want to return query results, call Open, if you do not need to return query results, call EXECSQL. Before calling Open or ExecSql, it is best to call the Prepare to notify the server to prepare.
The result of the query returned by executing the SQL statement is actually a subset composed of records that meet specific conditions, and only records that conform to 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. In the design period, as long as the Active property is set to TRUE, the SQL statement is automatically executed. In the runtime, you must first call the Prepare notification server ready, then call the Open or ExecSql to perform the SQL function statement.
9.4.1 Overview
SQL properties are a typical TStrings object. SQL properties typically contain only one complete SQL statement, but can be divided into several lines, the TQuery component automatically combines a few rows of strings into a SQL statement.
The advantage of dividing the SQL statement into several lines is that the logical structure of the SQL statement is relatively clear, which is conducive to future maintenance and debugging. Therefore, the SELECT section of the SQL statement and the WHERE portion are generally not on the same line.
The SQL statement can be used without parameters, fixing the field name and value in the SQL statement, for example, the following SQL statement is hard-write:
Select * from customer where need custno = 1231
Note: If you want to query the local database, if the field name in the SQL statement contains spaces or other special symbols, you must include quotation marks, and add the form name and small dots in front.
If you use the parameters, the query is much flexible. The application does not need to overwrite the SQL statement itself. If you modify the value of the parameter, you can make the SQL statement execute different query functions. Before performing the SQL statement, the TQuery component automatically replaces the parameters in the SQL statement, even if there is no explicit calls.
The following SQL statement is a typical parameterized query:
Select * from customer where custom =: Number
Among them, Number is a parameter, and it must have a number in front. At runtime, the application must provide the value of the Number parameter. Each time the SQL statement is executed, the value of the Number parameter can be different.
The value of the parameter is provided by the Params property of TQuery.
9.4.2 Specify SQL statement in design period
At the design period, you want to specify the SQL statement, you can pop up a string list editor in the SQL attribute edge, pop up a string list editor, as shown in Figure 9.1. Figure 9.1 Specifying a SQL statement at the design period
SQL statements can be divided into several lines, but the same word cannot be separated. In general, the SQL attribute can only contain a complete SQL statement, but some servers allow simultaneous SQL statements, in which case multiple SQL statements can be entered.
If you use the Client / Server version or Enterprise version of Delphi 4, you can also use the SQLBuilder this utility to create a SQL statement. To use SQL Builder, right-click on the TQUERY component and select the "SQL Builder" command in the pop-up menu.
9.4.3 Specify SQL statement in the runtime
In the runtime, there are three ways to specify the SQL attribute. One is to set the SQL property directly, and the other is to call the LoadFromFile to read the SQL statement from the file, or obtain the SQL statement from another TSTRINGS object.
First call the Close function before setting the SQL property directly. If there is already a SQL statement in the SQL attribute, you have to call CLEAR to clear the original SQL statement.
The following code demonstrates how to set the SQL attribute directly in the runtime:
With CustomerQuery Do
Begin
CLOSE;
With sql do
Begin
Clear;
Add ('SELECT * from customer');
Add ('where company =' light diver ');
END;
Open;
END;
Sometimes, I may want to modify or add a line on the basis of the original SQL statement. At this time, it can't call Clear to clear the original SQL statement, for example:
CustomerQuery.sql [1]: = 'where company = "light diver";
You can also call LoadFromFile to get the SQL statement from the file, which is mainly because the TStrings object supports file operations. LoadFromFile automatically clears the original SQL statement.
The following code is an example of calling loadFromfile:
CustomerQuery.Close;
CustomerQuery.sql.LoadFromfile ('c: /orders.txt');
CustomerQuery.open;
You can also get the SQL statement from another TSTRINGS object, which is to call the TStrings' ASSIGN function. Assign will automatically empty the original SQL statement.
The following code is to call Assign:
CustomerQuery.Close;
CustomerQuery.sql.assign (Memo1.Lines);
CustomerQuery.open;
9.5 parameters
To use a parameterized query, you must add parameters in the SQL statement, for example:
Insert Into Country (Name, Capital, Population)
VALUES (: Name,: Capital,: Population)
Where Name, Capital and Populations are three parameters.
Before performing the above SQL statement, the application should call the Prepare function to inform BDE and the server to pre-allocate good resources in advance to speed up the query speed. The program is now as follows:
With query1 do
Begin
CLOSE;
Unprepare;
PARAMBYNAME ('Name'). Asstring: = 'China'; parambylename ('Capital'). Asstring: = 'beijing';
PARAMBYNAME ('population'). Asinteger: = '120000';
Prepare;
Open;
END;
9.5.1 Provides parameters during the design period
To provide parameters on the design period, click the I omit button on the edge of the params property, pop up the editor shown in Figure 9.2.
Figure 9.2 Setting params attributes during design
If no parameters are included in the SQL statement, the editor shown in Figure 9.2 is blank. The toolbar of this editor is always prohibited, which means only the parameters can be added in the SQL statement.
Select one of the parameters (TPARAM objects) to set its properties in the object viewer, or establish an event handle. The main properties of TPARAM are:
The DataType property is used to specify the data type of the parameter. Its initial value is always ftunknown, and the data type of each parameter must be set.
The paraMType property is used to specify the type of use of the parameter, and its initial value is also PTunkNown.
The value attribute is used to give the value of the parameter. Of course, the value of the parameter can also be given in the running period.
9.5.2 Providing parameters during the course of operation
To access parameters in the running period, there are three ways:
The first is to access the parameters by the parambyname function.
The second is to access parameters according to the serial number through the params attribute.
The third is to access parameters by name by the paramvalues property of the TParams object.
Suppose a SQL statement has three parameters:
INSERT INTO "country.db"
(Name, Capital, Continent)
VALUES (: Name,: Capital,: Continent)
The following line code accesses the Capital parameters through the parambyname function: query1.parombyname ('capital'). Asstring: = Edit1.Text;
Below this line of code accesses the Name parameters through the Params property:
Query1.Params [0] .sstring: = Edit1.Text;
Below this line of code accesses three parameters at the same time through the paramvalues property of the TParams object:
Query1.Params.Paramvalues ['Country; Capital; CONTINENT']: = Vararrayof ([Edit1.Text, Edit2.Text, Edit3.Text]);
9.5.3 Getting parameters from another data set
The DataSource property of the TQuery component is used to specify a data source (TDataSource component), if the application is not assigned to the parameter in the design period, it looks for the fields that match the parameter name in this data source, and then use The value of this field is the value of the parameter.
Suppose a data module is called LinkModule, there is a TQuery component called ORDERSQUERY, its SQL statement is as follows:
Select Custno, Orderno, Saledate
From Orders Where Custno =: Custno
In addition, there are the following components on the data module:
A TTable component is called CustomersTable, and its TableName property is set to Customer.db.
A TDataSource component is ORDERSSOURCE, and its DataSet property is set to Ordersquery.
A TDataSource component is called CustomersSource, and its DataSet property is set to CustomersTable. .Ordersquery's DataSource property is also set to CustomersSource.
Suppose the application has a form called LinkedQuery, there are two TDBGRID components on the form, and their DataSource properties specify CustomersSource and OrdersSource, respectively.
If you compile and run this app, you will see the effect as shown in Figure 9.3:
Figure 9.3 Getting parameters from another data set
Here is a brief explanation of Figure 9.3. If there is no value to the SQL statement: CustNO parameter, ORDERSQUERY will try to find the matching field from the data set specified by CustomersSource. Since CustomersSource is obtained from Customer.db, it is just a CustNo field in Customer.db, so: The value of the CustNO parameter is the value of the CustNo field. If you select another record in the raster displaying Customer.db, it will result in the value of the CUSTNO parameter to change.
9.6 executive query
The query can be executed when the SQL statement is specified and the parameters are provided. If it is the first execution query, it is best to call the prepare to notify the BDE or the server ready to speed up the query.
You can also perform queries during the design period or execute inquiry during the running period.
To perform a query at the design period, just set the Active property to true. However, the SQL statement that can be performed in the design period is limited to the SELECT statement, and cannot be insert, update, or delete statement.
To perform a query at the runtime, you can call the Open or EXECSQL function, where Open is suitable for executing the SELECT statement, and the execSQL is suitable for executing the INSERT, UPDATE, or DELETE statement, the latter does not return the result.
Before calling Open or ExecSql, first call the Close. The program is now as follows:
CustomerQuery.Close;
CustomerQuery.open;
If you can't determine if you want to return query results when programming, you can use the TRY ... Except structure to write these two processes, usually call in the TRY part, and ExecSql calls in the Except section, so that even Open call failed Also executable to ExecSql. The program is now as follows:
Try
Query2.open;
Except
ON E: Exception DO
IF NOT (E Is Enoresultset).
END;
As mentioned earlier, it is best to call Prepare before performing query, although this is not necessary. Prepare can improve the performance of the application in advance. The program is now as follows:
CustomerQuery.Close;
IF not (CustomerQuery.prepared) THEN
CustomerQuery.prepare;
CustomerQuery.open;
The above program first calls the Close, then check the prepared property, if this property returns true, it is already ready, if this property returns false, it is not ready, at this time to call prepare.
9.7 Excellency Inquiry
The so-called heterogeneous query is to query a few different databases. The types of these databases can be different. For example, you can query the Oracle database, Sybase database, and local DBase tables. When the program performs heterogeneous queries, BDE analyzes and processes this query through local SQL, not with specific SQL syntax related to the server. The general step of establishing a heterogeneous query is this:
The first step is placed on the form or data module, allowing the DatabaseName property empty.
Step 2, establish a separate BDE alias for each database to be queried.
Step 3, set the SQL property to specify the SQL statement to be executed. In the SQL statement, you should add the name and colon before the name of the table and enclose the double quotes. The field name is before adding table and small dots. E.g:
Select Customer.custno, ORDERS.Orderno
From "Oracle1: Customer"
Join "Sybase1: ORDERS"
Customer.custno = Orders.custno
WHERE (Customer.custno = 1503)
Step 4, set the parames attribute to provide parameters.
Step 5, call the prepare to inform the BDE or the server ready, then call the Open or ExecSql to perform the query. If explicitly use the TDatabase component to connect the database, and its DatabaseName property defines the app-specific alias, you can replace the BDE alias in the SQL statement.
9.8 check the results
By default, the results of the query are read-only. The application can display query results with data controls, but users cannot edit data. How can I make the user edit the data?
To enable users to edit data, set the REQUESTLIVE attribute of the TQuery component to True. However, set the RequestLive property to True and cannot guarantee the results of the query must be modified, as this also depends on the query use Local SQL or the server-related SQL.
Like PARADOX or DBASE and heterogeneous queries are used to use local SQL, and query remote servers use SQL-related SQL. Even if the RequestLive property is set to True, and query is a local database, BDE will return a read-only query result due to the local database.
Therefore, first access the canmodify attribute before editing the data. Only when this property returns True, the result of the query is editable.
