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

Overview

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 uses SQL programming to automatically generate a SKU for parts. Anytime a new part is added, if the SKU is blank, a new SKU is generated when the record is saved. This occurs in an Insert Trigger within SQL. The trigger routine then uses sql_bridge to inform Control that it needs to refresh the record from the database (to pull the new SKU). 

-- =============================================
-- Author:		Cyrious Software
-- Create date: 2016-03-10
-- Description:	Automates the creation of the SKU for Parts if not assigned manually
-- =============================================
CREATE TRIGGER NewPart_AddSKUTrigger
   ON  Part
   AFTER INSERT, UPDATE
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Multiple records can be inserted.  
	-- Join the inserted records on the newly updated record to update the SKU if needed
	UPDATE Part
	SET SeqID = Inserted.SeqID + 1,
 
        -- Use a simple logic routine here for the SKU = CategoryID.PartID
        SKU = 
		   CONVERT(VARCHAR(12), COALESCE(Inserted.CategoryID, 0))
		   +'.'
		   +CONVERT(VARCHAR(12), Inserted.ID)
	FROM Inserted
	JOIN Part ON Inserted.ID = Part.ID
	WHERE COALESCE(RTrim(Inserted.SKU), '') = ''
	;
	-- Because we can have multiple parts simultaneously added, 
	-- we need to iterate through them all and refresh them individually
	DECLARE @NewID INT;
	DECLARE @NewCTID INT;
	DECLARE my_Cursor CURSOR FAST_FORWARD FOR 
		SELECT ID, ClassTypeID
		FROM INSERTED
		WHERE COALESCE(RTrim(Inserted.SKU), '') = '';
	OPEN my_Cursor;
	FETCH NEXT FROM my_Cursor INTO @NewID, @NewCTID;
	WHILE @@FETCH_STATUS = 0 
	BEGIN 
		EXEC dbo.csf_chapi_refresh @NewID, @NewCTID, -1;
		FETCH NEXT FROM my_Cursor INTO @NewID, @NewCTID;
	END;
	CLOSE my_Cursor;
	DEALLOCATE my_Cursor;
END

Source

Contributor: Cyrious Software

Date: 3/2016

Version: Control 5.7

You could leave a comment if you were logged in.