|
|
|
|
|
|
|
|
|
version 2004 (Modified)
What is an SQL-based Stored Procedure?
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.
What is a 4D Server Stored Procedure?
Although we use the industry name, the capabilities of 4D Server stored procedures significantly exceed the regular concept of stored procedures.
Using a 4D 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 4th Dimension Language Reference manual).
You can do the same with 4D Server, on a 4D Client machine. In addition, using the command Execute on server on the server machine, you can start a user process in which you are can run a method. Moreover, when using the EXECUTE ON CLIENT, you can run a method in another process on a different client.
In both cases, the method is called a stored procedure, and (with an abuse of language) the process started on the server machine or another client is also called a stored procedure.
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 4D process.
Architecture of 4D Stored Procedures
Like a regular process, a stored procedure has its own environment:
• Current selection per table: Each stored procedure has a separate current selection. One table can have a different current selection in different stored procedures.
• Current record per table: Each table can have a different current record in each stored procedure.
• Variables: Every stored procedure has its own process variables. Process variables are recognized only within the domain of their native stored procedure.
• Default table: Each stored procedure has its own default table.
• Process sets: Each stored procedure has its own process sets.
• On Error Call: Each stored procedure has its own error-handling method.
• Debugger window: Each stored procedure can have its own Debugger window.
In terms of user interface, a stored procedure can open windows and display data (i.e., DISPLAY RECORD).
A stored procedure executed on one or several client machines invoke data entry.
On the other hand a stored procedure executed on the server cannot invoke data entry (i.e., ADD RECORD); there is no data entry kernel on the server machine.
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 4D Client machines.
In the same way that 4th Dimension and 4D Client provide a multi-tasking environment to user processes running on a workstation, 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.
What a Stored Procedure Does?
Aside from data entry for stored procedures executed on the server, almost everything said in the 4th Dimension 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 and so on. Just think that instead of doing something on a 4D Client 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 new 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.
What a stored procedure does not do (executed on the server)?
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.
Commands displaying dialog boxes on the server machine as well as dialog boxes dealing with data entry should be avoided.
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:
• Forbidden commands on the server
If one of the following commands is used within a stored procedure, an alert will be displayed indicating that this command cannot be executed on 4D Server. The error #67 is returned, it can be catched through a method installed in the ON ERR CALL command.
• Unappropriate 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.
Create document (1)
EXPORT DATA (1)
IMPORT DATA (1)
Open document (1)
ORDER BY (2)
QUERY BY FORMULA (2)
QUERY (2)
SELECT LIST ITEMS BY REFERENCE
(1) Only when the first parameter is an empty string.
(2) Only when the syntax results in displaying a dialog box (i.e.: SORT ([Table])).
• Commands with no effect on the server
The following commands have no effect when they are executed within a stored procedure on the server. No specific error code is returned.
How to Start a Stored Procedure
• With 4D Client, you can manually start a stored procedure in the Execute Method dialog box:
You can execute it on 4D Server or on one or several 4D Client machines. Please note that to display the 4D Clients in this list, they should have been first registered (see the Registering 4D Client section and the REGISTER CLIENT command).
• Also on 4D Client, you can programmatically start a stored procedure using the commands Execute on server or EXECUTE ON CLIENT.
Note: It is not possible to use the process management commands DELAY PROCESS, PAUSE PROCESS and RESUME PROCESS from 4D Client with stored procedures on the server.
• A method executed on 4D Server (server database method or stored procedure) can start a stored procedure using Execute on server or New process or EXECUTE ON CLIENT.
More About Interprocess Communication Between Stored Procedures and User Processes
Stored procedures can communicate between themselves using:
• interprocess variables
• local or global semaphores
• records
• interprocess sets and interprocess named selections
• the commands GET PROCESS VARIABLE, SET PROCESS VARIABLE and VARIABLE TO VARIABLE.
Refer to the corresponding parts of the 4th Dimension 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.
See Also
SP-Based Import (Example), SP-Based Services (Example).
|
|
|
|
|
|
|
|
|