sql_bridge_example_-_inserting_a_note_in_a_company_record [Control]

**WARNING: If you are interested in using sql_bridge, please contact a sales or implementation consultant at Cyrious.**

Cyrious Control's sql_bridge is a collection of SQL Server stored procedures and functions and external modules that can be called to safely insert or update data into the Control database and notify the CHAPI service that data has been updated. The CHAPI service is a controller program that will then notify the SSLIP and all copies of Control about the changes.

**Caution**: Use of this feature requires expertise in SQL Server and in the Cyrious database structure. It is very possible to irreversibly damage your Cyrious Control database if you make a mistake. Always develop your tests using a separate database on a separate machine.

**Caution**: Always wrap multi-record operations with SQL transaction statements. This ensure the integrity of the entire update by preventing partial changes or records from being saved. If you are not familiar with using SQL transactions, STOP! Do not proceed with any database insertions, deletions, or updates until you are.

Example

This example inserts a note into a customer record.

DECLARE @Note VARCHAR(MAX);
DECLARE @AccountID INT;
DECLARE @ContactID INT;
DECLARE @JournalID INT;
DECLARE @EmployeeID INT;
DECLARE @ClassTypeID INT;
DECLARE @DT smalldatetime;
DECLARE @DivisionID INT;
DECLARE @ErrorMessage    VARCHAR(2048);
DECLARE @ErrorNumber     INT;
DECLARE @ErrorSeverity   INT;
DECLARE @ErrorState      INT;
DECLARE @ErrorLine       INT;
DECLARE @ErrorProcedure  VARCHAR(200);
-- Step 0 -- Set the key variables
SET @EmployeeID = 10; -- This is the System House Account
SET @AccountID = 9601; -- The customer Account.ID
SET @ClassTypeID = 20065; -- Journal Note
SET @DT = getdate();
-- Step 1 Look up the referenced company and Contact
SET @ContactID = (SELECT PrimaryContactID FROM Account WHERE ID = @AccountID);
SET @DivisionID = (SELECT COALESCE(DivisionID, 10) FROM Account WHERE ID = @AccountID);
--print '@AccountID = '+isnull( convert(varchar(25),@AccountID), 'NULL' );
--print '@ContactID = '+isnull( convert(varchar(25),@ContactID), 'NULL' );
-- Step 2, Get the new Journal ID
SET @JournalID = (SELECT dbo.csf_chapi_nextid(@ClassTypeID, 1));
-- Step 3, Insert Records!
BEGIN TRANSACTION
	BEGIN TRY
 
    INSERT INTO [Journal] ([ID], [StoreID], [ClassTypeID] ,[ModifiedByUser] ,[ModifiedByComputer] ,[ModifiedDate] ,[SeqID] ,[IsSystem] ,[IsActive] ,[EmployeeID],
			[JournalActivityType] ,[JournalActivityText] ,[Description] ,[Notes] ,[StartDateTime] ,[EndDateTime] ,[TotalTime] ,[ScheduledDateTime]  ,[CompletedByID] ,
			[CompletedDateTime] ,[IsSummary] ,[IsDetail] ,[SummaryID] ,[SummaryClassTypeID] ,[SummaryAmount] ,[DetailAmount] ,[StartGLGroupID]  ,[EndGLGroupID]  ,
			[AccountID] ,[AccountClassTypeID]  ,[ContactID]  ,[ContactClassTypeID]  ,[TransactionID] ,[TransactionClassTypeID]  ,[IsVoided]  ,[VoidedDateTime]  ,
			[VoidedEntryID] ,[VoidedEntryClassTypeID]  ,[VoidedReason]  ,[QueryStartDateTime] ,[QueryEndDateTime]  ,[ReminderDateTime]  ,[ReminderPrompt]  ,[PartID]  ,
			[ActivityType]  ,[ActivityTypeText]  ,[IsBillable] ,[BillableDateTime]  ,[UseActualTime]  ,[BillingNotes]  ,[BillingType]  ,[TotalBilledTime]  ,
			[RecurringActivityID]  ,[LinkID]  ,[LinkStoreID] ,[LinkClassTypeID] ,[SpecialCode] ,[DivisionID] ,[HasCalendarLinks] ,[TipRecipientID] ,[PartClassTypeID] ,
			[RecurringClassTypeID] ,[StationID] ,[StationClassTypeID] ,[CurrentState] ,[StageID] ,[StageClassTypeID]) 
 
    VALUES
        ( @JournalID     -- (
        , -1     -- ,
        , @ClassTypeID     -- ,
        , 'SQLBridge'     -- ,
        , @@ServerName     -- ,
        , GetDate()    -- ,
        ,  0    -- ,
        ,  0    -- ,
        ,  1    -- ,
        , @EmployeeID  -- 
        , 15  -- 
        , 'Note'  -- 
        , 'Slack Note'  -- 
        , @Note  -- 
        , @DT  -- 
        , @DT  -- 
        , NULL  -- 
        , NULL  -- 
        , @EmployeeID  -- 
        , @DT  -- 
        , 1  -- 
        , 1  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @AccountID  -- 
        , 2000  -- 
        , @ContactID  -- 
        , 3000  -- 
        , NULL  -- 
        , NULL  -- 
        , 0  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @DT  -- 
        , @DT  -- 
        , NULL  -- 
        , 0  -- 
        , NULL  -- 
        , 0  -- 
        , NULL  -- 
        , 0  -- 
        , NULL  -- 
        , 0  -- 
        , NULL  -- 
        , 0  -- 
        , 0  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , @DivisionID  -- 
        , 0  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- 
        , NULL  -- )
    );
 
-- Now commit the Transaction
COMMIT TRANSACTION
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    SELECT @ErrorMessage = ERROR_MESSAGE(),
            @ErrorSeverity = ERROR_SEVERITY(),
            @ErrorState = ERROR_STATE();
    -- Use RAISERROR inside the CATCH block to return 
    -- error information about the original error that 
    -- caused execution to jump to the CATCH block.
    RAISERROR (@ErrorMessage, -- Message text.
                @ErrorSeverity, -- Severity.
                @ErrorState -- State.
                );
END CATCH;
-- Step 5, Refresh the Customer Record
 
EXEC dbo.csf_chapi_refresh @AccountId, 2000, 0;

Contributor: Cyrious Software

Date: 2/2016

Version: Control 5.7

You could leave a comment if you were logged in.

Overview

Example

Source