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

There are two types of SQL statements written and used in the Delphi application: Static SQL statements, dynamic SQL statements. The static SQL statement is the SQL attribute value setting for the Tquery part in the program design phase. Dynamic SQL statement programming is the SQL statement contains a series of parameters, in the process of running the parameter values are variable, that is, the parameters of the SQL statement can be dynamically assigned values.

The static approach is to set the SQL command text as the SQL property value of the tquery part, so that when the application is executed, Delphi executes the SQL command set in the Tquery part SQL property. If it is a query command in SQL, the Tquery part is connected to the data control part through the Tdatasource part, and the result of the query is displayed in the data browsing part connected to the Tquery part. Dynamic SQL statement refers to the SQL statement contains some parameter variables, in the program can be assigned to these parameters, in the process of running the program, the value of each parameter is changed. There are also two ways to write SQL statements in the SQL properties of a tquery part by writing the corresponding SQL statements to the SQL properties of the Tquery in the program setup phase, and the other in the application developed by Delphi to SQL statements, Included in the Pascal code unit.

The specific steps for using the Tquery part in SQL programming are as follows:

① sets the DatabaseName property for the tquery part, which can be the alias of the database established with BDE, or the directory name in the desktop database system or the file name in the database server, if the tdatabase part is used in the application, Then the DatabaseName property of the tquery part can be a database alias defined in the Tdatabase part. For more information, please refer to "The use of tdatabase parts";

② set the SQL attribute for the tquery part, the SQL attribute value of the tquery part is the SQL command text to be executed by the application, and there are two ways to set the SQL properties:

In the process of programming, we can edit the SQL properties in Object Inspector to select the SQL property in the objects browser, which opens the String List Editor window, where we can write SQL commands, I can also open visual Query Builder to write SQL commands (only the client/server version of Delphi has this tool).

Include SQL commands in Pascal code unit

While the program is running, the Close method of the Tquery part is called first, closes the current tquery part, and then calls the clear method to purge the SQL property, describes the new SQL command text, and then calls the Add method to add the new SQL command text to the SQL property.

③ executes the SQL command by calling the Tquery part's Open method or the Execsql method. The Open method executes only the Select command, and the Execsql method can also execute other SQL commands. The difference between the open method and the Execsql method is discussed further in later chapters.

If you use a dynamic SQL statement, first call the Prepare method, assign a value to the parameter in the dynamic SQL statement, and then call the Open method or the Execsql method. Calling the Propare method is not necessary, but calling prepare can significantly improve the performance of the Tquery part execution SQL statement for multiple execution of a dynamic SQL statement in the SQL attribute in the tquery part.

17.2.2 write a simple SQL query command in the tquery part

In this section we will learn how to use the Tquery part to write simple SQL query commands and implement SQL queries in the Delphi application.

For example, if we want to query the number and company name of the customer in the table customer.db, we implement the following steps:

① Place a tquery part, a tdatasource part, a tdatagrid part in the application form, and connect them

② Set the DatabaseName property value of the form Tquery part Query1 to Dbdemos

③ double-click the SQL property of Query1 in the Object Inspector window, and Delphi displays the String List Editor window.

④ enter the SQL statement in the window in Figure 17.3:

Select Custno,company from Custormer;

⑤ Click the OK button to close the String List Editor window.

⑥ set the Open property of query to True.

17.3 SQL Language Programming Overview

The SQL command statement in the Delphi application is contained in the SQL attribute of the tquery part, and the SQL property of the tquery part is of type tstring, that is, the SQL attribute value is a list of strings that is very similar to an array of string types. Refer to the online Help for information about the tstring type. In the previous section we introduced the Tquery part can execute two kinds of SQL statements:

Static SQL statement

Dynamic SQL statement

A static SQL statement is fixed at design time, it does not contain any parameters and variables, such as the following statement is a static SQL statement:

Select * from Cusromer Where custno = 1234;

Dynamic SQL statements, also called parameterized statements, contain parameters that represent field names or table names, such as the following statement, which is a dynamic SQL statement:

Select * from Customer Where Custno =: number;

The variable number is a parameter variable, which is guided by a colon, and must be assigned a value for the parameter during the program's operation, and the SQL statement can be executed correctly, giving the parameter variable a different value each time the application is run.

Writing 17.3.1 SQL command text

1. Use string List Editor to write

When we want to set the SQL command text for the SQL attribute of the tquery part, you can select the Tquery part in the application form and double-click the SQL property in the Object Inspector window, which opens the String List Editor window. In this window we can write various SQL commands, as shown in Figure 17.3.

After you write the appropriate SQL statements, select the OK button to mount the SQL command text in the editor to the SQL properties of the Tquery part, and select the Save button to save the written SQL command to a file for later programming. We can also select the Load button to transfer SQL commands from one SQL command file when writing SQL command text. To set the SQL properties of the Tquery part while the program is running, you must first call the Close method, turn off the tquery part, and then call the clear method to purge the existing SQL command statement in the SQL attribute. Finally, call the Add method to set a new SQL command statement for the SQL property. For example:

Query1.close {close Query1)

Query1.SQL.Clear {Clear SQL command statements in SQL properties}

QUERY1.SQL.ADD (' Select * from Country ');

Query1.SQL.Add (' Where Name = ' Argentina ');

It is always safe to call the Close method when you set the SQL property for the tquery part, and if the tquery part has been closed, there is no effect when you call the closing method. When you set a new SQL command statement for a SQL property in your application, you must call the Clear method to purge the existing SQL command statement in the SQL property, and if you do not call the Clear method, call the Add method to set the SQL command statement to the SQL property. The newly set SQL command statement is appended to the existing SQL command statement, and often occurs when the program is running with unexpected query results and even the program cannot run.

In particular, the SQL attribute of the Tquery part can only contain a complete SQL statement, which is not allowed to be set to multiple SQL statements. Of course, some database servers also support setting multiple SQL statements in the SQL properties of the tquery part, as long as the database server allows us to set up multiple SQL statements for the SQL properties when we program.

2. Use Visual Query Builder to write

The client/server version of Delphi also contains a visual query Builder, visual Builder, with this visual tool we can only write SELECT statements. After you select the Tquery part in the application form, right-click, Pop up a pop-up menu, select Run Visual Query Builder and a dialog box prompts you to select the database you want to access, select the database you want to access, and then select the OK button. A pop-up dialog box prompts you to select the database table you want to query, select multiple database tables at a time, select multiple database tables, click the Add button after each table, select another table, select the table you want to query, and then click the Close button, The database table selected by the user will be displayed in the visual Query Builder.

For information about how to use the Visual Query Builder visual query Builder see online Help, when you construct a query in visual query Builder and exit visual query Builder, the SQL The command statement is automatically written to the corresponding SQL attribute of the tquery part.

17.3.2 SQL Program execution

After you have set the property values for the SQL property for the tquery part, and after you have written the appropriate SQL program, there are several ways to execute the SQL program.

In the design process, after you set the SQL property of the Tquery part to the value of its active property to True, you can execute the SQL program in the SQL property if there is a data browsing part (such as Tddgrid Tdbedit) connected to the Tquery part in the application The results of the SQL program are displayed in the data browsing widget.

During the application run, the open method or Execsql method that invokes the Tquery part by program can execute the SQL program in its SQL properties. The Open method and the Execsql method are not the same. We must pay attention to the process of programming. The Open method can only be used to execute the SQL language query statement (SELECT command) and return a query result set, and the Execsql method can also be used to execute other commonly used SQL statements such as INSERT, UPDATE, delete commands, such as:

Query1.open (This will return a query result set)

If you call the open method without the query result, an error occurs. You should now call the Execsql method instead of the open method. Such as:

Query1.execsql (no results returned)

Of course, when designing an application, programmers cannot determine whether the SQL statements in the Tquery part will return a query result. You should use try ... for this situation. Except module to design the program. The Open method is called in the Try section, and the Excesql method is called in the except section to ensure that the program is running correctly.

For example:

Try

Query1.open

Except

Query1.execsql

End

When you use the Tquery part in your application, you can also set its Unidirectional property to true, which speeds up the retrieval of database tables, but only moves the record pointer in one Direction, and by default, The value of the Unidirectional property is false.