17th Chapter-sql Programming (i) (3)

17.3.3 How to get the active data through the tquery part

When we introduce the ttable part in the previous chapters, we know that the data obtained from the database through the ttable part is active, i.e. the user can edit the data directly through the data browsing part. Two types of data are available through the Tquery part:

Data for "activity"

This data is the same as the data obtained through the ttable part, the user can edit and modify the data through the data browsing part, and when the Post method is invoked or when the focus leaves the current data browsing widget, the user's modification of the data is automatically written back to the database, see Chapter Fourth " The application and programming of data browsing parts.

Inactive data (read-only data)

The data is not modified by the user through the data browsing part. By default, the query result data obtained through the tquery part is read-only, and in order to obtain "active" data, the Tquery part's Requestlive property value must be set in the application to true, but not in any case ( You can get "active" data by setting the Requestlive true), and to get the "active" data, BDE to be able to return "active" data in addition to true for the Tquery part's Requestlive property. The corresponding SQL command statement also satisfies the syntax rules in Appendix C and the following constraints:

The tquery part gets the constraint of the "active" Query result data:

When querying a table in a Paradox or dBASE database:

A query can only relate to a single table

The ORDER BY command cannot be included in an SQL statement

Aggregation operator sum or AVG cannot be included in SQL statements

Cannot have a calculated field in the field list after select

The SELECT statement where section can only contain comparison operations between field values and constants, these comparison operators are: like,>,<,>=,<=, and the comparison operations can have and and intersection operations: and and OR.

When querying database tables in the database server through SQL statements:

A query can only relate to a single table

The ORDER BY command cannot be included in an SQL statement

Aggregation operator sum or AVG operation cannot be included in SQL statements

In addition, if you are querying a table in the Sybase database, there can be only one index in the table being queried.

If the tquery part is required to return an "active" Query result dataset in the application, but the SQL command statement does not meet the constraint, BDE can only return a read-only dataset for SQL queries on the local database. For SQL queries in the database server, only the wrong code can be returned. When the tquery part returns an "active" Query result dataset, the value of its Canmodify property is set to true.

Table 17.1 tquery Part returns the type of the query result data

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

Requestlive Property Value Canmodify Property value query result type

────────────────────────────────

False false read-only data

True (SQL statement satisfies constraint) True "Active" data

True (SQL statement does not meet constraints) False read-only data

━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━━

When the Tquery part returns a read-only query result dataset, and the user wants to modify the read-only dataset, this is typically done by adding a tquery part Query2 in the application (assuming the name of the tquery part that gets the read-only result is Query1), Setting Modify statement update in QUERY2 modifies the data in Query1, which enables changes to read-only data.

17.4 Programming of Dynamic SQL statements

In section 17.3, we've covered dynamic SQL statements (also known as parameterized SQL statements). In this section, we focus on assigning parameters to dynamic SQL statements in which you can change parameters that are used in the actual program design, so that in the application, you are able to flexibly With SQL statements. The writing, execution, and so on of dynamic SQL statements are the same as the SQL statements described in section 17.3.

Parameters in a dynamic SQL statement, we can assign values to it in two ways:

1. Assigning values to parameters using the parameter editor (Parameter Editor)

Select the Tquery part, right-click, and select Define Parameters to open the parameter editor.

For example, in the SQL attribute of the tquery part, we set the following SQL statement:

Setect * from Customer Where custno=:number;

The DatabaseName property of the Tquery is Dbdemos, where number is a parameter variable. We can assign a value to the parameter number, select the data type of the parameter in the Datetype combo box to integer integers, assign a value to parameter number in the Value edit box, or click Null The Value check box assigns a null value to the parameter number. After assigning a value to the parameter, click the OK button so that the SQL query in the tquery part is ready and the parameter value is assigned to the corresponding parameter in the dynamic SQL statement, when the Tquery part's Active property is set to true. The query result is displayed in the data browsing part connected with the Tquery part, and the parameter is assigned by the parameter editor, which lacks the flexibility and is less used in the practical application, in the practical application, the program designer wants to assign value to the parameter in a more flexible and convenient way. That's another way we're going to introduce:

2. In the process of running, by the program for the parameters assigned value

There are three ways to assign a value to a parameter in this way:

① sets the params property value of the Tquery part to the parameter assignment, based on the order in which the parameters appear in the SQL statement.

② directly based on the names of the parameters in the SQL statement, call the Parambyname method to assign values to each parameter.

③ sets the Tquery part's DataSource property to another data source, which assigns the corresponding parameter to the field value in the other data source that matches the parameter name in the SQL statement of the current Tquery part.

These three methods are described in detail in the following three sections