**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 prodedures 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 scheduled payment into the database for a series of order (based on a query).
-- Step 0, Variable Declarations DECLARE @ScheduledDate smalldatetime; DECLARE @COUNT INT; DECLARE @NextID INT; DECLARE @ClassTypeID INT; DECLARE @Orders TABLE (THID INT PRIMARY KEY); DECLARE @ErrorMessage VARCHAR(2048); DECLARE @ErrorNumber INT; DECLARE @ErrorSeverity INT; DECLARE @ErrorState INT; DECLARE @ErrorLine INT; DECLARE @ErrorProcedure VARCHAR(200); -- Step 1, Set any user-specified data SET @ScheduledDate = '2/10/2016'; SET @ClassTypeID = 16110; -- ClassTypeID for Scheduled Payments -- Step 2, figure Out the orders to create scheduled payments for. -- Adjust the WHERE clause for your needs but leave the last line (so it doesn't duplicate existing scheduled payments) -- INSERT INTO @Orders SELECT TH.ID FROM TransHeader TH WHERE StatusID = 3 AND EXISTS (SELECT * FROM TransDetail TD WHERE TD.TransHeaderID = TH.ID AND TD.GoodsItemID IN (1407, 1408, 10016, 10017, 10019, 1441, 1442, 1443, 1444, 1445, 1446, 1447) ) AND NOT EXISTS (SELECT * FROM ScheduledPayment Existing WHERE Existing.TransactionID = TH.ID) ORDER BY ID SET @COUNT = (SELECT COUNT(*) FROM @Orders) -- Step 3, Get the starting ID and reserve @Count IDs SET @NextID = (SELECT dbo.csf_chapi_nextid(@ClassTypeID, @COUNT)); -- Step 4, Insert Records! BEGIN TRANSACTION BEGIN TRY INSERT INTO dbo.ScheduledPayment ( ID, StoreID, ClassTypeID, ModifiedByUser, ModifiedByComputer, ModifiedDate, SeqID, IsSystem, IsActive, TransactionID, TransactionNumber, ScheduledDate, Description, Amount, Attempts, Results, IsComplete, CompletedDate, CompletedPaymentID ) SELECT @NextID - 1 + rank() OVER (ORDER BY TH.ID) AS ID , CONVERT(INT, -1) AS StoreID , @ClassTypeID AS ClassTypeID , 'SQLBridge' AS ModifiedByUser , CONVERT(VARCHAR(12), NULL) AS ModifiedByComputer , getdate() AS ModifiedDate , CONVERT(INT, 0) AS SeqID , CONVERT(bit, 1) AS IsSystem , CONVERT(bit, 1) AS IsActive , TH.ID AS TransactionID , TH.OrderNumber AS TransactionNumber , @ScheduledDate AS ScheduledDate , 'Payment for Order #'+CONVERT(VARCHAR(10), TH.TransactionNumber) + ' ' + TH.Description AS Description , TH.BalanceDue AS Amount , CONVERT(INT, 0) AS Attempts , '' AS Results , CONVERT(bit, 0) AS IsComplete , CONVERT(smalldatetime, NULL) AS CompletedDate , CONVERT(INT, NULL) AS CompletedPaymentID FROM @Orders O JOIN TransHeader TH ON O.THID = TH.ID -- 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 Orders. -- Rather than use csf_chapi_refersh (which would be fine) -- I'm going to insert records in the RefreshMonitor table. -- This will take 1 minute or so to refresh (not as good), -- but it's a whole lot easier. INSERT INTO RefreshMonitor (ID, ClassTypeID, SeqID, IsDeleted) SELECT O.THID, @ClassTypeID, -1, CONVERT(bit, 0) FROM @Orders O
Source
Contributor: Cyrious Software
Date: 2/2016
Version: Control 5.7
You could leave a comment if you were logged in.