SQL Bridge


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 <<confirm>>. 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


Here are some sample SQL methods that use SQLBridge. They require expertise, but a SQL guru can see how it is all working!


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
@lockId
int
Yes
The ID of the record to be locked.
@lockClassTypeID
int
Yes
The 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
@lockId
int
Yes
The ID of the record to be unlocked.
@lockClassTypeID
int
Yes
The 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:
Object
Call
Account
cs_chapi_nextid 2000, 1
AccountContact
cs_chapi_nextid 3000, 3
Address
cs_chapi_nextid 4000, 2
AddressLink
cs_chapi_nextid 4000, 5
AccountUDF
None. Uses the same ID as Account.
AccountContactUDF
None. Uses the same ID as AccountContact.

Parameters:

The stored procedure uses the following parameters:

Name
Data Type
Required
Description
@ClassTypeID
int
Yes
The ClassTypeID of the ID(s) requested.
@count
int
No
The 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;
 


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
@SpecialTypeName
varchar(32)
Yes
The special type name of the ID(s) requested.
@count
int
No
The 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
@lockId
int
Yes
The ID of the record to be refreshed.
@lockClassTypeID
int
Yes
The ClassTypeID of the record to be refreshed.
@etag
int
Yes
The 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
@lockId
int
Yes
The ID of the record to be refreshed.
@lockClassTypeID
int
Yes
The ClassTypeID of the record to be refreshed.
@etag
int
Yes
The new eTag (SeqID) of the record. Use -1 to refresh regardless of the eTag value.
@items
MacroMessageType
Yes
Table of macro messages type.
@clientsessionid
guid
No
Session 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
MessageType
int
Yes
ID of the Macro Message Type.
ID
int
No
Optional alternative ID.
ClassTypeID
int
No
Optional alternative Class Type ID.

Macro Message Types

ID
Name
NeedAltID
Description
AltIDDescription
0
Undefined
0


1
TierObjNew
0


2
TierObjEdit
0


3
TierObjDelete
0


4
TierObjVoid
0


5
OrderNew
0


6
OrderEdit
0


7
OrderStatusChange
0


8
OrderStationChange
0


9
OrderPayment
0


10
OrderVoid
0


11
OrderBuilt
0


12
OrderSale
0


13
OrderClosed
0


14
EstimateNew
0


15
EstimateEdit
0


16
EstimateStatusChange
0


17
EstimateStationChange
1

Line Item ID
18
EstimateVoid
0


19
EstimateConverted
0


20
EstimateLost
0


21
CompanyNew
0


22
CompanyEdit
0


23
CompanyDelete
0


24
CompanyPayment
0


25
CompanyCreditChange
0


26
CompanyFirstOrder
0


27
CompanyNewOrder
1

New TransHeaderID
28
CompanyNewEstimate
1

New TransHeaderID
29
ContactNew
0


30
ContactDelete
0


31
PaymentNew
0


32
PaymentVoid
0


33
UserNew
0


34
UserEdit
0


35
SMSCommand
0


36
EmployeeNew
0


37
EmployeeEdit
0


38
EmployeeDelete
0


39
ServiceTicketNew
0


40
ServiceTicketEdit
0


41
ServiceTicketStatusChange
0


42
ServiceTicketStationChange
0


43
ServiceTicketPayment
0


44
ServiceTicketVoid
0


45
ServiceTicketBuilt
0


46
ServiceTicketSale
0


47
ServiceTicketClosed
0


48
CompanyNewServiceTicket
1

New TransHeaderID
49
LineItemStationChange
0


50
InventoryYellow
0


51
InventoryRed
0


52
LineItemNew
0


53
CourseEventNew
0


54
CourseEventComplete
0


55
CourseEventNewLink
0


56
WorkAssignmentNew
0


57
WorkAssignmentComplete
0


58
OrderDropped
0


59
GLAccountChange
0


60
OrderStatusToChange
1

New Status ID
61
OrderStatusFromChange
1

Old Status ID
62
EstimateStatusToChange
1

New Status ID
63
EstimateStatusFromChange
1

Old Status ID
64
ServiceTicketStatusToChange
1

New Status ID
65
ServiceTicketStatusFromChange
1

Old Status ID
66
OrderStationToChange
1

New Station ID
67
OrderStationFromChange
1

Old Station ID
68
EstimateStationToChange
1

New Station ID
69
EstimateStationFromChange
1

Old Station ID
70
ServiceTicketStationToChange
1

New Station ID
71
ServiceTicketStationFromChange
1

Old Station ID
72
LineItemStationToChange
1

New Station ID
73
LineItemStationFromChange
1

Old Station ID
74
PurchaseOrderNew
0


75
PurchaseOrderEdit
0


76
PurchaseOrderStatusChange
0


77
PurchaseOrderStatusToChange
1

New Status ID
78
PurchaseOrderStatusFromChange
1

Old Status ID
79
BillNew
0


80
BillEdit
0


81
BillStatusChange
0


82
BillStatusToChange
1

New Status ID
83
BillStatusFromChange
1

Old Status ID
84
ReceivingDocNew
0


85
ReceivingDocEdit
0


86
ReceivingDocStatusChange
0


87
ReceivingDocStatusToChange
0


88
ReceivingDocStatusFromChange
0


89
AccountStageChange
0


90
AccountStageToChange
1

New Stage ID
91
AccountStageFromChange
1

Old Stage ID
92
ShipmentShipped
1

Shipment ID
93
ShipmentCreated
1

Shipment ID
94
ShipmentEdited
1

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

See Also