DB Connector 2.x Operations Manual

Note: Version 2.0 is NOT backward compatible with version 1.x proxy services and configurations!!!

I. Feature Highlights

2.1 Features:

1. Supports pagination for SQL Server 2008 when using OFFSET

2.0 New Features:

1. Transmits special characters like single quotes without requiring special handling

2. Eliminates OFFSET queries, supports multi-primary keys, and leverages indexes

3. Eliminates OFFSET queries, supports queries with varying precision (ns, ms, s), and leverages indexes

4. Supports polling scenarios (e.g., daily polling with full or incremental queries)

5. Enables secure custom function calls via DB middleware

6. Supports record invalidation

7. Supports querying invalidated data

II. Deployment Guide

The installation package includes a deployment guide. Refer to it directly for details.

Test Deployment

After successful deployment, access the following URLs from the proxy server for testing. Replace localhost with the proxy server’s internal IP for intranet access or the public IP/domain for external access.

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

• Test URLs: http://localhost:8080/test; http://localhost:8080/dbadapt/proxy/api/test

• Admin Console: http://localhost:8080/admin

III. Configuration Guide

To support the new features, version 2.0 introduces significant configuration changes. Frontend configuration updates will follow in subsequent releases.

Connection Configuration

• Proxy Service URL: Publicly accessible API endpoint (e.g., https://dbproxy.example.com/dbadapt/proxy/api or http://[PublicIP]:[Port]/dbadapt/proxy/api).

  • If the URL is correct and the Integration Platform can access it, the system will display “Proxy service operational” with the version number. Otherwise, check the proxy deployment and network connectivity.

• Data Source: Select from available options.

• Username & Password: Credentials configured in the db.setting file during deployment.

picture coming soon:

API Configuration

• Navigation: Connector > Connection Object > API Configuration > SQL Configuration

• Configuration Format: For each object (Primary + Sub-object), enter YAML-formatted content in the Batch Query field. Prefix with #YAML (no leading spaces). All configurations must be consolidated in this field.

picture coming soon:

SQL Execution Logic: Variables in SQL are marked with ${}. The Integration Platform dynamically substitutes values based on the API context.

SQL Configuration Details

• querySql:

  • Single entry. The query SQL. ${__where} is replaced with scenario-specific WHERE clauses (Primary Object only; Sub-objects must reference Primary Object fields).

• batchWhere:

  • Multiple entries. Conditions for batch queries (e.g., time ranges). Parameters include timestamps and lastMaxId.

• idWhere:

  • Single entry. SQL for ID-based queries. Parameters include dataId.

• insertSql:

  • Single entry. SQL for record insertion. Parameters include all data fields.

• updateSql:

  • Single entry. SQL for record updates. Parameters include all data fields.

• queryIdSql:

  • Single entry. SQL to retrieve the ID field after insert/update. Optional for tables with auto-increment keys.

• invalidSql:

  • Single entry. SQL to invalidate a record. Parameters include dataId.

• invalidRelationSqls:

  • Multiple entries. SQL to cascade invalidation to related records (e.g., invalidating detail records when a master record is invalidated).

Example Configurations:

Primary Object:
yaml #YAML # For millisecond precision querySql: select * from test2 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id # Skip if lastMaxId is empty - 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 (assuming parent_id links to Primary Object):
yaml #YAML querySql: select * from test2-d1 where parent_id = ${id} idWhere: | id = ${dataId}

API-Specific Configuration Notes

Variable Types:
- Dates: Append __d to field keys. The proxy converts these to datetime types for JDBC.
- ID Formats: For array results, use:
- __dot: . delimiter
- __dashed: - delimiter
- __underline: _ delimiter

Batch Query Parameters:

ID-Based Query Parameters:

Insert/Update Parameters:

Scenario-Specific SQL Examples

Indexing Tip: Create composite indexes matching the ORDER BY clauses in these examples!

Non-OFFSET Range Query: ID + Datetime (Precision > ms, e.g., ns)

yaml #YAML # For sub-millisecond precision querySql: select * from test1 where ${__where} batchWhere: # Query last 1ms of prior results when precision exceeds ms - date >= ${startTime__d} and date < ${plus1StartTime__d} and id > ${lastMaxId} order by date,id # Main query: > startTime and <= endTime - date >= ${plus1StartTime__d} and date < ${plus1EndTime__d} order by date,id

Non-OFFSET Range Query: ID + Datetime (Millisecond Precision)

yaml #YAML # For millisecond precision querySql: select * from test2 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id # Skip if lastMaxId is empty - date > ${startTime__d} and date <= ${endTime__d} order by date,id idWhere: - id = ${dataId}::INTEGER

Non-OFFSET Range Query: ID + Datetime (Precision < ms, e.g., Seconds)

yaml #YAML # For precision coarser than ms querySql: select * from test3 where ${__where} batchWhere: - date = ${startTime__d} and id > ${lastMaxId} order by date,id # Skip if lastMaxId is empty - date > ${startTime__d} and date <= ${endTime__d} order by date,id idWhere: - id = ${dataId}::INTEGER

Range Query: Date-Only Field (No Time Component)

1. Uses OFFSET queries.

2. Ensure data remains static during query windows to avoid omissions.

3. Reduce polling frequency (e.g., every 2 hours).

yaml #YAML # Date-only field querySql: select * from test5 where ${__where} batchWhere: # Historical data query (exclude prior day during cross-day queries) - date1 > ${startTime__d0} and date1<=${endTime__d0} order by date1,id # Current day query - date1 = ${endTime__d0} order by date1,id idWhere: - id = ${dataId}::INTEGER

Range Query: SQL Server 2008, String Date Field (yyyyMMdd Format)

1. Uses OFFSET queries.

2. Ensure data remains static during query windows.

3. Set isSqlServer2008=true in the proxy’s DB config.

4. Reduce polling frequency (e.g., every 2 hours).

yaml #YAML # Date-only field querySql: select * from test5 where ${__where} batchWhere: # Historical data query (exclude prior day during cross-day queries) - date1 > CONVERT(VARCHAR,${startTime__d0},112) and date1<= CONVERT(VARCHAR,${endTime__d0},112) order by date1,id # Current day query - date1 = CONVERT(VARCHAR,${endTime__d0},112) order by date1,id idWhere: - id = ${dataId}::INTEGER

Non-OFFSET Range Query: Multi-Primary Key + Datetime

yaml #YAML # Multi-primary key (delimited by "-") 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 #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 (Insert or Update) - SQL Server Example

Primary Object:
yaml #YAML # SQL Server upsert 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:
yaml #YAML # SQL Server upsert 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 Calls

Example:

```groovy 
/** 
* @author Admin 
* @codeName sql 
* @description sql 
* @createTime 2024-05-23 
*/
def type = “dbProxyExecutor” 
def dcId = “66193e6e47b33f000110e9ae” 
// Query returning a 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 returning a 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”=’程秀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

For general use, install the latest version. The ZIP includes the proxy service, deployment guide, and usage instructions.

db-proxy-server2.0.zip (44.9 MB)

db-proxy-server-2.1.zip (52.0 MB)

db-proxy-server2.2.zip (45.0 MB)