====== ======
**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