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 the query to effectively use the query, you must be familiar with the standard SQL language and the server used to limit and expand the SQL-92, but also be familiar with BDE. 9.1.1 Querying the desktop database As a desktop developer, the concept of dealing with the table, records, and fields is known, and can be 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. Functional, query is complicated than filtration, which is mainly reflected in: You can query a few tables at the same time. You can let the query results contain only partial fields, 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 Querying the 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 You can query which databases use the TQuery component to query the following databases: First, PARADOX or DBASE, which is implemented through 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 be accessed.
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 to set the SQL property 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 is 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 not be parameters, fix the field name and value in the SQL statement, for example, the following SQL statement is hard-write: SELECT * from custom000 WHERE 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, must be enclosed with quotes, 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 where Number is a parameter, and it must be colon. 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 the SQL statement at the design period in the design period, to specify the SQL statement, you can click the omitial button on the SQL attribute edge in the object viewer, pop up a string list editor, as shown in Figure 9.1.
Figure 9.1 Specifying the SQL statement SQL statement in the design period 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 the SQL statement in the runtime, three ways to specify the SQL attribute, one is to set the SQL attribute directly, and the other is to call the LoadFromFile to read the SQL statement from the file, or get 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 SQL attributes directly in the running period: with customerQuery dobeginclose; with sql dobeginclear; add ('selection * 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, I 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 SQL statements from your 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, this is to call TSTRINGS ASSIGN function. Assign will automatically empty 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 queries, parameters must be added to 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.
Program example: With Query1 DoBeginClose; Unprepare; ParamByName ( 'Name') AsString:. = 'China'; ParamByName ( 'Capital') AsString: = 'Beijing'; ParamByName ( 'Population') AsInteger:.. = '120000 '; Prepare; open; end; 9.5.1 Providing parameters in design period To provide parameters in design period, click the Optional button on the edge of the PARAMS attribute to pop up the editor shown in Figure 9.2. Figure 9.2 Setting the params attribute in the design period If there is no parameters 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 attributes of TPARAM are: DataType properties The data types used to specify parameters, and its initial value is always ftunknown, and must set the data type of each parameter. 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 in runtime access parameters in the runtime, there are three ways: one is to access 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) This row code Accesses the Capital parameters through the parambyname function: query1.parambyname ('Capital'). Asstring: = edit1.text; The following line code is accessed by parame parameters through the params property: query1.params [0] .sstring: = Edit1.text; below this line of code passed the paramvalues of the TParams object Attributes to access three parameters simultaneously: query1.params.paramvalues ['country; capital; continent']: = varRayof ([edit1.text, edit2.text, edit3.text]); 9.5.3 get from another Data set The DataSource property of the parameter 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 a field that matches the parameter name in this data source, then Use the value of this field as 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 is also the following components on the data module:. A TTable component called CustomersTable 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 application, you will see the effect as shown in Figure 9.3: Figure 9.3 Getting parameters from another data set This brief explanation below. 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 Search When the SQL statement is specified and the parameters are provided, the query can be executed. 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 example is 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 the Open is called in the TRY, and Execsql calls in the Except section, so that even if the Open call fails, you can execute it to ExecSql. The program is as follows: tryquery2.open; ExcePt on E: Exception do if not (e is enores) THEN RAISE; END; previously mentioned, it is best to call prepare before executing query, although this is not necessary. Prepare can improve the performance of the application in advance. The program is as follows: CustomerQuery.close; if not (CustomerQuery.prepared) thencustomerQuery.prepare; CustomerQuery.Open; The above program first calls Close, then check the prepared property, if this property returns true, indicating that you are ready, if this property returns false It is not ready to call prepare at this time. 9.7 Excellence inquiry so-called heterogeneous queries, simultaneously query a few different databases. The types of these databases can be different.
