SQL Server to Cloud Code 2.0: Dealer Save Sync Strategy Tutorial

What This Strategy Solves

Dealer master data is the foundation for downstream scenarios such as QR-code scanning, anti-channel-leakage, and rebate settlement. In one retail project, we used Qeasy (Qingyiyun Data Integration Platform) to push dealers from an SQL Server core view into the /admin-api/core/dealer/save-erp endpoint of Cloud Code 2.0, where the target system stores records idempotently by code. This single strategy owns the dealer archive sync. It looks trivial, but if the code mapping is wrong, the two sides drift apart within three months.

Data Flow and Field Mapping

The chain is one-way: SQL Server (A) → Qeasy middleware → Cloud Code 2.0 (B).

The source side executes main_sql via a WebAPI(POST), filtering the vw_cus_core_dealer_erp view by FFORBIDSTATUS='A' and approval time within the last 3 hours, returning code and name. The Qeasy middleware does not perform heavy cleansing; it handles idempotency keys, retries, and dispatch. The target side also uses WebAPI(POST) against /admin-api/core/dealer/save-erp, writing code and name through, idempotent on code.

Key field mapping:

Note: the SQL contains 1=:create_date, where :create_date is bound through the main_params object in Qeasy. This is the typical way to parameterize the time window instead of hardcoding it.

How to Configure in Qeasy

In Qeasy, this strategy is represented as one "integration strategy" node. Key configuration points:

• Source platform: SQL Server adapter, API type select, effect=QUERY, method=POST;
• Request parameters: pass a placeholder object via main_params, paste main_sql under otherRequest, and use Qeasy built-in time functions like {{HOURE_AGO_3|datetime}} directly in SQL;
• Response fields: declare code and name, keep buildModel=false, enable autoFillResponse so Qeasy auto-builds the response schema;
• Target platform: Cloud Code 2.0 adapter, API /admin-api/core/dealer/save-erp, effect=EXECUTE, idCheck=true, number=code, id=code;
• Mapping: {{code}} → code, {{name}} → name; no other fields;
• Retry and dedup: enable dedup by code, retry 3 times on failure to avoid duplicate writes.

Centralized code mapping is a common Qeasy pattern: register idempotency keys such as code in a single mapping table so other strategies (salespeople, price lists) reuse the same definition rather than re-declaring it.

Implementation Steps

We typically proceed in three phases: connect first, validate second, stabilize last.

1. Incremental start: by default the strategy is cron-triggered. Source cron is 1 * * * *, target cron is offset by two minutes (3 * * * *) to avoid contention.
2. Full sync trigger: before first go-live, manually run a full sync to backfill historical dealers. After it completes, shrink the window back to HOURE_AGO_3 and stay incremental.
3. Scheduling frequency: keep hour-level incremental in steady state. During dealer onboarding peaks, temporarily shrink the window, then restore it once the spike passes.

The "incremental + full backup" dual-track approach is widely used among Qeasy customers: full sync handles the initial backfill, incremental keeps daily deltas stable.

Pitfalls

1. Missing colon on placeholder. Writing 1=create_date instead of 1=:create_date silently breaks parameter binding. Always declare the placeholder in main_params as well.
2. Wrong time field for the incremental window. The window must bind to the approval time, not the last-modified time, or unapproved dirty data leaks downstream. A common mistake is using FMODIFYDATE instead of FAPPROVEDATE.
3. Idempotency key collision across namespaces. The source's code is a primary key; the target's code is a business code. Different namespaces require an explicit rename mapping in Qeasy, not raw passthrough.
4. Missing status filter. Forgetting FFORBIDSTATUS='A' pushes forbidden dealers downstream, producing the spooky "record exists but unusable" error in scanning flows.
5. Source and target crons collide. Identical cron expressions cause the target to read while the source is still writing. Offset source and target by 2–3 minutes.

When to Use and When Not to

Use it when: dealer archives change at hourly pace, the source is an SQL Server view, the target exposes an idempotent save-erp-style endpoint, and only one-way sync is required. Do not use it when: bidirectional sync is needed, transactional consistency is required (inventory, orders), or the source has no stable approval-time field for the incremental window.

About Qeasy (Qingyiyun Data Integration Platform)

Qeasy supports private deployments and connects SQL Server, mainstream ERPs, and SaaS platforms via WebAPI, WebService, or direct database links. The source SQL, time-function placeholders, and target idempotent endpoints in this article can all be orchestrated as no-code "integration strategies" in Qeasy, ideal for IT teams that need minimal disruption.