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.

• sql_stored_procedure_-_import_company
• sql_stored_procedure_-_import_contact
• sql_stored_procedure_-_import_order
• sql_stored_procedure_-_import_shipping_information for a single Ship To location.
• sql_stored_procedure_-_import_shipping_information Detail for shipping line items to different locations.
• sql_stored_procedure_-_import_estimate
• sql_stored_procedure_-_import_line_item
• sql_stored_procedure_-_import_address
• sql_stored_procedure_-_import_payment
• sql_stored_procedure_-_convert_estimate_to_order
• sql_stored_procedure_-_add_note_to_company_order_or_estimate
• sql_stored_procedure_-_import_part
• sql_stored_procedure_-_import_part Category
• sql_stored_procedure_-_insert_part_on_a_line_item
• sql_stored_procedure_-_customer_list_import
• sql_stored_procedure_-_quickbooks_import

Supporting Functions

• sql_stored_function_-_string_split
• sql_stored_function_-_map_tax_class_by_name
• sql_stored_procedure_-_import_text_file_to_cols

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

• sqlbridge_example_-_inserting_a_payment
• sqlbridge_example_-_creating_a_new_company
• sqlbridge_example_-_creating_a_new_order
• sql_bridge_example_-_creating_scheduled_payments
• sql_bridge_example_-_inserting_a_note_in_a_company_record
• sql_bridge_example_-_inserting_a_time_clock_record
• sql_bridge_example_-_auto-creating_a_default_sku_with_a_sql_trigger

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

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.

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

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:

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.

-- 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)

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:

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.

-- 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)

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:

Parameters:
The stored procedure uses the following parameters:

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

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

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:

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

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

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:

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.

-- 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)

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:

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.

-- 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)

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

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

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

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

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

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

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

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