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, DB2, and PostgreSQL

SQL Query Requirements

Your SQL query must return at minimum these required columns:

Column Name	Type	Required	Description
INVCOIL	varchar	Yes	Unique coil identifier
MATERIAL	varchar	Yes	Material code

Column-name matching is case-insensitive, so INVCOIL, invcoil, and InvCoil are all accepted. This lets the same mapping code work across databases that fold identifier casing differently — PostgreSQL folds unquoted identifiers to lowercase, Oracle to uppercase, SQL Server preserves source casing.

SELECT * is also supported. The required columns are verified against the actual query result at config-test time, so missing columns surface with a clear "missing required column(s): X. Available: …" error rather than a SQL-string substring check.

:::tip Best Practice Prefer explicit column names over SELECT * when:

• Your source table has binary columns (e.g. SQL Server rowversion/timestamp) you don't want to materialize
• You're joining tables that share column names
• You want the smallest possible row payload over the wire :::

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
IsComplete	bit/bool	When true, marks the coil complete (expired). When false on a coil that was previously complete, the coil is reactivated and DateOut is cleared. See Expiring Coils Explicitly below.

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

Uses SQL Server's rowversion/timestamp binary column for efficient change tracking. Not available on PostgreSQL, MySQL, Oracle, or DB2 — use DateTime-based detection on those.

SELECT
  InvCoil, 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

PostgreSQL Setup

PostgreSQL has no direct equivalent of SQL Server's rowversion. The recommended pattern is a timestamptz column auto-updated by a BEFORE UPDATE trigger, paired with ChangeDetectionMethod = DateTime in the Coil Import config.

Source-side schema and trigger

-- Trigger function bumps the column on every UPDATE
CREATE OR REPLACE FUNCTION set_updated_dt()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_dt = clock_timestamp();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TABLE coils (
  invcoil       VARCHAR(32)   PRIMARY KEY,
  material      VARCHAR(64)   NOT NULL,
  description   VARCHAR(255),
  sqlplant      VARCHAR(16),
  -- ...your other columns...
  updated_dt    TIMESTAMPTZ   NOT NULL DEFAULT clock_timestamp()
);

CREATE INDEX ix_coils_updated_dt ON coils (updated_dt);
CREATE INDEX ix_coils_sqlplant   ON coils (sqlplant);

CREATE TRIGGER trg_coils_updated_dt
BEFORE UPDATE ON coils
FOR EACH ROW
EXECUTE FUNCTION set_updated_dt();

Notes:

• clock_timestamp() returns the actual wall-clock time when the trigger fires, unlike now() which is fixed for the duration of a transaction. Using clock_timestamp() prevents two rapid updates inside one transaction from landing on the same value and confusing incremental sync.
• The DEFAULT clock_timestamp() on the column means freshly inserted rows pick up a sync-friendly timestamp without needing a separate INSERT trigger.
• PostgreSQL folds unquoted identifiers to lowercase, so the columns above end up as invcoil, material, etc. Eclipse's case-insensitive column matching handles this automatically — no aliases needed.

Pagination

PostgreSQL uses LIMIT/OFFSET instead of T-SQL's OFFSET … FETCH NEXT:

SELECT
  invcoil, material, description, sqlplant,
  datein, dateout, importdate,
  vendorname, vendorcode, heatnumber, purchaseorder,
  user1, user2,
  iscomplete, startingft, remainingft, storelocation,
  updated_dt
FROM coils
WHERE (@PlantCode IS NULL OR sqlplant = @PlantCode)
  AND updated_dt > @LastSyncValue
ORDER BY invcoil
LIMIT @FetchSize OFFSET @Offset

Coil Import config values

• ChangeDetectionMethod: DateTime
• ChangeDetectionColumn: updated_dt

Connection string

Standard Npgsql format. If your coil table lives in a non-public schema, either schema-qualify the table in the SELECT (FROM ams.coils) or append Search Path so unqualified table names resolve:

Host=localhost;Port=5432;Database=eclipse_erp;Username=eclipse_user;Password=...;Search Path=ams,public

Limitations

• Schedule sync from PostgreSQL is not currently supported. The Schedule sync code path uses T-SQL-only pagination internally; pointing it at a PostgreSQL connection produces a clear "Schedule sync from PostgreSQL is not yet supported" error at config time. Coil sync, coil validation, material import, and pattern lookup all work against PostgreSQL.

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	-

Understanding Poll Interval vs Full Sync Interval

These two settings work together to control sync behavior:

• Poll Interval: How often the sync process runs
• Full Sync Interval: How much time must elapse since the last full sync before triggering another full sync

Key Rule: For incremental syncs to work, pollInterval must be less than fullSyncInterval.

Configuration	Behavior
pollInterval < fullSyncInterval	Incremental syncs with periodic full syncs
pollInterval >= fullSyncInterval	Every sync is a full sync

Example - Correct Configuration:

pollInterval: 00:05:00      # Check for changes every 5 minutes
fullSyncInterval: 06:00:00  # Run full sync every 6 hours

This will run incremental syncs every 5 minutes (fetching only changed records), with a full sync every 6 hours to detect deletions.

Example - Incorrect Configuration:

