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

17.4.1 use the Params property to assign a value to a parameter

The tquery part has a params property that is not available at design time, is available during program execution, and is dynamically built, and Delphi automatically builds an array params when writing dynamic SQL statements for tquery parts. The array params begins with a 0 subscript, which in turn corresponds to the parameters in the dynamic SQL statement, that is, the first parameter in the dynamic SQL statement corresponds to the params[0], the second parameter corresponds to params[1], and so on.

For example: A tquery part Query1, the dynamic SQL statement that we write for it is:

Insert into Customer (custno,name,country)

Values (: Custno,:name,: Country)

For the parameters in this dynamic SQL statement, we can assign values to the parameters using the params property of the Tquery part:

Query1.params[0]. Asstring: = "1988";

QUERY1.PARAMS[1]. Asstring: = "Lichtenstein";

QUERY1.PARAMS[2]. Asstring: = "USA";

The above statement assigns "1988" to the parameter: Cuse_no, "Lichtenstein" is assigned to the parameter: Name, "USA" is assigned to the parameter: Country.

17.4.2 Use the Parambyname method to assign a value to a parameter

Parambyname is a function that uses parameters in dynamic SQL statements as arguments to call the Parambyname function, so that you can assign values to them, and you must know the name of the dynamic SQL statement parameter by using this assignment method.

For example, in the example in section 17.4.1, you can also assign a value to a parameter in the following ways:

Query1.parambyname (' Custno '). Asstring: = "1988";

Query1.parambyname (' Name '). Asstring: = "Lichtenstein";

Query1.parambyname (' Country '). Asstring: = "USA";

This method can also be used to assign values to each parameter, and is more intuitive.

17.4.3 Use the DataSource property to assign a value to a parameter

The common characteristics of the above two methods are: When we assign a value to each parameter, we know the specific parameter value of each parameter. In a specific application, some parameter values are often not determined, such as parameter values from another query results, for this case, Delphi provides the use of the DataSource property for dynamic SQL statements are still not assigned parameters, Delphi will automatically check the Tquery The Datasource property of the part, if the property value is set for the Datasource property (the value of the property is the name of another Tdatasource part), Delphi will compare the unassigned parameters with the fields in the Tdatasource part, Delphi Assigns the corresponding field value to the parameter that matches it. Using this method can also achieve the so-called connection query, when we learn to use the ttable part, we will create the main-detail database application, the connection query created with the Tquery part is similar to the main-detail application.

For example: In the application shown in Figure 17.7, the following parts are set up:

A ttable part

The name is Cust, and its DatabaseName property is the Demos,tablename property for the customer.

A tdatasource part

The DataSet property is set to Cust, whose name is Custsource.

A tquery part

The name is orders and its databasename is set to the Demos,sql property value:

Select Orders.custno,orders.orderno,orders.saledate from Orders

WHERE Orders.custno =: Custno

The Datasouce property of orders is set to Custsource

A tdatasource part

The name is Ordersource and its dataset attribute is set to orders.

Two Tdbgrid parts

They are connected to Custsource and Ordersource respectively.

Parameters in the dynamic SQL statement in the tquery part orders: Custno did not assign a value to it during the programming process, and Delphi automatically looks up and parameters in the data source Custsource described in its DataSource property when the application runs: Custno matches the field, and the Custsource has exactly one field named Custno with the parameter: Custno match, so the Custno field value in the Customer table is assigned to the parameter: Custno, and when the record pointer in the Customer table is moved , Parameters: Custno value will change, and parameters: The value of Custno changed, orders in the dynamic SQL statements will be based on the new parameter value requery, from the database table to obtain the corresponding order data, so also changed to implement similar to the main-detail application. is the connection query.

The use of 17.4.4 prepare method

In the use of dynamic SQL statements programming, often used a very important method prepare, call the Prepare method, Delphi will take the SQL statement with parameters to its corresponding database engine, dynamic SQL statements for parsing and optimization. While it is not necessary to invoke the prepare method when programming with dynamic SQL statements, we strongly recommend calling the prepare method, since invoking the prepare method greatly improves the performance of dynamic SQL statements. Especially when you want to execute the same dynamic SQL statement repeatedly, its superiority will be more obvious. If the Prepare method is not explicitly invoked before executing an SQL statement in an application, Delphi calls the Propare method implicitly to prepare the query each time the SQL statement is executed.

The tquery part also has a prepare property, which is a Boolean property that, when its property value is true, indicates that the query is ready (the SQL statement has been routed to the database engine) when we use the parameter editor parameters Editor to assign a value to a parameter in a dynamic SQL statement, when the corresponding parameter value is set and exit the parameter editor, Delphi implicitly calls the prepare method to prepare the query.

After the SQL statement is executed, to prepare the next query, you must first call the Close method before you can call the prepare method to prepare the next query. In general, you should call the Prepare method once in an application, often call the Prepare method in the form's OnCreate event processing, and then assign a value to the parameter using the method described above, and finally call the Open method or the Execsql method to execute the SQL statement. To complete the query.

Of course, when you call the Prepare method to prepare a query, you will consume some database resources, so every time a query executes, it's good practice to call the Unprepare method to undo the query. When you run a program to change the SQL attribute value of the tquery part by program, Delphi automatically calls the Close method and the Unprepare method to undo the query.