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.
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 stand-alone 4D process.

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 a 4D client machine allow 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 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.
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 intercepted through a method installed in the ON ERR CALL command.

ACCUMULATE
ADD RECORD
_o_ADD SUBRECORD
APPEND MENU ITEM
BREAK LEVEL
CALL PROCESS
CHANGE LICENSES
Count menu items
Count menus
CREATE USER FORM
DELETE MENU ITEM
DELETE USER FORM
DISABLE MENU ITEM
DISPLAY SELECTION
EDIT ACCESS
EDIT FORM
ENABLE MENU ITEM
FILTER EVENT
Get menu item
Get menu item key
Get menu item mark
Get menu item style
Get menu title
SET PICTURE TO LIBRARY
_o_GRAPH TABLE
INSERT MENU ITEM
Level
LIST USER FORMS
Menu selected
MODIFY RECORD
MODIFY SELECTION
_o_MODIFY SUBRECORD
ON EVENT CALL
_o_Open external window
PAGE BREAK
PAGE SETUP
PRINT SETTINGS
QUERY BY EXAMPLE
QR REPORT
Printing page
REMOVE PICTURE FROM LIBRARY
SET MENU ITEM
SET MENU ITEM SHORTCUT
SET MENU ITEM MARK
SET MENU ITEM STYLE
SET PICTURE TO LIBRARY
SHOW MENU BAR
Subtotal

• 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
Activated
_o_ADD DATA SEGMENT
After
APPEND DATA TO PASTEBOARD
APPEND TO LIST
Before
BLOB TO DOCUMENT
BLOB to list
BRING TO FRONT
_o_C_GRAPH
CANCEL
CHANGE CURRENT USER
CHANGE PASSWORD
CLEAR LIST
CLEAR PASTEBOARD
Copy list
Count list items
Count screens
Create document(1)
_o_Create resource file(1)
Current form table
Current user
Deactivated
DELETE FROM LIST
DELETE USER
DIALOG
_o_DISABLE BUTTON
DRAG AND DROP PROPERTIES
DRAG WINDOW
Drop position
_o_During
_o_ENABLE BUTTON
ERASE WINDOW
EXPORT DATA(1)
FILTER KEYSTROKE
Find window
Focus object
FONT LIST
_o_Font name
_o_Font number
Form event
FORM FIRST PAGE
FORM Get current page
FORM GET PROPERTIES
FORM GOTO PAGE
FORM LAST PAGE
FORM NEXT PAGE
FORM PREVIOUS PAGE
FORM SET INPUT
FORM SET OUTPUT
Frontmost process
Frontmost window
Get edited text
GET GROUP LIST
GET GROUP PROPERTIES
GET HIGHLIGHT
GET LIST ITEM
GET LIST ITEM PROPERTIES
GET LIST PROPERTIES
GET MOUSE
GET PASTEBOARD DATA
GET PICTURE FROM PASTEBOARD
Get text from pasteboard
GET USER LIST
GET USER PROPERTIES
GET WINDOW RECT
Get window title
GOTO OBJECT
GRAPH SETTINGS
HIDE PROCESS
HIDE TOOL BAR
HIDE WINDOW
HIGHLIGHT RECORDS
HIGHLIGHT TEXT
IMPORT DATA(1)
In break
In footer
In header
INSERT IN LIST
_o_INVERT BACKGROUND
Is a list
Is user deleted
Keystroke
List item parent
List item position
LIST TO BLOB
Load list
MAXIMIZE WINDOW
Menu bar height
Menu bar screen
MINIMIZE WINDOW
Modified
New list
Next window
OBJECT GET COORDINATES
OBJECT MOVE
OBJECT SET LIST BY NAME
OBJECT SET COLOR
OBJECT SET ENTERABLE
OBJECT SET FILTER
OBJECT SET FORMAT
OBJECT SET RGB COLORS
OBJECT SET TITLE
OBJECT SET VISIBLE
Old
Open document(1)
Open resource file(1)
ORDER BY(2)
Outside call
Pasteboard data size
Pop up menu
POST CLICK
POST EVENT
POST KEY
QUERY BY FORMULA(2)
QUERY(2)
REDRAW
_o_REDRAW LIST
REDRAW WINDOW
REGISTER CLIENT
REJECT
SAVE LIST
SCREEN COORDINATES
SCREEN DEPTH
Screen height
Screen width
Select folder
SELECT LIST ITEMS BY POSITION
SELECT LIST ITEMS BY REFERENCE
SELECT LOG FILE
Selected list items
Self
SET CURSOR
SET FIELD TITLES
Set group properties
SET LIST ITEM
SET LIST ITEM PROPERTIES
SET LIST PROPERTIES
SET PICTURE TO PASTEBOARD
SET SCREEN DEPTH
SET TABLE TITLES
SET TEXT TO PASTEBOARD
SET TIMER
Set user properties
SET WINDOW RECT
Shift down
SHOW PROCESS
SHOW WINDOW
SORT LIST
User in group
Validate password
Window kind
WINDOW LIST
Window process

(1) Only when the first parameter is an empty string.

(2) Only when the syntax results in displaying a dialog box (i.e.: ORDER BY([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.

GRAPH
MESSAGES OFF
MESSAGES ON
SET MENU BAR
SHOW TOOL BAR

• From 4D, you can manually start a stored procedure in the Execute Method dialog box:

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).

• Also on 4D, 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 a remote 4D with stored procedures on the server.

• A method executed on 4D Server (server database method, method with the Execute on Server attribute or stored procedure) can start a stored procedure using Execute on server, New process or EXECUTE ON CLIENT.

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 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.