Overview

Cyrious Control uses Microsoft SQL Server as its database engine/backend. It is often desirable to interface the Control data with another business application such as a web site, production scheduling program, or other.

Knowledgeable database programmers can read the data directly from the SQL databases without danger of interference with Control provided they ensure database, table, and row level locking is disabled in their requests. This is commonly done with the with (nolock) directive when writing SQL select statements, or by setting the transaction isolation to . Failure to disable locking will likely result in write failures and the loss of new data in Control.

Cyrious Control's SQLBridge (SQLBridge) 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: 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.

Stored Procedures that use SQL Bridge

T-SQL Examples

Installation

SQLBridge is included for free and is already installed in Control as of version 6.0+ and beyond.


How to Use SQLBridge

A typical insert sequence using SQLBridge follows these steps:

  1. Request a new ID or new IDs.
  2. Create a SQL transaction.
  3. Insert the record with the given IDs.
  4. Commit the SQL transaction.
  5. Notify Control of the inserted IDs so it can refresh anything being displayed.
  6. Handle any errors if they arose.

A typical update sequence (which may also include inserts) using SQLBridge follows these steps:

  1. Request a lock on the record(s) to be modified. If this fails, another process is editing the record and the process can't continue.
  2. If applicable, request any new ID or new IDs.
  3. Create a SQL Transaction
  4. Update the records to change.
  5. If applicable, insert any new records with the given IDs.
  6. Commit the SQL Transaction.
  7. Release the locks obtained in step 1.
  8. Notify Control of the changed and inserted IDs so it can refresh anything being displayed.
  9. Handle any errors if they arose.

Delete operations are supported by SQLBridge, but are strong cautioned against except by implementors very familiar with Control database structure. A typical delete sequence using SQLBridge follows these steps:

  1. Create a SQL transaction.
  2. Delete the desired record(s).
  3. Commit the SQL transaction.
  4. Notify Control of the deleted IDs so it can refresh anything being displayed.
  5. Handle any errors if they arose.


Stored Functions and Procedures Used

Each of the individual steps necessary to communicate with Control* is contained in SQL scalar-value functions. The stored functions are:

  • SQLBridge.Chapi.Lock (formerly csf_chapi_lock)
  • SQLBridge.Chapi.Unlock (formerly csf_chapi_unlock)
  • SQLBridge.Chapi.NextID (formerly csf_chapi_nextID)
  • SQLBridge.Chapi.NextNumber (formerly csf_chapi_nextNumber)
  • SQLBridge.Chapi.Refresh (formerly csf_chapi_refresh)
  • SQLBridge.Chapi.RefreshEx (formerly csf_chapi_refresh_ex)
  • SQLBridge.Chapi.Recompute (formerly csf_chapi_recompute)

These stored functions call an internal function found in the DLL. These functions are listed for reference, but should probably never be called directly.

  • _SQLBridge.Chapi.Lock
  • _SQLBridge.Chapi.Unlock
  • _SQLBridge.Chapi.NextID
  • _SQLBridge.Chapi.NextNumber
  • _SQLBridge.Chapi.Refresh
  • _SQLBridge.Chapi.Recompute


SQLBridge.Chapi.Lock

This stored function obtains a lock on an object, as identified by its ID and ClassTypeID. As a general rule for complex objects, only the root object that is being changed needs to be locked. For example, when modifying any or all parts of any order, only the Order Header records needs to be locked. Individual line items, UDFs, etc. do not need to be locked.

If the object is already locked, the stored function raises an error. This indicates another user is currently editing the object and it can not be adjusted. Any changed made to a locked user are likely to be overridden when the user with the lock writes her changes. Any routine calling this stored procedure must allow for this case and handle it gracefully.

Parameters: The stored procedure uses the following parameters:

Name Data Type Required Description
@lockIdintYesThe ID of the record to be locked.
@lockClassTypeIDintYesThe ClassTypeID of the record to be locked.

Returns:
The stored procedure a single value of type bit.

  • 1 Lock successfully acquired.
  • 0 Lock not acquired. This is usually because a lock already exists on that object, but could also be the result of an invalid ID or ClassTypeID.
  • Warning: Always check the returned value. Saving an order without a lock may result in that information being overwritten by changes from Control or the SSLIP.


Examples:

-- lock order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.LOCK](3345, 10000)
 
