4D v13Stored Procedures |
||
|
4D v13
Stored Procedures
Stored Procedures
The expression Stored Procedure comes from the SQL-based Server world. When a client workstation sends a request to an SQL-based server, it actually sends a plain text request in SQL language to the SQL-based server. This request is then parsed and interpreted on the SQL-based server before being executed. Obviously, if the source code of the request is huge and if the request is sent multiple times during a session, there is a great deal of time spent in sending the source code over the network, parsing and interpreting the request as many times as the request is sent. So, the idea was to find a way to send that request over the network, parse and interpret it once, and then execute it only each time it was received from a client workstation. The solution was to keep the request source code (in other words, a procedure) on the server side and have the client workstation send a request consisting only of the name of the procedure to be executed. The procedure is consequently said to be “stored” on the server, thus the term “stored procedure.” Note that an SQL-based stored procedure is a procedure that can receive parameters from a client workstation, execute the tasks it implements (synchronously or asynchronously) and eventually return a result to the client workstation. When a client workstation invokes the execution of a stored procedure, to a certain extent, it delegates code execution on the server machine. Although we use the industry name, the capabilities of 4D Server stored procedures significantly exceed the regular concept of stored procedures. With 4D in local mode, when you use a command, such as New process, you can start a user process in which you can run a method. This method is called a process method (see the Project Methods section in the 4D Language Reference manual). You can do the same with 4D Server, on a client machine. In addition, using the command Execute on server on the server machine, you can start a user process in which you can run a method. Moreover, when using the EXECUTE ON CLIENT, you can run a method in another process on a different client. Important: The essential difference between an SQL-based stored procedure and a 4D Server stored procedure is that in the first case you execute an SQL procedure, in the second case, you run a stand-alone 4D process. Like a regular process, a stored procedure has its own environment:
In terms of user interface, a stored procedure can open windows and display data (i.e., DISPLAY RECORD). You can start as many as stored procedures as the system authorizes (hardware and memory). In fact, the 4D Server machine should be viewed as a machine that not only replies to 4D Client and Web browsers, but also one that executes processes that interact with other processes running on the server machine and on remote 4D machines. In the same way that 4D provides a multi-tasking environment to user processes running on the machine, 4D Server provides a multi-tasking environment to stored procedures. For example, 4D Server maintains a table of interprocess variables that can be used by the stored procedures for interprocess communications. Note: The "Execute on Server" method property can also be used to execute a method in a process on the server, but the method uses a "twinned" process of the client process in this case, which means more particularly that it can take advantage of the environment of this client process. In this case, it is not a 4D stored procedure. For more information, please refer to the Execute on Server attribute section. Aside from data entry for stored procedures executed on the server, almost everything said in the 4D Language Reference manual about the capabilities of processes and commands applies to stored procedures. A stored procedure can add, query, order by, update or delete records. A stored procedure can use sets and named selections, access documents on disk, work with BLOBs, print records and so on. Just think that instead of doing something on a local 4D machine, you are doing it on the server machine or on one or several 4D client machines. One obvious advantage of stored procedures executed on the server is that indeed a stored procedure executes locally on the server machine, the machine where the database engine is located. For example, an APPLY TO SELECTION is not efficient over the network, but it is from within a stored procedure. The example proposed in the section SP-Based Import (Example) shows the magnitude of performance optimization you can achieve with “smart” stored procedure implementation. Stored procedures executed on one or several client machines allows to optimize the task repartition and the communication between several client machines. Refer to the command REGISTER CLIENT in the Language Reference manual for an example of a stored procedures executed on several clients. However, the most important advantage of the stored procedure architecture is the additional dimension it gives to 4D Server. Using stored procedures, you can implement your own custom 4D Server services. The only limit is your imagination. The example in the section SP-Based Services (Example) shows a stored procedure that provides clients with information about 4D Server or the server machine. You can, for example, list the volumes of the server machine. This example could be expanded easily for returning directory or document information to a client. Generally speaking, stored procedures executed on the server should not deal with interface items (such as menus, windows, forms...). Indeed the interface is not managed on the server's side. Here is the list of the commands that should NOT be used within stored procedures executed on the server. These commands are organized within three groups:
ACCUMULATE • Inappropriate commands on server We strongly advise you not to use the following commands in stored procedures because they are not suitable for the server executing method. They can block the server and create errors, and in any case they do not execute properly. No specific error code is returned.ACCEPT (2) Only when the syntax results in displaying a dialog box (i.e.: ORDER BY([Table])).
You can execute it on 4D Server or on another 4D client machine. Please note that to display the 4D client machines in this list, they should have been first registered (see the Stored procedures on client machines section and the REGISTER CLIENT command).
Note: It is not possible to use the process management commands DELAY PROCESS, PAUSE PROCESS and RESUME PROCESS from a remote 4D with stored procedures on the server.
Stored procedures can communicate between themselves using:
Refer to the corresponding parts of the 4D Language Reference manual. Once again, keep in mind that the 4D commands act within the scope of the server machine which is executing the stored procedure (server or clients) in the same way as they act in the scope of a client machine. Note: The CALL PROCESS and Outside call mechanism has no meaning on the server machine, because stored procedures do not have a user interface with data entry. There is yet another important feature: client user processes (processes running on a client machine) can read and write the process variables (*) of a stored procedure, using the commands GET PROCESS VARIABLE, SET PROCESS VARIABLE and VARIABLE TO VARIABLE. (*) as well as the server machine interprocess variable. Important: “Intermachine” process communication, provided by the commands GET PROCESS VARIABLE, SET PROCESS VARIABLE and VARIABLE TO VARIABLE, is possible from client to server only. It is always a client process that reads or write the variables of a stored procedure. |
PROPERTIES
Product: 4D SEE ALSO
SP-Based Import (Example) |