Coil Sync

Coil Sync Documentation

Coil sync enables the synchronization of coil inventory data from external databases into Eclipse Pro. This feature supports both full and incremental synchronization strategies to efficiently keep coil data up-to-date.

Key Features

1. Flexible SQL Queries: Define custom SQL statements to match your database schema
2. Change Detection: Support for incremental sync using datetime or rowversion columns
3. Deletion Detection: Periodic full syncs can identify and remove deleted coils
4. Multi-Database Support: Works with SQL Server, MySQL, Oracle, and DB2

SQL Query Requirements

Your SQL query must return at minimum these required columns:

Column Name	Type	Required	Description
INVCOIL	varchar	Yes	Unique coil identifier (must be uppercase in result set)
MATERIAL	varchar	Yes	Material code (must be uppercase in result set)

Column Name Case Sensitivity

The DbCoilSyncer requires INVCOIL and MATERIAL columns to be uppercase in the query result set. Use column aliases if your table has different casing:

SELECT InvCoil AS INVCOIL, Material AS MATERIAL, ...

Best Practice

Always use explicit column names instead of SELECT * to:

• Control column name casing with aliases
• Avoid type conversion errors with binary columns (timestamp/rowversion)
• Prevent ambiguous column errors
• Improve query performance

Additional Supported Columns

Column Name	Type	Description
Description	varchar	Material description
DateIn	datetime	Coil receipt date
DateOut	datetime	Coil consumption date
VendorName	varchar	Vendor name
VendorCode	varchar	Vendor code
HeatNumber	varchar	Heat/lot number
PurchaseOrder	varchar	Purchase order reference
StartingFt	decimal	Initial footage
RemainingFt	decimal	Current remaining footage
StoreLocation	varchar	Storage location
User1	varchar	Custom field 1
User2	varchar	Custom field 2

Required Parameters

Your SQL query must include these parameters:

• @Offset - For pagination (starting row)
• @FetchSize - For pagination (number of rows)
• @PlantCode - Plant filter (when configured in External Connection)
• @LastSyncValue - For change detection (when enabled)

Change Detection Methods

None (Full Sync)

Fetches all records every sync cycle. Suitable for small datasets.

SELECT 
  InvCoil AS INVCOIL, 
  Material AS MATERIAL,
  Description, DateIn, DateOut, RemainingFt, StartingFt,
  VendorName, VendorCode, HeatNumber, PurchaseOrder,
  StoreLocation, User1, User2
FROM CoilInventory
WHERE (@PlantCode IS NULL OR Plant = @PlantCode)
ORDER BY INVCOIL
OFFSET @Offset ROWS
FETCH NEXT @FetchSize ROWS ONLY

DateTime-based

Only syncs records modified since the last sync. Requires a datetime column that's updated on changes.

SELECT 
  InvCoil AS INVCOIL, 
  Material AS MATERIAL,
  LastModified,  -- Include for change detection
  Description, DateIn, DateOut, RemainingFt, StartingFt,
  VendorName, VendorCode, HeatNumber, PurchaseOrder,
  StoreLocation, User1, User2
FROM CoilInventory
WHERE (@PlantCode IS NULL OR Plant = @PlantCode)
  AND (@LastSyncValue IS NULL OR LastModified > @LastSyncValue)
ORDER BY LastModified, INVCOIL
OFFSET @Offset ROWS
FETCH NEXT @FetchSize ROWS ONLY

RowVersion-based (SQL Server)

Uses SQL Server's rowversion/timestamp for efficient change tracking.

SELECT 
  InvCoil AS INVCOIL, 
  Material AS MATERIAL,
  RowVersion,  -- Include for change detection
  Description, DateIn, DateOut, RemainingFt, StartingFt,
  VendorName, VendorCode, HeatNumber, PurchaseOrder,
  StoreLocation, User1, User2
FROM CoilInventory
WHERE (@PlantCode IS NULL OR Plant = @PlantCode)
  AND (@LastSyncValue IS NULL OR RowVersion > @LastSyncValue)
ORDER BY RowVersion, INVCOIL
OFFSET @Offset ROWS
FETCH NEXT @FetchSize ROWS ONLY

Configuration Settings

Setting	Description	Default
Poll Interval	How often to check for changes	00:05:00
Fetch Size	Records per batch	1000
Select Statement	Custom SQL query	Required
Change Detection Method	None, DateTime, or RowVersion	None
Change Detection Column	Column name for change tracking	-
Full Sync Interval	How often to run full sync for deletions	-

Deletion Detection

When using incremental sync (DateTime or RowVersion), deleted records won't be detected automatically. Configure Full Sync Interval to periodically run a full synchronization that will:

1. Fetch all records from the source
2. Compare with existing coils in Eclipse
3. Mark missing coils as deleted

tip

Schedule full syncs during off-peak hours to minimize performance impact.

Best Practices

1. Indexing: Ensure change detection columns are indexed for performance
2. Batch Size: Start with default (1000) and adjust based on your network and data size
3. Poll Interval: Balance between data freshness and system load
   • High-volume changes: 1-5 minutes
   • Low-volume changes: 5-15 minutes
4. Full Sync Frequency:
   • Daily for most scenarios
   • Hourly if deletions are frequent
   • Weekly if deletions are rare

Security Considerations

• SQL queries are validated to prevent injection attacks
• Only SELECT statements are allowed
• DDL/DML operations (DROP, UPDATE, DELETE, etc.) are blocked
• Parameters must be used for all dynamic values

Troubleshooting

Common Issues

"Implicit conversion from data type nvarchar to timestamp is not allowed"

• Occurs with RowVersion change detection
• Ensure your RowVersion column is properly typed in the SELECT statement

"Query must include @LastSyncValue parameter"

• Your SQL is missing the required parameter for change detection
• Add the parameter even if checking for NULL

Changes not detected

• Verify the change detection column is updated when records change
• Check that the column type matches the detection method
• For RowVersion, ensure the column is actually a rowversion/timestamp type

Example Configuration

{
  "type": "DbCoilSyncer",
  "enabled": true,
  "settings": {
    "ExternalConnectionId": "ExternalConnections/sql-server-1",
    "PollInterval": "00:05:00",
    "FetchSize": 1000,
    "SelectStatement": "SELECT InvCoil AS INVCOIL, Material AS MATERIAL, RowVersion, Description, DateIn, DateOut, RemainingFt, StartingFt, VendorName, VendorCode, HeatNumber, PurchaseOrder, StoreLocation, User1, User2 FROM vw_CoilInventory WHERE (@PlantCode IS NULL OR PlantCode = @PlantCode) AND (@LastSyncValue IS NULL OR RowVersion > @LastSyncValue) ORDER BY RowVersion OFFSET @Offset ROWS FETCH NEXT @FetchSize ROWS ONLY",
    "ChangeDetectionMethod": "RowVersion",
    "ChangeDetectionColumn": "RowVersion",
    "FullSyncInterval": "1.00:00:00"
  }
}