-- alternate version, lock order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.LOCK]( (SELECT ID FROM TransHeader WHERE OrderNumber = 12345 AND TransactionType IN (1,6)), 10000)
 
-- lock company with Account.ID = 1715
SELECT dbo.[SQLBridge.Chapi.LOCK](1715, 2000)

SQLBridge.Chapi.Unlock

This stored function unlocks an object, as identified by its ID and ClassTypeID. Committing your inserts or changes does not call unlock automatically. The Unlock must always be called after making changes when a Lock was called. Until Unlock is called, no other users will be able to make subsequent changes. If the object is not locked, CHAPI still returns success. Note: If the object was locked by another user, this call will still succeed. There is no requirement that the Unlocking request be made by the same user as the Lock request. Parameters: The stored procedure uses the following parameters:

Name Data Type Required Description
@lockIdintYesThe ID of the record to be unlocked.
@lockClassTypeIDintYesThe ClassTypeID of the record to be unlocked.

Returns: The stored procedure a single value of type bit. The return value does not indicate that the record was unlocked, but only that the message was relayed to the CHAPI endpoint.

  • 1 Unlock message sent.
  • 0 Unlock message not sent. This indicates the function was not able to establish a connection with the CHAPI endpoint.


Examples:

-- unlock order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.UNLOCK](3345, 10000)
 
-- alternate version, unlock order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.UNLOCK]( (SELECT ID FROM TransHeader WHERE OrderNumber = 12345 AND TransactionType IN (1,6)), 10000)
 
-- unlock company with Account.ID = 1715
SELECT dbo.[SQLBridge.Chapi.UNLOCK](1715, 2000)

SQLBridge.Chapi.NextID

This stored function gets the next ID (or series of IDs) in the database for an object, as identified by the ClassTypeID of the object. Control tracks which IDs are available in a database and does not re-check the database for the validity of its understands. If an external program inserts data with a different ID, Control will not realize the ID is taken and may attempt to write a new record with the same ID. This will result in a “Key Violation” and the loss of the new data being inserted by Control. For this reason, obtaining the ID(s) to use through Control is mandatory for the successful operation of Cyrious Control. Do not attempt to generate or determine your own IDs to use. Each call requests the IDs for one type of object. You may request more than one idea if you have multiple records of one type to insert, but you will need to use multiple calls if you have multiple types of records to insert. For example, a typical call to insert a new Customer might include the following distinct calls:

ObjectCall
Accountcs_chapi_nextid 2000, 1
AccountContactcs_chapi_nextid 3000, 3
Addresscs_chapi_nextid 4000, 2
AddressLinkcs_chapi_nextid 4000, 5
AccountUDFNone. Uses the same ID as Account.
AccountContactUDFNone. Uses the same ID as AccountContact.

Parameters:
The stored procedure uses the following parameters:

Name Data Type Required Description
@ClassTypeIDintYesThe ClassTypeID of the ID(s) requested.
@countintNoThe number of IDs requested. If not supplied, 1 is assumed. This value must be between 1 and 1000.

Returns:
The stored procedure a single value of type int, corresponding to the first ID in the granted range.

Examples:

-- request the necessary IDs to insert an order (sample only, not necessarily the full list)
DECLARE @OrderID INT = dbo.[SQLBridge.Chapi.NextID](10000, DEFAULT);  -- 1 order
DECLARE @OrderVariationID INT = dbo.[SQLBridge.Chapi.NextID](10400, DEFAULT);  -- 1 order variation
DECLARE @OrderItemID INT = dbo.[SQLBridge.Chapi.NextID](10100, 2);  -- 2 line items
DECLARE @OrderPartID INT = dbo.[SQLBridge.Chapi.NextID](10300, 10);  -- 10 parts on line items (between both line items)
DECLARE @OrderActivityID INT = dbo.[SQLBridge.Chapi.NextID](20500,1);  -- 1 Transaction Activity to record creation
DECLARE @OrderGLID INT = dbo.[SQLBridge.Chapi.NextID](8900, 8);  -- 8 GL Entries for the Order
 
-- Now do the insertion with this information
--
-- <<your code here>>
 
-- If you want to see the ID selected above, just run a select statement on that declared variable.
SELECT @OrderID;
SELECT @OrderVariationID;
SELECT @OrderItemID;
SELECT @OrderPartID;
SELECT @OrderActivityID;
SELECT @OrderGLID;

