SP-Based Services (Example)

In the example discussed in the section SP-Based Import (Example), a stored procedure is started and ended each time an import data operation is requested. In this example, a stored procedure is started automatically when the server database starts up, and can be ended and restarted at will by any 4D connected to the database. As soon as it runs, the stored procedure can reply asynchronously to multiple requests sent by the clients connected to the database.

While the SP-Based Import (Example) section shows how to implement a drastically optimized existing service provided by 4D Server, this example shows how to implement new and custom services available to all connected 4D client machines. In addition, this example can be used as a template for implementing your own services.

The stored procedure is automatically started by the On Server Startup Database Method:

Because the On Server Startup Database Method starts the SP SERVICES project method as a stored procedure, SP SERVICES starts running as soon as the database is opened with 4D Server, whether or not clients are actually connected to the server database. In the following figure, the 4D Server administration window shows the stored procedure running when no client is yet connected.

The START SP SERVICES project method is listed here:

Since the Execute on server command acts like New process when called on the server machine, the same method (START SP SERVICES) can be used on the server machine or on any client machine to start, at will, the method SP SERVICES as a stored procedure on the server machine.

The STOP SP SERVICES project method “tells” the SP SERVICES project method to stop.

When the SP SERVICES project method starts, it sets the vbStopSPServices process variable to False and then loops until this Boolean variable becomes True. The command SET PROCESS VARIABLE, enables any user process running on the server or any client machines to change the value of the vbStopSPServices variable, and consequently stop the stored procedure at will.

The stored procedure should be able to receive and reply asynchronously to client requests at any time and in any order. A straightforward way to insure this communication is to use a table.

The [SP Requests] table contains the following fields:

• [SP Requests]reqID is set using the Sequence number command. This field uniquely identifies each request.
• [SP Requests]reqType describes the type of the request.
• [SP Requests]reqStatus may take one of the following values:

Note: These values are arbitrarily choosen for this example, they are not imposed by 4D.

• [SP Requests]reqData is a BLOB containing the data of the request. It can contain data sent by the requester or data returned by the stored procedure to the requester.
• [SP Requests]reqParams optionally contains parameter values sent by the requester to the stored procedure.

Communication between a client process and a stored procedure can be implemented using the command GET PROCESS VARIABLE, SET PROCESS VARIABLE and VARIABLE TO VARIABLE. For example, this is the solution used in the section SP-Based Import (Example), as well as in the STOP SP SERVICES project method listed previously.

Here, the system must allow the stored procedure to receive and send back variable amounts of data. Arrays, including Text and Picture arrays, could be used, but there are two reasons for using a table:

• The algorithm for handling requests via records is simpler to implement. Posting a request from a client machine just consists of adding a request to the table. Replying to the request from within the stored procedure just consists of modifying this request.
• Since the requests are stored in a table, they are stored on disk. As a result, the size of a large request is is not an issue, because it can be purged from memory (unlike data stored in arrays).

The Client post request project method is a generic method for posting a request:

The method returns the request ID number whose unicity is guaranteed by the use of the Sequence number command. After the record has been added to the [SP Requests] database, the client can poll the field [SP Requets]redStatus in order to wait until the stored procedure has completely handled the requests.

The Client get result project method is a generic method for polling the status of the request. As explained previously, as soon as the field [SP Requets]redStatus becomes different from 1, the client knows that the stored procedure has managed (successfully or not) the request.

If the request has been successfully managed by the stored procedure, the method copies the result (if any) from the record to the BLOB whose pointer is passed as parameter. The caller method then parses and uses the BLOB data according to the type of the request. Note that the client is in charge of deleting the [SP Requests] record once the request is completed.

The small WAITING LOOP project method loops until a number of ticks has elapsed:

Reminder: DELAY PROCESS has no effect on the Application process. Using the WAITING LOOP project method, the process will wait the required amount of time, even though the request originated from the User environment process of a client machine.

The SP SERVICES project method is the method running as stored procedure on the server machine. The overall architecture of this method, here shown in pseudocode, is straightforward:

Here is the actual source code:

The SP SERVICES project method can be used as a template for implementing new services to a database. In this section, we detail the SP DO SERVER INFORMATION and SP DO VOLUME LIST subroutines. The SP DO BROWSE DIRECTORY (which takes as a parameter the parameter sent by the client in the [SP Requests]reqParams field) is not detailed in this document.

Depending on the type of the request, the SP SERVICES project method calls a subroutine whose task is to store the result data in the [SP Requests]reqData field. Saving the record and changing the status of the request is performed by the SP SERVICES project method.

Here is the SP DO SERVER INFORMATION subroutine. It stores server-related information in the BLOB. Another project method will extract the BLOB data accordingly on the client machine.

Here is the SP DO VOLUME LIST subroutine. It stores volume-related information in the BLOB. Another project method will extract the BLOB data accordingly on the client machine.

Using the generic Client post request and Client get result project methods, the M_SERVER_INFORMATION project method displays the server information returned by the stored procedure on the client machine. This method could be attached to a menu command or invoked, for instance, from a button’s object method:

Here is the [SP Requests];"SERVER INFORMATION" form being executed:

Using the generic Client post request and Client get result project methods, the M_SERVER_VOLUMES project method displays, on the client machine, the server machine volume list returned by the stored procedure. This method could be attached to a menu command or invoked, for instance, from a button’s object method:

Here is the [SP Requests];"VOLUME LIST" form being executed: