Differences

This shows you the differences between two versions of the page.

--- customization:stored-procedures [2016-10-07 13:17]
127.0.0.1 external edit
+++ customization:stored-procedures [2018-12-02 22:09]
@@ Line 1: / Line 1: @@
-====== Stored procedures ======
-Custom stored procedures can be triggered from the object model (VBA). This can be very useful if much data needs to be processed since the data never has to leave the server.
-===== Creating a procedure =====
-Only custom stored procedures may be executed from VBA. The system has two requirements for custom procedures:
-  * It must have "csp_" prefixed to its name.
-  * It must be flagged with a ''%%--%% FLAG_EXTERNALACCESS %%--%%'' comment somewhere in the procedure body.
-The following example creates a custom procedure that given the record ID of a coworker returns the total sum from all time reports from companies that the coworker is responsible for. In addition, an XML document is returned containing information on the ten largest customers for the coworker. If all goes well 0 is returned, otherwise 1.
-<code sql>
-CREATE PROCEDURE [dbo].[csp_toptencustomers]
-   @@responsible   int,
-   @@totalamount   int OUTPUT
-AS
--- FLAG_EXTERNALACCESS --
-  DECLARE @retval int
-  SET @retval = 0
-  -- Make sure the coworker exists and is active
-  IF (NOT EXISTS(SELECT * FROM dbo.[coworker] WHERE [status] = 0 AND [inactive] = 0 AND [idcoworker] = @@responsible))
-     SET @retval = 1
-  IF (@retval = 0)
-  BEGIN
-     -- Retrieve all time reports for the coworker
-     SELECT @@totalamount = SUM(t.[units] * t.[price]) FROM dbo.[timereport] t
-         INNER JOIN dbo.[company] c ON c.[idcompany] = t.[company]
-         WHERE c.[status] = 0 AND t.[status] = 0 AND c.[responsible] = @@responsible
-     -- Then retrieve the ten biggest customers
-     SELECT TOP 10
-AS [TAG]
-       , NULL AS [PARENT]
-       , c.[name] AS [topten!1!company]
-       , SUM(t.[units] * t.[price]) AS [topten!1!amount]
-     FROM dbo.[timereport] t
-       INNER JOIN dbo.[company] c ON c.[idcompany] = t.[company]
-       WHERE c.[status] = 0 AND t.[status] = 0 AND c.[responsible] = @@responsible
-       GROUP BY c.[name]
-      ORDER BY [topten!1!amount]
-       FOR XML EXPLICIT, BINARY BASE64
-  END
-  RETURN @retval
-GO
-</code>
-===== Registering a procedure =====
-In order for Lime CRM Server to become of the new custom procedure, these steps must be followed:
-  - Execute the ''lsp_setdatabasetimestamp'' procedure. This refreshes the internal procedure cache.
-  - Launch //Component Services// from the Start menu. Go to **Computers** | **My Computer** | **COM+ Applications**. Right-click //Lundalogik Data Components// and select **Shut down** from the popup menu.
-  - Restart Lime CRM Desktop Client.
-The above procedures needs to be done whenever adding or removing custom procedures and also when changing the signature of existing procedures, e.g. changing anything to do with procedure parameters.
-===== Calling a procedure =====
-Custom procedures are available from VBA through the Database.Procedures object. The following example lists all procedures and dumps information about their parameters:
-<code VB>
-Public Sub DisplayProcedureInfo()
-   Dim Procedure As LDE.Procedure
-   Dim Parameter As LDE.ProcedureParameter
-   For Each Procedure In Database.Procedures
-       Debug.Print "Procedure " & Procedure.Name
-       For Each Parameter In Procedure.Parameters
-           Debug.Print vbTab & Parameter.Name & " as " _
-                             & Parameter.DataType _
-                             & IIf (Parameter.IsOutput, " for output", "")
-       Next Parameter
-   Next Procedure
-End Sub
-</code>
-A custom procedure can be accessed either by index or by name, where the latter is the safest alternative. In the following example the procedure created above is executed:
-<code VB>
-Public Sub ExecuteTopTen()
-   Dim Procedure As LDE.Procedure
-   Set Procedure = Database.Procedures("csp_toptencustomers")
-   Procedure.Parameters("@@responsible").InputValue = Database.ActiveUser.ID
-   ' Log the execution to the Info Log
-   Procedure.Log = True
-   ' Run it synchronously
-   Procedure.Execute False
-   ' Print the output parameter value
-   Debug.Print Procedure.Parameters("@@totalamount").OutputValue
-   ' Print the XML output
-   Debug.Print Procedure.Result
-End Sub
-</code>
-==== Logging ====
-The ''Log'' property controls whether or not Lime CRM will log whether or not it was possible to execute the procedure at all. Any other logging needs to be handled by the procedure itself.
-==== Execution mode ====
-Procedures may be run synchronously or asynchronously depending on the boolean flag passed to ''Procedure.Execute''. Procedures can only be run asynchronously if they don't have out parameters or return an XML document. Extended logging is recommended.