Note: The ClassTypeIDs can be found in database_table_by_object_type_and_classtypeid.

SQLBridge.Chapi.NextNumber

This stored function is similar to SQLBridge.Chapi.NextID except it is used for "special" IDs and numbers. These are numbers that are tracked but not actually database ID key fields.  The following special type names can be supplied:
  • OrderNumber
  • EstimateNumber
  • OrderTemplateNumber
  • InvoiceNumber
  • PurchaseOrderNumber
  • BillNumber
  • ReceivingDocNumber
  • CompanyNumber
  • GLGroupID
  • UserCloseOut
  • DailyCloseOut
  • MonthlyCloseOut
  • YearlyCloseOut
  • ExportCloseOut
  • RoyaltyCloseOut
  • PCChargeSettlement
  • WeeklyCloseout
  • QuarterlyCloseout
  • SessionCloseout

Parameters: The stored procedure uses the following parameters:

Name Data Type Required Description
@SpecialTypeNamevarchar(32)YesThe special type name of the ID(s) requested.
@countintNoThe number of IDs requested. If not supplied, 1 is assumed. This value must be between 1 and 1000.

Returns:
The stored procedure a single value of type int, corresponding to the first ID in the granted range.

Examples:

-- request the next order number (distinct from the OrderID)
DECLARE @OrderNumber INT = SELECT dbo.[SQLBridge.Chapi.NextNumber]('OrderNumber', 1);
 
-- Now do the insertion with this information
--
-- <<your code here>>

SQLBridge.Chapi.Refresh

Anytime a Control user changes something, all Control users are notified to Refresh their copy of the data if they have it loaded. When an external program inserts or changes a record in the database, there is no way for Control to realize the information it might have in memory could be wrong. This stored function directs Control to refresh any copy of the object it might already have loaded to ensure it matched the database copy.  The eTag/SeqID must be incremented every time a record is updated. The eTag is used by Control to determine if the copy in memory is the latest copy. If the eTag/SeqID in memory is equal to or later than the eTag/SeqID in the refresh message, it assumes it has the current (or later) copy and ignores the refresh message. Use -1 to always cause a refresh, regardless of the current eTag/SeqID value in memory.  **Warning**: If the refresh function is not called, Control may overwrite the changed data with it's own copy of the data upon some subsequent edit.  Parameters:  The stored procedure uses the following parameters:
Name Data Type Required Description
@lockIdintYesThe ID of the record to be refreshed.
@lockClassTypeIDintYesThe ClassTypeID of the record to be refreshed.
@etagintYesThe new eTag (SeqID) of the record. Use -1 to refresh regardless of the eTag value.

Returns:
The stored procedure a single value of type bit. The return value does not indicate that all copies of Control were refreshed, but only that the message was relayed to the CHAPI endpoint.
* 1 Refresh Sent.
* 0 Refresh not sent. This indicates the function was not able to establish a connection with the CHAPI endpoint.

Examples:

-- refresh order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.Refresh](3345, 10000, -1)
 
-- alternate version, refresh order number 12345 (with OrderHeader.ID = 3345)
SELECT dbo.[SQLBridge.Chapi.Refresh]( (SELECT ID FROM TransHeader WHERE OrderNumber = 12345 AND TransactionType IN (1,6)), 10000, -1)
 
-- refresh company with Account.ID = 1715
SELECT dbo.[SQLBridge.Chapi.Refresh](1715, 2000, -1)

SQLBridge.Chapi.RefreshEx

