Please note: Version 2.0 agent service and configuration are NOT compatible with 1.x!!!

1.x user guide: https://help.fxiaoke.com/9bfb/c68f/0540/191e

1. Features

2.3 1. Reduced code size 2. BigQuery detection support

2.2 1. BigQuery detection support

2.1 Features: 1. Support pagination for SqlServer2008 when using offset

New in 2.0: 1. Data can transmit special characters such as single quotes without special handling 2. No offset queries; supports composite primary keys and can use indexes 3. No offset queries; supports ns, ms, s time precision levels and can use indexes 4. Supports polling schedules such as daily polling, per-day incremental, or full sync 5. Supports safe usage of custom functions in DB middleware queries 6. Support record invalidation (soft delete) 7. Support querying invalidated records

2. Deployment

The installation package includes a deployment instruction document; open it for details.

Test deployment

After successful deployment, test the agent on the proxy server by visiting these URLs. From an intranet host, replace localhost with the proxy server IP. From public internet, replace with public IP + port or domain.

API path (for connection configuration): http://localhost:8080/dbadapt/proxy/api

Test paths: http://localhost:8080/test; http://localhost:8080/dbadapt/proxy/api/test

Admin console: http://localhost:8080/admin

3. Configuration

To support the features above, the new configuration changed significantly. Front-end configuration UI requires later updates.

Connection configuration

Agent service access URL: the publicly accessible API path of the deployed service, e.g.: https://dbproxy.example.com/dbadapt/proxy/api or http://[PUBLIC_IP]:[PORT]/dbadapt/proxy/api

If the access URL is correct and the Integration Platform service can reach it, the agent will report “running” and display the program version. Otherwise it will report an error — check agent deployment and network connectivity.

Data source: select the source

Username and password: configured in db.setting inside the installation package.

API configuration

Navigation: Connector -> Connected Object -> API Configuration -> SQL Configuration

Configuration details: For each object (Primary Object + Sub-object), place unified YAML-formatted configuration into the Batch Query box. The content must start with #YAML (no leading spaces). Put all object configurations into this single box.

SQL execution logic: Use ${} in SQL to denote variables. For different API calls, the Integration Platform will supply appropriate variables.

SQL configuration fields

querySql: - Single entry. The SQL to query. ${__where} will be replaced depending on the scenario. (Only Primary Object queries will replace ${__where}; Sub-object queries must reference fields that relate to the Primary Object.)

batchWhere: - Multiple entries. Conditions used during batch queries; typically include time range and parameters like lastMaxId.

idWhere: - Single entry. SQL used when querying by id; parameters include id.

insertSql: - Single entry. SQL executed on insert; parameters include all object fields.

updateSql: - Single entry. SQL executed on update; parameters include all object fields.

queryIdSql: - Single entry. SQL to retrieve id after insert or update. If an auto-increment primary key exists, this can be left empty.

invalidSql: - Single entry. SQL executed to invalidate (soft delete) a single record; parameters include the record id.

invalidRelationSqls: - Multiple entries. SQLs to invalidate related records; used when invalidating a Primary Object and needing to invalidate detail/Sub-object rows.

Example configuration — Primary Object:

YAML

# When time precision is ms querySql: select * from test2 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id # Skipped if lastMaxId is null - date > ${startTime__d} and date <= ${endTime__d} order by date,id idWhere: - id = ${dataId}::INTEGER insertSql: | INSERT INTO test2 (“name”, “date”) VALUES(${name}, ${date__d}); updateSql: | UPDATE test2 SET name=${name}, date=${date__d} WHERE id=${id}::INTEGER; invalidSql: | UPDATE test2 SET is_deleted=true WHERE id=${id}::INTEGER invalidRelationSqls: | UPDATE test2-1 SET is_deleted=true WHERE pid=${id}::INTEGER

Sub-object example (assuming sub-object links to primary via parent_id; ${dataId} will be replaced by current Primary Object dataId):

YAML

querySql: select * from test2-d1 where parent_id = ${dataId}

Per-API variable rules

Variable types: - Date fields: append _d to the field key to mark date/time. The agent recognizes date/time types and passes date/time parameters to JDBC accordingly. - id field types: includes batch query lastMaxId and id-based dataId fields. The agent can provide separated array results. __dot means dot-separated (.), __dashed means dash-separated (-), __underline means underscore-separated ().

Batch query parameters:

Query by id:

Insert or update:

Scenario-based SQL examples

Note: Indexing — create composite indexes matching the ORDER BY sequence in the examples below.

No-offset range query by id + high-precision timestamp (precision finer than ms, e.g., ns)

YAML

# When time precision < ms querySql: select * from test1 where ${__where} batchWhere: # For precision finer than ms, query the last 1ms slice of the previous run - date >= ${startTime__d} and date < ${plus1StartTime__d} and id > ${lastMaxId} order by date,id # Main range query: > startTime and <= endTime - date >= ${plus1StartTime__d} and date < ${plus1EndTime__d} order by date,id

No-offset range query by id + millisecond timestamp

YAML

# When time precision is ms querySql: select * from test2 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id # Skipped if lastMaxId is null - date > ${startTime__d} and date <= ${endTime__d} order by date,id idWhere: - id = ${dataId}::INTEGER

No-offset range query by id + low-precision timestamp (precision coarser than ms, e.g., seconds)

YAML

# When time precision > ms querySql: select * from test3 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id - date > ${startTime__d} and date <= ${endTime__d} order by date,id idWhere: - id = ${dataId}::INTEGER

Range query where date field is date-only (no time) 1. Uses offset query 2. Business must ensure data in the queried time range does not change during the window to avoid missing data 3. Because each run queries a single day, reduce frequency appropriately (for example, every 2 hours)