pollInterval: 01:00:00      # Check every 1 hour
fullSyncInterval: 00:02:00  # Full sync threshold is 2 minutes

warning

This configuration causes every sync to be a full sync because 1 hour elapsed is always greater than the 2-minute threshold. If you want incremental syncs, ensure pollInterval is shorter than fullSyncInterval.

Expiring Coils

Eclipse supports two ways to expire a coil — pick whichever fits your source system better.

Expiring Coils Explicitly (via IsComplete)

The recommended approach is to return an IsComplete column in your SELECT statement. This gives you immediate, explicit control over coil lifecycle and does not require a full sync to take effect.

• Return IsComplete = 1 (or true) for expired coils → Eclipse marks the coil MarkedComplete on the next sync
• Return IsComplete = 0 (or false) for active coils
• If a previously expired coil comes back with IsComplete = 0, Eclipse reactivates it and clears DateOut

SELECT
  InvCoil, Material,
  CASE WHEN DateOut IS NOT NULL THEN 1 ELSE 0 END AS IsComplete,
  DateOut,
  Description, DateIn, RemainingFt, StartingFt, ...
FROM CoilInventory
WHERE (@PlantCode IS NULL OR Plant = @PlantCode)
ORDER BY InvCoil
OFFSET @Offset ROWS
FETCH NEXT @FetchSize ROWS ONLY

In this example, any row whose source table has a DateOut value will be expired in Eclipse on the next sync. The source row itself is still returned by the query, so Eclipse retains its other fields (remaining footage, vendor, etc.).

tip

Returning DateOut alongside IsComplete lets Eclipse populate the coil's consumption date from your source system rather than using the sync time.

Expiring Coils Implicitly (Purge Expired Coils)

If you can't return an IsComplete column — for example, the source system simply deletes finished coils — enable the Purge Expired Coils setting. Eclipse will then expire coils that disappear from the query result set.

A coil is expired implicitly by causing it to stop appearing in the result set of your SELECT statement, either by:

1. Deleting the row from the source table, or
2. Filtering it out in the SELECT's WHERE clause (for example, WHERE Active = 1 or WHERE DateOut IS NULL)

:::warning Common Misconception Simply updating columns like DateOut or status flags on the source row will not expire the coil under Purge Expired Coils — Eclipse only notices what the query returns. Either filter those rows out in your WHERE clause, or use the explicit IsComplete approach above. :::

What "Purge" Means

Despite the name, "Purge Expired Coils" does not delete any rows — neither from the external SQL table nor from Eclipse's database. When a coil is purged (whether via IsComplete or by disappearing from the query), Eclipse:

• Sets MarkedComplete = true on the coil
• Sets DateOut to the current time

The coil record is retained in Eclipse for historical and reporting purposes; it is simply no longer treated as active inventory.

Purge Rules

For a coil to be purged, all of the following must be true:

1. Purge Expired Coils is enabled in the Coil Import config
2. The current sync is a full sync (controlled by Full Sync Interval — see above)
3. At least two full syncs have completed since the service started. The first full sync establishes a baseline; purging only happens on the second (and later) full syncs.
4. The coil has been missing from the source for two consecutive full syncs. A coil that disappears from the query is given one full-sync cycle of grace before being marked complete — this prevents transient query failures or WHERE-clause tweaks from accidentally expiring active inventory.
5. The coil is not already marked complete

:::tip Testing Expiration To verify the feature is working:

1. Confirm FullSyncInterval is configured and has elapsed at least twice
2. Remove a test coil from your source query's result set
3. Wait for two full sync cycles (not two poll cycles) to complete
4. Check the coil in Eclipse — it should now be marked complete with a DateOut of the purge time

Check the Eclipse Server log for messages like Marking coil X as complete (missing from source for 2+ full syncs...) to confirm purge activity. :::

:::tip Performance Schedule full syncs during off-peak hours to minimize performance impact. Purge runs are capped at 1,000 coils per full sync cycle. :::

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

"Query result is missing required column(s): X. Available: …"

• The configured SELECT didn't return one or more of INVCOIL, MATERIAL, or your ChangeDetectionColumn. The error lists what's missing alongside what came back, so you can check for typos or aliasing problems against the available column list.
• Detected during config save / connection test, not at runtime — a misconfigured query will fail fast.

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

• Occurs with RowVersion change detection on SQL Server
• 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
• For PostgreSQL, confirm the BEFORE UPDATE trigger fires on every change to the source table

"relation "coils" does not exist" (PostgreSQL)

• Your table is in a non-public schema and the connection's search_path doesn't include it
• Schema-qualify the table (FROM ams.coils) or add Search Path=ams,public to the connection string

"Schedule sync from PostgreSQL is not yet supported"

• Schedule sync (a separate import type) doesn't yet support PostgreSQL. Coil sync, coil validation, material import, and pattern lookup do.

Example Configuration

{
  "type": "DbCoilSyncer",
  "enabled": true,
  "settings": {
    "ExternalConnectionId": "ExternalConnections/sql-server-1",
    "PollInterval": "00:05:00",
    "FetchSize": 1000,
    "SelectStatement": "SELECT InvCoil, 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"
  }
}