Anytime a Control user changes something, all Control users are notified to Refresh their copy of the data if they have it loaded. When an external program inserts or changes a record in the database, there is no way for Control to realize the information it might have in memory could be wrong. This stored function directs Control to refresh any copy of the object it might already have loaded to ensure it matched the database copy. Along with the refresh notification, macro notifications can be included as well.  The eTag/SeqID must be incremented every time a record is updated. The eTag is used by Control to determine if the copy in memory is the latest copy. If the eTag/SeqID in memory is equal to or later than the eTag/SeqID in the refresh message, it assumes it has the current (or later) copy and ignores the refresh message. Use -1 to always cause a refresh, regardless of the current eTag/SeqID value in memory.  **Warning**: If the refresh function is not called, Control may overwrite the changed data with it's own copy of the data upon some subsequent edit.  Parameters:  The stored procedure uses the following parameters:
Name Data Type Required Description
@lockIdintYesThe ID of the record to be refreshed.
@lockClassTypeIDintYesThe ClassTypeID of the record to be refreshed.
@etagintYesThe new eTag (SeqID) of the record. Use -1 to refresh regardless of the eTag value.
@itemsMacroMessageTypeYesTable of macro messages type.
@clientsessionidguidNoSession ID of the control client that executed the refresh. This is only used by control for client executed macros. This is for internal Cyrious use only.
MacroMessageType Columns
Name Data Type Required Description
MessageTypeintYesID of the Macro Message Type.
IDintNoOptional alternative ID.
ClassTypeIDintNoOptional alternative Class Type ID.
Macro Message Types
ID Name NeedAltID Description AltIDDescription
0Undefined0
1TierObjNew0
2TierObjEdit0
3TierObjDelete0
4TierObjVoid0
5OrderNew0
6OrderEdit0
7OrderStatusChange0
8OrderStationChange0
9OrderPayment0
10OrderVoid0
11OrderBuilt0
12OrderSale0
13OrderClosed0
14EstimateNew0
15EstimateEdit0
16EstimateStatusChange0
17EstimateStationChange1 Line Item ID
18EstimateVoid0
19EstimateConverted0
20EstimateLost0
21CompanyNew0
22CompanyEdit0
23CompanyDelete0
24CompanyPayment0
25CompanyCreditChange0
26CompanyFirstOrder0
27CompanyNewOrder1 New TransHeaderID
28CompanyNewEstimate1 New TransHeaderID
29ContactNew0
30ContactDelete0
31PaymentNew0
32PaymentVoid0
33UserNew0
34UserEdit0
35SMSCommand0
36EmployeeNew0
37EmployeeEdit0
38EmployeeDelete0
39ServiceTicketNew0
40ServiceTicketEdit0
41ServiceTicketStatusChange0
42ServiceTicketStationChange0
43ServiceTicketPayment0
44ServiceTicketVoid0
45ServiceTicketBuilt0
46ServiceTicketSale0
47ServiceTicketClosed0
48CompanyNewServiceTicket1 New TransHeaderID
49LineItemStationChange0
50InventoryYellow0
51InventoryRed0
52LineItemNew0
53CourseEventNew0
54CourseEventComplete0
55CourseEventNewLink0
56WorkAssignmentNew0
57WorkAssignmentComplete0
58OrderDropped0
59GLAccountChange0
60OrderStatusToChange1 New Status ID
61OrderStatusFromChange1 Old Status ID
62EstimateStatusToChange1 New Status ID
63EstimateStatusFromChange1 Old Status ID
64ServiceTicketStatusToChange1 New Status ID
65ServiceTicketStatusFromChange1 Old Status ID
66OrderStationToChange1 New Station ID
67OrderStationFromChange1 Old Station ID
68EstimateStationToChange1 New Station ID
69EstimateStationFromChange1 Old Station ID
70ServiceTicketStationToChange1 New Station ID
71ServiceTicketStationFromChange1 Old Station ID
72LineItemStationToChange1 New Station ID
73LineItemStationFromChange1 Old Station ID
74PurchaseOrderNew0
75PurchaseOrderEdit0
76PurchaseOrderStatusChange0
77PurchaseOrderStatusToChange1 New Status ID
78PurchaseOrderStatusFromChange1 Old Status ID
79BillNew0
80BillEdit0
81BillStatusChange0
82BillStatusToChange1 New Status ID
83BillStatusFromChange1 Old Status ID
84ReceivingDocNew0
85ReceivingDocEdit0
86ReceivingDocStatusChange0
87ReceivingDocStatusToChange0
88ReceivingDocStatusFromChange0
89AccountStageChange0
90AccountStageToChange1 New Stage ID
91AccountStageFromChange1 Old Stage ID
92ShipmentShipped1 Shipment ID
93ShipmentCreated1 Shipment ID
94ShipmentEdited1 Shipment ID

Returns: The stored procedure a single value of type bit. The return value does not indicate that all copies of Control were refreshed, but only that the message was relayed to the CHAPI endpoint.

  • 1 Refresh Sent.
  • 0 Refresh not sent. This indicates the function was not able to establish a connection with the CHAPI endpoint.


Examples:

-- refresh order number 12345 (with OrderHeader.ID = 3345) and send a station changed to Installation macro message
DECLARE @MacroMessageTypes MacroMessageType;
INSERT INTO @MacroMessageTypes
(MessageType, ID, ClassTypeID)
VALUES
(66, 1037, 26100)
 
SELECT dbo.[SQLBridge.Chapi.RefreshEx](3345, 10000, -1, @MacroMessageTypes, NULL)

Underlying SQL Commands

The code for the underlying SQL Commands is contained in this section.

SQLBridge.Chapi.Lock

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Locks a Control Record
-- =============================================
ALTER FUNCTION [SQLBridge.Chapi.LOCK]
(
    @id [INT], @ct [INT]
)
RETURNS bit
AS
BEGIN
  RETURN [dbo].[_SQLBridge.Chapi.LOCK]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @id, @ct);
END

SQLBridge.Chapi.Unlock

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Unlocks a Control Record
-- =============================================
ALTER FUNCTION [SQLBridge.Chapi.UNLOCK]
(
    @id [INT], @ct [INT]
)
RETURNS bit
AS
BEGIN
    RETURN [dbo].[_SQLBridge.Chapi.UNLOCK]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @id, @ct);
END

SQLBridge.Chapi.NextID

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Gets the next ID block for an object
-- =============================================
ALTER FUNCTION [SQLBridge.Chapi.NextID]
(
    @ct [INT], @COUNT [INT]
)
RETURNS bit
AS
BEGIN
    RETURN [dbo].[_SQLBridge.Chapi.NextID]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @ct, @COUNT);
END

SQLBridge.Chapi.NextNumber

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Gets the next number block for an object
-- =============================================
ALTER FUNCTION [SQLBridge.Chapi.NextID]
(
    @name nvarchar(256), @COUNT [INT]
)
RETURNS bit
AS
BEGIN
    RETURN [dbo].[_SQLBridge.Chapi.NextNumber]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @name, @COUNT);
END

SQLBridge.Chapi.Refresh

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Refresh a record in Control
-- =============================================
ALTER FUNCTION SQLBridge.Chapi.Refresh
(
  @id [INT], @ct [INT], @etag [INT]
)
RETURNS bit
AS
BEGIN
    RETURN [dbo].[_SQLBridge.Chapi.Refresh]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @id, @ct, @etag, NULL, NULL);
END

SQLBridge.Chapi.RefreshEx

-- =============================================
-- Author:        Cyrious Software
-- Create date: 1/2014
-- Description:    Refresh a record in Control
-- =============================================
ALTER FUNCTION [SQLBridge.Chapi.RefreshEx]
(
  @id [INT], @ct [INT], @etag [INT], @items MacroMessageType READONLY, @clientsessionid uniqueidentifier = NULL
)
RETURNS bit
AS
BEGIN
    DECLARE @itemsTable TABLE(mrt INT, tid VARCHAR(25));
    INSERT INTO @itemsTable
    SELECT MessageType AS mrt,
           CASE
             WHEN ISNULL(ID, -1) = -1 THEN NULL
             WHEN ISNULL(ClassTypeID, -1) = -1 THEN LTRIM(STR(ID))
             ELSE LTRIM(STR(ID)) + 'c' + LTRIM(STR(ClassTypeID))
           END AS tid
    FROM   @items
    DECLARE @itemsJson nvarchar(4000);
    SELECT @itemsJson = COALESCE(@itemsJson + ',', '')
    + '{{{{""mrt"":' + LTRIM(STR(mrt))
    + CASE WHEN tid IS NULL THEN '' ELSE ',""tid"":""' + tid + '""' END
    + '}}}}'
    FROM @itemsTable
    ;
    IF @itemsJson IS NOT NULL
      SET @itemsJson = '[' + @itemsJson + ']';
    RETURN [dbo].[_SQLBridge.Chapi.Refresh]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @id, @ct, @etag, @itemsJson, @clientsessionid)
END

SQLBridge.Chapi.Recompute

ALTER FUNCTION [dbo].[SQLBridge.Chapi.Recompute]
(
    @id [INT], @ct [INT], @notes nvarchar(256)
)
RETURNS [bit]
AS
BEGIN
    RETURN [dbo].[_SQLBridge.Chapi.Recompute]('{ChapiUri}', '{ChapiDBName}', {EnableLogging}, @id, @ct, @notes);
END


You could leave a comment if you were logged in.