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.
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 1 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
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
andlsp_refreshldc
procedures. 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:
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
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:
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
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.