node-red-gbase

Node-RED nodes for querying GBase through a Windows/System ODBC DSN.

Nodes

• zgbaseodbcdb: GBase ODBC DSN query node. It can be wired directly in a flow.
• zgbaseodbc: query node kept for compatibility with the earlier split config/query shape.

The runtime follows the same basic pattern as a direct odbc test script:

const connection = await odbc.connect({ connectionString: "DSN=testdb" });
const statement = await connection.createStatement();
await statement.prepare("select tabid,tabname from systables where tabid < ?");
await statement.bind([10]);
const result = await statement.execute();

Message Compatibility

• msg.topic: SQL statement, compatible with zmysql.
• msg.query: SQL statement, compatible with zoracledb.
• msg.payload: array for ? parameters, or object for :name parameters.
• msg.bindVars: array or object of explicit bind values. Oracle input objects with val are accepted.
• msg.resultAction: single, single-meta, multi, or none.
• msg.resultSetLimit: rows per message when msg.resultAction is multi.
• msg.uppercaseResultKeys: set to false to keep the ODBC driver's original field names.

Dynamic connection configs are supported with msg.gbaseConfig, msg.odbcConfig, msg.dbConfig, or compatible msg.oracleConfig.

Result field names are uppercased by default to match the existing Oracle node output, so a GBase row like { tabid: 1, tabname: "systables" } is sent as { TABID: 1, TABNAME: "systables" }.

Bind parameters are normalized before calling ODBC:

• undefined and NaN become null.
• boolean becomes 1 or 0.
• Date becomes a local timestamp string like 2026-07-04 03:55:02.000.
• bigint becomes a string.
• plain objects become JSON strings.

For debugging, the node adds msg.gbaseBindParams, msg.gbaseBindParamTypes, and msg.gbaseStringParamLengths before execution.

When SQL has no ? or :name/:1 placeholder, msg.payload is not bound as a parameter. This avoids accidental binding when upstream nodes leave arrays in payload.

If the GBase ODBC driver rejects parameter binding with HY003 Invalid application buffer type, the node falls back to safely inlining normalized parameter literals and executing the SQL with connection.query() instead of prepared-statement bind. The message will include msg.gbaseBindFallback = true and msg.gbaseBindFallbackSql.

If prepared-statement execution fails with 22001 String data right truncation, the node also tries the same inline-literal fallback with connection.query(). The message will include msg.gbaseExecuteFallback = true and msg.gbaseBindFallbackSql. If that fallback also fails, compare msg.gbaseStringParamLengths with the target table column sizes.

For Oracle compatibility, INSERT ALL ... INTO ... VALUES ... SELECT 1 FROM DUAL is automatically rewritten before execution. A single-target statement becomes one INSERT INTO ... VALUES ...; a multi-target statement is split into multiple INSERT INTO ... VALUES ... statements and executed sequentially on the same connection. The message will include msg.gbaseSqlTransformed = true, msg.gbaseOriginalSql, msg.gbasePreparedSql, and msg.gbaseMultiStatementCount for multi-target statements.

Example with your DSN:

msg.gbaseConfig = { dsn: "testdb" };
msg.query = "select tabid,tabname from systables where tabid < ?";
msg.payload = [10];
return msg;

If the zgbaseodbcdb node already has DSN=testdb configured, the function can be just:

msg.query = "select tabid,tabname from systables where tabid < ?";
msg.payload = [10];
return msg;

Named-parameter style is also accepted:

msg.gbaseConfig = { dsn: "testdb" };
msg.query = "select tabid,tabname from systables where tabid < :maxTabId";
msg.payload = { maxTabId: 10 };
return msg;

Runtime Requirements

Install and verify the GBase ODBC driver in Windows ODBC Data Sources first, then run npm install in this plugin directory.