18th Chapter-delphi Client Server application Development (III.) (5)

18.3.3 Using Stored Procedures

18.3.3.1 Tstoredproc Parts Overview

A stored procedure is a program that accepts input parameters based on a database server and returns the results to the application. The TSTOREDPROC part operates a stored procedure in a database on a remote server. A stored procedure is a collection of expressions that are stored as part of a server. The stored procedure performs a series of repetitive database-related tasks on the server and passes the results to the client application, such as the Delphi database application.

The Tstaredproc widget enables the Delphi database application to execute stored procedures on the server.

Typically, actions that are used in a large number of records in a database table and use statistical or mathematical functions are the preferred objects for stored procedures. By transferring these repetitive computing tasks to the server, you can improve the performance of your database application.

Take full advantage of the server's processing power and speed

Reduce the number of network transmissions

For example, consider an application that needs to compute a single value, and the standard deviation value in a large number of records. If you perform this function in the Delphi application, you must get all the records used in the calculation from the server, which will result in network congestion. Because the application needs only the final return value that represents the standard deviation. Therefore, it is more efficient to read data from stored procedures on the server, perform calculations, and pass values to the application.

1. Key attributes of Tsoredproc

⑴databasename Property

The DatabaseName property describes the name of the database to be accessed. This property can be:

The BDE alias has been defined

Directory of local Database

directory path and filename for local interbase server

Tdatabase-defined application alias

Use the Close method to make the dataset part inactive before changing the databasename.

⑵storedprocname Property

The Storedprocname property represents the name of the stored procedure on the server. Oracle servers allow multiple stored procedures with the same name. Therefore, you set the overload property to describe the stored procedure name that is executed on the Oracle server.

⑶overload Property

Oracle servers allow overloads of stored procedures in Oracle software packages. That is, different procedures with the same name, set the overload property to describe the stored procedure executed on the Oracle server. If the overload value is zero, the assumption is that there is no overload, and if Overload is 1, Delphi executes the first stored procedure with the same name; if the value is 2, the second stored procedure executes.

⑷params Property

The params property contains parameters passed to the stored procedure.

2. Key methods

⑴parambyname method

Statement: function Parambyname (const value:string): Tparam;

The Parambyname method returns the value of an element with the name value in the params property. This method is generally used to assign a value to a parameter in a dynamic query.

⑵prepare method

The Prepare method prepares the stored procedure to execute, which allows the server to load the stored procedure or prepare to handle the exception.

⑶execproc method

The Execproc method executes the stored procedure on the server.

⑷open method

The Open method opens the dataset part and places it in Browse state. This is equivalent to putting the active property to true. For Tstoredproc If the stored procedure returns a result set, use open to execute the stored procedure. If the stored procedure returns a single line, use Execproc to execute the stored procedure earlier.

18.3.3.2 Tstoreproc Use method

1. Establish a StoredProc component

The steps to establish a TSTOREDPROC part for a stored procedure on a database server are as follows:

⑴ Select the Tstoredproc part from the data access page of the component palette to put it on the module.

⑵ sets the DatabaseName property of the Tstoredproc part to the name of the database in which the stored procedure resides.

DatabaseName must be a BDE alias.

⑶ sets the Storedprocname property of the Tstoredproc part to the stored procedure name used, or from the Drop-down

list box to select the.

⑷ describes the input parameters in the params property of the Tstoredproc part. You can use the parameter editor to set the loss

into the parameter. The parameter editor also allows the user to view the values returned to the application by the stored procedure.

2. Set the input parameters of the stored procedure to see the output parameters

Many stored procedures need to pass them a series of input parameters to determine what to process and how to handle them, as described in the params property. The order of the input parameters described is important and is determined by the stored procedures on the server. At design time, the easiest and safest way to do this is to activate the Tstoredproc parameter Editor and edit the input parameters. The parameter editor lists the input parameters in the correct order and lets you assign them a value.

To activate the Tstoredproc parameter editor:

① Select the Tstoredproc part.

② Activates the accelerator menu (Speed menus) by right-clicking the mouse button.

③ choose Define Parameters.

The list of parameter names shows all the input, output, and result parameters of the procedure. Information about input and output parameters is obtained from the server. For some servers and parties, parameter information is inaccessible, such as Sybase, in which case the list box is empty, so you must increase the input and output parameters yourself in the order that the procedure requires.

The Parameter type check box describes whether the selected parameter is input, output, or result parameter. If the server support parameter can be both input and output. If you add a parameter to the list box, you must set the parameter type.

Data type check box to list the data type of the parameter selected in the list box. You must set the data type if you give the list box the sound and sound parameters.

Assign a value to the input parameter in the value edit box.

If the server does not pass stored procedure information to Delphi, you can use the Add button to add parameters to the stored procedure. The Delete button deletes the added parameter, and the Clear button clears all the parameters in the list box.

When you have finished setting the parameters, select the OK button.

3. Establish parameters and parameter values at run time

Establish parameters at run time to directly access the params property. The params property is an array of parameter strings. For example, the following code assigns the text of an edit box to the first string of an array:

Staredproc1.params[0]. Asstring: = Edit1.text

You can also use the Paramsbyname method to access parameters by name:

Storedproc1.paramsbyname (' company ') asstring: = Edit1.text;

4. Preparing and executing stored procedures

To use a stored procedure, you must also prepare and execute it. There are two ways to prepare a stored procedure:

At design time, by selecting the OK button for the parameter editor

At run time, by calling the Tstoredproc prepare method

For example, the following code prepares the execution of a stored procedure:

Storedproc1.prepare;

To perform a prepared stored procedure, call the Execproc method of the Tstroedproc part. The following code demonstrates preparing and executing stored procedures:

Storedproc1.params[0] Asstring: = Edit1.text;

Storedproc1.prepare;

Storedproc1.execproc;

When you execute a stored procedure that returns an output parameter or result set, there are two possible return types: a single return, such as a single value or set of values, and a bunch of result sets that return many values.

5. Accessing output parameters and result sets

The stored procedure returns a value in an array of output parameters. If the server supports a return value, it can be either a single result or a result set.

You access the output parameters of a stored procedure at run time, either by indexing the params property or by using the Parambyname method to access the values. The following expression sets the value of the edit box with an output parameter:

Edit1.text: = Storedproc1.params[6]. asstring;

Edit1.text: = Storedproc1.paramsbyname (' contact '). asstring;

If the stored procedure returns a result set, it is more useful to control access and display values with standard data-related controls.

On some servers, such as Sybase, stored procedures can return result sets like query statements, and applications can use data-related controls to display the output of these stored procedures.

Data-related control shows how the stored procedure returns results as follows:

① the datasource part on the data module.

② Sets the DataSet property of the DataSource part to the name of the Tstoredproc part that receives the data.

③ sets the DataSource attribute of the data-related control to the name of the datasource part.

This allows data-related control to display the results returned from the stored procedure when the Tstoredproc part and the active property are true.

18.3.4 from development platform to server