4D v14

SP-Based Services (Example)

Home

 
4D v14
SP-Based Services (Example)

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:

 
  ` On Server Startup Database Method
 START SP SERVICES

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:

  ` START SP SERVICES Project Method
 ◊vlSPServices:=Execute on server("SP SERVICES";32*1024;"SP SERVICES";*)

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.

  ` STOP SP SERVICES Project Method
 SET PROCESS VARIABLE(◊vlSPServices;vbStopSPServices;True)

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:

ValueDescription
1the request has been posted but not processed yet.
0the request has been successfully processed.
< 0the request has been processed but an error occurred.

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:

  ` Client post request Project Method
  ` Client post request ( String { ; Text } ) -> Long
  ` Client post request ( Request Type { ; Parameters } ) -> Request ID
 CREATE RECORD([SP Requests])
 [SP Requests]reqID:=Sequence number([SP Requests])
 [SP Requests]reqType:=$1
 [SP Requests]reqStatus:=1
 If(Count parameters>=2)
    [SP Requests]reqParams:=$2
 End if
 SAVE RECORD([SP Requests])
 $0:=[SP Requests]reqID

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.

  ` Client get result Project Method
  ` Client get result ( Long ; ->BLOB {; Long } ) -> Long
  ` Client get result ( Request ID ; ->Data {; Delay } ) -> Error Code
 C_LONGINT($0;$1;$vlDelay)
 $0:=1
 $vlDelay:=0
 If(Count parameters>=3)
    $vlDelay:=$3
 End if
 READ ONLY([SP Requests])
 Repeat
    QUERY([SP Requests];[SP Requests]reqID=$1)
    If(Records in selection([SP Requests])>0)
       If([SP Requests]reqStatus&NBSP;#&NBSP;1)
          $2->:=[SP Requests]reqData
          READ WRITE([SP Requests])
          While(Locked([SP Requests]))
             WAITING LOOP($vlDelay)
             LOAD RECORD([SP Requests])
          End while
          DELETE RECORD([SP Requests])
          $0:=[SP Requests]reqStatus
       End if
    Else
  ` Request record has been lost!
  ` It should not happen. But anyway set error to -2 (arbitrary value)
       $0:=-2
    End if
  ` The request has not been processed yet
    If($0=1)
       WAITING LOOP($vlDelay)
    End if
 Until($0&NBSP;#&NBSP;1)
 READ ONLY([SP Requests])

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:

  ` WAITING LOOP Project Method
  ` WAITING LOOP ( Long )
  ` WAITING LOOP ( Delay in ticks )
 C_LONGINT($1)
 $vlStartTicks:=Tickcount
 Repeat
    IDLE
 Until((Tickcount-$vlStartTicks)>=$1)

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:

   Initialize a “stop” variable
   Repeat
      Look for the requests with the [SP Requests]reqStatus field equal to 1
      For each request
         Depending on the type of the request, call a subroutine
            that stores the result in the [SP Requests]reqData field
         Change the status of the request so that the client knows what happened
      End for
      “Sleep” a little bit before to start again
   Until the “stop” variable becomes true

Here is the actual source code:

  ` SP SERVICES Project Method
  ` The stored procedure is starting
 vbStopSPServices:=False
  ` The stored procedure does not need read-write access to the tables...
 READ ONLY(*)
  ` ...except the [SP Requests] table
 READ WRITE([SP Requests])
 Repeat
  ` Look for the requests that have not been processed yet
    QUERY([SP Requests];[SP Requests]reqStatus=1)
  ` Process these requests one after one
    For($vlRecord;1;Records in selection([SP Requests]))
  ` If the request record is locked, wait until it becomes unlocked
       While(Locked([SP Requests]))
  ` Wait one second before trying again
          DELAY PROCESS(Current process;60)
  ` Try to get read-write access
          LOAD RECORD([SP Requests])
       End while
  ` Assume the request will be processed successfully
       [SP Requests]reqStatus:=0
       Case of
          :([SP Requests]reqType="Server Information")
             SP DO SERVER INFORMATION
          :([SP Requests]reqType="Volume List")
             SP DO VOLUME LIST
          :([SP Requests]reqType="Browse Directory")
             SP DO BROWSE DIRECTORY([SP Requests]reqParams)
  ` ...
  ` OTHER REQUEST TYPES COULD BE ADDED HERE!
  ` ...
          Else
  ` The request type is unknown, returns error -1 (arbitrary value)
             [SP Requests]reqStatus:=-1
       End case
  ` Force request status to be different from 1
  ` (in case a subroutine sets it to 1)
       If([SP Requests]reqStatus=1)
          [SP Requests]reqStatus:=-3
       End if
  ` Update the request record
       SAVE RECORD([SP Requests])
  ` Go to the next unprocessed request
       NEXT RECORD([SP Requests])
    End for
  ` Free the last processed request record
    UNLOAD RECORD([SP Requests])
  ` Wait one second before starting answering request again
    DELAY PROCESS(Current process;60)
  ` Loop until the SP is told to stop execution
 Until(vbStopSPServices)

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.

  ` SP DO SERVER INFORMATION Project Method
 TEXT TO BLOB(Application version(*);[SP Requests]reqData;UTF8 C string)
 TEXT TO BLOB(Structure file;[SP Requests]reqData;UTF8 C string;*)
 TEXT TO BLOB(Data file;[SP Requests]reqData;UTF8 C string;*)
 PLATFORM PROPERTIES($vlPlatform;$vlSystem;$vlMachine)
 VARIABLE TO BLOB($vlPlatform;[SP Requests]reqData;*)
 VARIABLE TO BLOB($vlSystem;[SP Requests]reqData;*)
 VARIABLE TO BLOB($vlMachine;[SP Requests]reqData;*)

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.

  ` SP DO VOLUME LIST Project Method
 VOLUME LIST($asVName)
 $vlSize:=Size of array($asVName)
 ARRAY REAL($arVSize;$vlSize)
 ARRAY REAL($arVUsedSpace;$vlSize)
 ARRAY REAL($arVFreeSpace;$vlSize)
 For($vlElem;1;$vlSize)
    VOLUME ATTRIBUTES($asVName{$vlElem};$arVSize{$vlElem};$arVUsedSpace{$vlElem}
    ;$arVFreeSpace{$vlELem})
 End for
 VARIABLE TO BLOB($asVName;[SP Requests]reqData)
 VARIABLE TO BLOB($arVSize;[SP Requests]reqData;*)
 VARIABLE TO BLOB($arVUsedSpace;[SP Requests]reqData;*)
 VARIABLE TO BLOB($arVFreeSpace;[SP Requests]reqData;*)

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:

  ` M_SERVER_INFORMATION
 C_BLOB(vxData)
 C_LONGINT($vlReqID;$vlErrCode;$vlOffset)
  ` Post the request
 $vlReqID:=Client post request("Server Information")
  ` Poll the request status and get the result
 $vlErrCode:=Client get result($vlReqID;->vxData;60)
  ` If the request is successfully completed, display the result
 If($vlErrCode=0)
  ` Extract the result information from the BLOB
    $vlOffset:=0
    vsServerVersion:=BLOB to text(vxData;UTF8 C string;$vlOffset)
    vsStructureFile:=BLOB to text(vxData;UTF8 C string;$vlOffset)
    vsDataFile:=BLOB to text(vxData;UTF8 C string;$vlOffset)
    BLOB TO VARIABLE(vxData;$vlPlatform;$vlOffset)
    BLOB TO VARIABLE(vxData;$vlSystem;$vlOffset)
    BLOB TO VARIABLE(vxData;$vlMachine;$vlOffset)
  ` Analyse the platform properties
    vs4DPlatform:="Unknown 4D Server Version"
    vsSystem:="Unknown System Version"
    vsMachine:="Unknown Machine"
  `...
  ` Here is the code (not listed) that parses the $vlSystem and $vlMachine
  ` ( see the example for the PLATFORM PROPERTIES command)
  ` ...
  ` Display the result information
    DIALOG([SP Requests];"SERVER INFORMATION")
 Else
    ALERT("Request error "+String($vlErrCode))
 End if
  ` No longer need the BLOB
 CLEAR VARIABLE(vxData)

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:

  ` M_SERVER_VOLUMES
 C_BLOB(vxData)
  ` Post the request
 $vlReqID:=Client post request("Volume List")
  ` Poll the request status and get the result
 $vlErrCode:=Client get result($vlReqID;->vxData;120)
  ` If the request is successfully completed, display the result
 If($vlErrCode=0)
  ` Extract the result information from the BLOB
    $vlOffset:=0
    BLOB TO VARIABLE(vxData;asVName;$vlOffset)
    BLOB TO VARIABLE(vxData;arVSize;$vlOffset)
    BLOB TO VARIABLE(vxData;arVUsedSpace;$vlOffset)
    BLOB TO VARIABLE(vxData;arVFreeSpace;$vlOffset)
    For($vlElem;1;Size of array(arVSize))
  ` Convert from bytes to MB
       arVSize{$vlElem}:=arVSize{$vlElem}/1048576
       arVUsedSpace{$vlElem}:=arVUsedSpace{$vlElem}/1048576
       arVFreeSpace{$vlElem}:=arVFreeSpace{$vlElem}/1048576
    End for
  ` Display the result information
    DIALOG([SP Requests];"VOLUME LIST")
 Else
    ALERT("Request error "+String($vlErrCode))
 End if
  ` No longer need the BLOB
 CLEAR VARIABLE(vxData)

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

 
PROPERTIES 

Product: 4D
Theme: 4D Server and the 4D Language

 
SEE ALSO 

SP-Based Import (Example)
Stored Procedures

 
ARTICLE USAGE

4D Server Reference ( 4D v13)
4D Server Reference ( 4D Server v12)
4D Server Reference ( 4D Server v11 SQL Release 6)
4D Server Reference ( 4D v14 R2)
4D Server Reference ( 4D v14)
4D Server Reference ( 4D v14 R3)
4D Server Reference ( 4D Server v14 R4)