Auxiliary Data Value Pull-Back: A Practical Strategy from Kingdee Cloud to MySQL

What This Strategy Solves

In projects where Kingdee Cloud (Kingdee Cloud Cosmic, a mainstream Chinese ERP) serves as the back-end ERP, the front-end business database (here, a MySQL business library) often needs a mirror copy of the ERP's auxiliary data set values — things like codes, names, descriptions, and forbidden statuses under custom categories. If the business side connects directly to Kingdee's WebAPI, every new query scenario requires re-implementing authentication and pagination logic. Once this pull-back pipeline is modeled as a standalone strategy on the Qeasy data integration platform, the business side only queries MySQL, and code-to-name lookups live in a single, centrally managed table.

Data Flow and Field Mapping

Overall direction: Kingdee Cloud (source, executeBillQuery) → Qeasy Integration Platform (middle layer, field mapping and deduplication) → MySQL (target, UPSERT write).

Key field mapping:

Business Meaning	Kingdee Cloud Field	MySQL Field	Notes
Entry primary key	FEntryID	FEntryID	Unique identifier, used for idempotent writes
Code	FNumber	FNumber	Code under the category
Name	FDataValue	FDataValue	Display name for the code
Description	FDescription	FDescription	Business note
Category ID	FId	FId	Owning auxiliary data category
Category name	FId_fname	FId_fname	Human-readable category name
Forbidden status	FForbidStatus	FForbidStatus	Whether disabled
Document status	FDocumentStatus	FDocumentStatus	Audit / create status

We deliberately pull back FId together with FId_fname, so the business side can filter by name without an additional join.

How to Configure in Qeasy

Source configuration: Select the Kingdee Cloud platform, use the executeBillQuery API (POST, QUERY semantics). Take FEntryID as the primary key field, FId_fname as the business number field. Enable idCheck, disable buildModel. This returns a flat, structured result set that is straightforward to persist.

Target configuration: Select the MySQL platform, use the execute API (POST, EXECUTE semantics). The main_sql is an INSERT ... ON DUPLICATE KEY UPDATE UPSERT; main_params uses named-parameter binding. Keep placeholders such as :FEntryID in the value field — Qeasy binds them automatically by name.

Scheduling: Source strategy cron is 3 2 * * *, target strategy cron is 23 2 * * *, separated by about 20 minutes so the target writer doesn't fire before the source query finishes.

Implementation Steps

Incremental start point. Before going live, identify the maximum FEntryID in Kingdee Cloud and use it as the initial filter FEntryID > {startId}. Add a "start point parameter" step in the write strategy so the first run is full and subsequent runs are incremental.
Full-volume trigger. In Qeasy, enable the strategy's isFirst flag to pull historical data once, then immediately disable it to prevent the next scheduled run from doing another full pull.
Scheduling frequency. Auxiliary data set values change infrequently; daily scheduling is enough. For categories that need near-real-time freshness, create a separate minute-level strategy. Keep code mappings centrally managed in the platform.
Validation. Add an assertion strategy that compares COUNT(*) on the MySQL side against the Kingdee query total. Raise an alert when the delta is non-zero.

Lessons from the Trenches

A common mistake is treating FId_fname as just another string field. It is actually the "display field" of the category in Kingdee. Without a proper index on the target table, filtering by name becomes a full-table scan. We usually create a composite unique index on (FId, FNumber) so the UPSERT behaves correctly.
executeBillQuery pagination can silently miss rows. Kingdee's pagination is row-number-based. If new rows are inserted mid-pull, a naive offset-based pagination will skip records. The safe route is the FEntryID incremental condition with sorting, rather than pure pagination.
ON DUPLICATE KEY UPDATE requires a primary key or unique key. If the target table has no unique constraint, the UPSERT degrades into repeated INSERTs and rows are duplicated. Before going live we always run SHOW INDEX to confirm.
Status fields get overwritten. If the target business system manually edits FForbidStatus, the next UPSERT overwrites it with the original Kingdee value. The fix is to only update fields that genuinely change in the ON DUPLICATE KEY UPDATE clause, leaving manual edits intact.
Cross-timezone scheduling drift. In private deployments the server timezone does not always match the database timezone. Cron expressions should be written in UTC and the timezone explicitly marked inside Qeasy to avoid off-by-hours surprises in the early-morning runs.

Where It Fits and Where It Doesn't

Fits: Auxiliary data set values, custom categories, document status dictionaries — low-frequency, code-to-name translation scenarios on the front end. Does not fit: High-concurrency writes, or document bodies that require transactional consistency (such as sales order header/line bodies). Those belong in a document sync strategy, not an auxiliary data pull-back.