V2NodesODBC
ODBC
V2

ODBC

Provides integration with ODBC data sources so you can query data and write rows from a Workflow.

Revision History

0.0.0.2 - Initial release.

Connection

Use the Connection to choose how the ODBC connection string is built and to control template discovery and SQL identifier handling.

Property Type Description
Connection Mode OdbcConnectionMode Selects how the Connection builds the ODBC connection string.
DSN String The ODBC Data Source Name configured outside Flowgear on the Local Runtime machine when Connection Mode is Dsn.
Driver String The ODBC driver name to use when Connection Mode is DriverServerDatabase.
Server String The database server or host name when Connection Mode is DriverServerDatabase.
Port Integer Optional server port when the selected driver supports a Port keyword.
Database String Optional database or catalog name when Connection Mode is DriverServerDatabase.
User ID String Optional user identifier for DSN or driver-based connections when the target database requires credentials.
Password Masked Optional password for DSN or driver-based connections when the target database requires credentials.
Connection String Masked Raw ODBC connection string used when Connection Mode is RawConnectionString.
Additional Connection String Properties Masked Optional extra connection string properties, one keyword=value pair per line.
Default Schema String Optional default schema used when a method call leaves Schema blank.
Schema Filter String Optional schema filter used during template discovery.
Select SQL String SQL template used to discover table metadata for templates and write methods.
Schema Table Separator String Separator placed between schema and table names when qualified identifiers are built.
Identifier Open Encapsulator String Optional override for the driver's opening identifier encapsulator.
Identifier Close Encapsulator String Optional override for the driver's closing identifier encapsulator.
Parameter Prefix String Reserved for future advanced driver profile behavior. Portable ODBC v1 query text still uses positional ? placeholders.
Allow Named Parameters Boolean Reserved for future advanced driver profiles. It does not enable ad hoc named SQL placeholders in portable ODBC v1 queries.
Use Integrated Security Boolean Adds a generic integrated security hint when Connection Mode is DriverServerDatabase and the driver recognizes it.
Encrypt Boolean Adds a generic Encrypt hint when Connection Mode is DriverServerDatabase and the driver recognizes it.
Trust Server Certificate Boolean Adds a generic TrustServerCertificate hint when Connection Mode is DriverServerDatabase and the driver recognizes it.
Connection Timeout Integer Time in seconds to wait while establishing the connection. The minimum value is 15.

Setup Notes

ODBC behavior depends on the installed driver and its metadata support. Configure and test the Connection on the same Local Runtime machine or cluster that will run the Workflow.

Dsn

Use Dsn when the required ODBC DSN already exists on the runtime machine.

A DSN, or Data Source Name, is an ODBC data source definition that is created outside Flowgear. It normally comes from your database or infrastructure team, or you create it on the Local Runtime machine by using the operating system's ODBC data source administration tools for the installed driver.

  • Enter DSN.
  • Use the exact DSN name defined on the Local Runtime machine. Flowgear does not create or manage the DSN entry for you.
  • Enter User ID and Password if the DSN requires credentials.
  • Use Additional Connection String Properties only for extra driver-specific settings.

DriverServerDatabase

Use DriverServerDatabase when you want Flowgear to build the connection string from the driver and server details.

  • Enter Driver and Server.
  • Enter Port and Database if the driver uses them.
  • Enter User ID and Password, or enable Use Integrated Security if the driver supports it.
  • Set Encrypt and Trust Server Certificate as needed for your database environment.

RawConnectionString

Use RawConnectionString for advanced cases where you need to supply the full ODBC connection string yourself.

  • Enter Connection String.
  • Do not also populate DSN, Driver, Server, Port, or Database.

Discovery Settings

These settings help when the driver needs explicit metadata configuration.

  • Leave Schema blank in a method call to use Default Schema.
  • Use Schema Filter to narrow template discovery to a specific schema.
  • Select SQL must contain either {qualifiedTable} or {schema}{table}.
  • Do not include identifier encapsulators directly in Select SQL.

Methods

The ODBC Node exposes methods for running SQL queries and for inserting, updating, or deleting rows in a target table.

Query

Executes a query against an ODBC data source and streams the first result set. Use positional ? placeholders in the SQL text, and pass external values through Parameters instead of concatenating them into the query.

Parameter Type Description
Connection Connection The ODBC Connection.
Query String The SQL statement to execute.
Parameters Array Optional parameter items bound in ascending Ordinal order. Each item can include Ordinal, optional Name, Value, and optional DbType.
Return Type Description
Items Array The rows returned by the first result set. Each output item is an object keyed by column name.

Insert

Inserts one or more JSON rows into a target ODBC table without updating existing rows.

Parameter Type Description
Connection Connection The ODBC Connection.
Items Array The rows to insert. Each item should be an object whose property names match the target column names.
Schema String The target schema name. Leave it blank to use Default Schema from the Connection.
TableName String The exact target table name.
BatchSize Integer Number of rows processed per batch window. The default is 100.
Return Type Description
RowsAffected Integer The total number of rows affected by the insert operation.

Update

Updates one or more JSON rows in a target ODBC table without inserting missing rows.

Parameter Type Description
Connection Connection The ODBC Connection.
Items Array The rows to update. Each item must include every field named in KeyFields and at least one non-key column to update.
Schema String The target schema name. Leave it blank to use Default Schema from the Connection.
TableName String The exact target table name.
KeyFields String Comma-delimited exact key field names used to match existing rows.
BatchSize Integer Number of rows processed per batch window. The default is 100.
Return Type Description
RowsAffected Integer The total number of rows affected by the update operation.

Delete

Deletes one or more rows from a target ODBC table by exact key field values only.

Parameter Type Description
Connection Connection The ODBC Connection.
Items Array The delete items that supply the values for the fields named in KeyFields.
Schema String The target schema name. Leave it blank to use Default Schema from the Connection.
TableName String The exact target table name.
KeyFields String Comma-delimited exact key field names used in the delete predicate.
BatchSize Integer Number of rows processed per batch window. The default is 100.
Return Type Description
RowsAffected Integer The total number of rows affected by the delete operation.

Usage Notes

  • This Node runs on LocalRuntime, so the required ODBC drivers and any DSNs must be installed on the runtime machine.
  • Query uses positional ? placeholders only. Ad hoc named placeholders such as @Id, :Id, and $Id are not supported in portable ODBC v1.
  • In Parameters, Ordinal is zero-based and determines the binding order. Name is descriptive only in the portable v1 contract.
  • For Insert, Update, and Delete, use item property names that exactly match the target column names.
  • If the driver does not expose primary-key metadata reliably, enter KeyFields manually for Update and Delete.
  • Additional Connection String Properties must use one keyword=value pair per line.

Known Issues

  • ODBC drivers vary in metadata quality and supported connection string keywords.
  • Template discovery can be limited when the driver does not expose schema or primary-key metadata consistently.