YAML

# Date-only querySql: select * from test5 where ${__where} batchWhere: # Range query supports historical runs; avoid querying previous day when crossing midnight - date1 > ${startTime__d0} and date1<=${endTime__d0} order by date1,id # Equal sign used to query current day - date1 = ${endTime__d0} order by date1,id idWhere: - id = ${dataId}::INTEGER

Range query for SqlServer2008 where date field is a string in yyyyMMdd 1. Uses offset query 2. Business must ensure data in the queried time range does not change to avoid missing data 3. Set isSqlServer2008=true in agent DB config 4. Reduce frequency because each run queries a day (for example, every 2 hours)

YAML

# Date-only as string querySql: select * from test5 where ${__where} batchWhere: - date1 > CONVERT(VARCHAR,${startTime__d0},112) and date1<= CONVERT(VARCHAR,${endTime__d0},112) order by date1,id - date1 = CONVERT(VARCHAR,${endTime__d0},112) order by date1,id idWhere: - id = ${dataId}::INTEGER

No-offset, range query with composite primary keys + timestamp

YAML

# Composite primary key combined with ‘-‘ as separator querySql: select name1||’-‘||name2 as name,* from test4 where ${__where} batchWhere: - date = ${startTime__d} and name1 = ${lastMaxId__dashed[0]} and name2 > ${lastMaxId__dashed[1]} order by date,name1,name2 - date = ${startTime__d} and name1 > ${lastMaxId__dashed[0]} order by date,name1,name2 - date > ${startTime__d} and date <= ${endTime__d} order by date,name1,name2 idWhere: - name1 = ${dataId__dashed[0]} and name2 = ${lastMaxId__dashed[1]}

Insert and update

YAML

insertSql: | INSERT INTO test2 (“name”, “date”) VALUES(${name}, ${date__d}) updateSql: | UPDATE test2 SET name=${name}, date=${date__d} WHERE id=${id}::INTEGER

Upsert example for SQL Server (check insert or update using MERGE)

Primary Object configuration:

YAML

# Insert and update for SQL Server insertSql: | insert into t7 (id, name, “date”) values (${id}, ${name}, ${date__d}); updateSql: | MERGE INTO t7 AS target USING (VALUES (${id}, ${name}, ${date__d}) ) AS source (id, name, date) ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.name = source.name, target.date = source.date WHEN NOT MATCHED THEN INSERT (id, name, date) VALUES (source.id, source.name, source.date); queryIdSql: | select ${id}

Sub-object configuration:

YAML

# Insert and update for SQL Server insertSql: | insert into t7_1 (id, pid, name, “date”) values (${id}, ${pid}, ${name}, ${date__d}); updateSql: | MERGE INTO t7_1 AS target USING (VALUES (${id}, ${pid}, ${name}, ${date__d}) ) AS source (id, pid, name, date) ON target.id = source.id WHEN MATCHED THEN UPDATE SET target.pid = source.pid, target.name = source.name, target.date = source.date WHEN NOT MATCHED THEN INSERT (id, pid, name, date) VALUES (source.id, source.pid, source.name, source.date); queryIdSql: | select ${id}

Custom function call:

Example usage:

/** * @author admin * @codeName sql * @description sql * @createTime 2024-05-23 */

def type = “dbProxyExecutor” def dcId = “66193e6e47b33f000110e9ae” // Query returns single value def params = [“dcId”:dcId, “executeType”:”sql_query_string”, “body”:[ “sql”:”select count(*) from customers” ] ] def arg = [“type”:type, “params”:Fx.json.toJson(params)] def (Boolean error, HttpResult result, String errorMessage) = Fx.proxy.callAPI(“erp.syncData.executeCustomFunction”, [:], arg) log.info(result) log.info(“result1=”+result.content[‘data’][‘body’])

// Query returns data list params = [“dcId”:dcId, “executeType”:”sql_query”, “body”:[ “sql”:”select * from customers limit 2” ] ] arg = [“type”:type, “params”:Fx.json.toJson(params)] (error,result,errorMessage) = Fx.proxy.callAPI(“erp.syncData.executeCustomFunction”, [:], arg) log.info(result) log.info(“result2=”+result.content[‘data’][‘body’])

// Execute update params = [“dcId”:dcId, “executeType”:”sql_execute”, “body”:[ “sql”:’'’UPDATE public.customers SET “name”=’Cheng Xiu 222’ WHERE id=68;’’’ ] ] arg = [“type”:type, “params”:Fx.json.toJson(params)] (error,result,errorMessage) = Fx.proxy.callAPI(“erp.syncData.executeCustomFunction”, [:], arg) log.info(result) log.info(“result2=”+result.content[‘data’][‘body’])

Installation package

If no special requirements exist, install using the latest package. The ZIP includes the agent program, deployment, and usage instructions.

db-proxy-server2.0.zip — 44.9 MB https://saas.bk-cdn01.com/t/e77d24d5-3d21-4bbc-8ec5-886214579a51/u/94794ae2-e207-45f4-ad4c-20efe8d21ba1/1721130328403/db-proxy-server2.0.zip

db-proxy-server-2.1.zip — 52.0 MB https://saas.bk-cdn01.com/t/e77d24d5-3d21-4bbc-8ec5-886214579a51/u/94794ae2-e207-45f4-ad4c-20efe8d21ba1/1742383414413/db-proxy-server-2.1.zip

db-proxy-server2.3.zip — 52.1 MB https://saas.bk-cdn01.com/t/e77d24d5-3d21-4bbc-8ec5-886214579a51/u/94794ae2-e207-45f4-ad4c-20efe8d21ba1/1762155733789/db-proxy-server2.3.zip