ODBC Support
Open Database Connectivity (ODBC) is a standard database access method developed by the SQL Access group in 1992. ODBC, just like JDBC in Java, allows consistent client access regardless of which database management system (DBMS) is handling the data. ODBC uses a driver to translate the application’s data queries into commands that the DBMS understands. For this to work, both the application and the DBMS must be ODBC-compliant – that is, the application must be capable of issuing ODBC commands and the DBMS must be capable of responding to them.
Teiid can provide ODBC access to deployed VDBs in the Teiid runtime through PostgreSQL’s ODBC driver. This is possible because Teiid has a PostgreSQL server emulation layer accessible via socket clients.
|
Note
|
By default, ODBC is enabled and running on on port 35432. |
The pg emulation is not complete. The intention of the ODBC access is to provide non-JDBC connectivity to issue Teiid queries - not pgsql queries. While many PostgreSQL constructs are supported the default behavior for queries matches Teiid’s expectations. See System Properties for optional properties that further emulate pgsql handling.
|
Note
|
Handling names with underscore ("") in ODBC. By default Teiid does not have a default like escape character. Depending upon the ODBC client however there may be an expectation that backslash is used by default - which is the behavior of PostgreSQL.
This may cause metadata queries to be issued against objects with "" in their name to return no or incorrect results. You may globally emulate the behavior of PostgreSQL by setting the org.teiid.backslashDefaultMatchEscape system property to true. To alter the property just for the current session then have your ODBC client issue select cast(teiid_session_set('backslashDefaultMatchEscape', true) as boolean) statement before any other statement.
|
Postgres ODBC drivers 9.5 and later do not require this special property as the client will use an E escaped literal instead.
Compatibility was last ensured with the 9.6 Postgres ODBC driver. You are encouraged to use later client versions when needed and report any issues to the community.
Known Limitations:
-
Updateable cursors are not supported. You will receive parsing errors containing the pg system column ctid if this feature is not disabled.
-
LO support is not available. LOBs will be returned as string or bytea as appropriate using the transport max lob size setting.
-
The Teiid object type will map to the PostgreSQL UNKNOWN type, which cannot be serialized by the ODBC layer. Cast/Convert should be used to provide a type hint when appropriate - for example teiid_session_set returns an object value. "SELECT teiid_session_set('x', 'y')" will fail, but "SELECT cast(teiid_session_set('x', 'y') as string)" will succeed.
-
Multi-dimensional arrays are not supported.
Installation
Before an application can use ODBC, you must first install the ODBC driver on same machine that the application is running on and then create a Data Source Name (DSN) that represents a connection profile for your Teiid VDB.
For a windows client, see the Windows Installation Guide.
Configuration
|
Warning
|
By default Teiid supports plain text password authentication for pg/ODBC. If the client/server are not configured to use SSL or GSS authentication, the password will be sent in plain text over the network. |
For a windows client, see the Configuring the Data Source Name.
See also DSN Less Connection.
Connection Settings
All the available pg driver connection options with their descriptions that can be used are defined here https://odbc.postgresql.org/docs/config.html. When using these properties on the connection string, their property names are defined here https://odbc.postgresql.org/docs/config-opt.html.
However Teiid does not honor all properties, and some, such as Updatable Cursors, will cause query failures.
| Name | Description |
|---|---|
Updateable Cursors & Row Versioning |
Should not be used. |
Use serverside prepare & Parse Statements & Disallow Premature |
It is recommended that "Use serverside prepare" is enabled and "Parse Statements"/"Disallow Premature" are disabled |
SSL mode |
May be needed if you are connecting to a secured pg transport port. See Security Guide |
Use Declare/Fetch cursors & Fetch Max Count |
Should be used to better manage resources when large result sets are used |
Logging/debug settings can be utilized as needed.
Settings that manipulate datatypes, metadata, or optimizations such as "Show SystemTables", "True is -1", "Backend genetic optimizer", "Bytea as LongVarBinary", "Bools as Char", etc. are ignored by the Teiid server and have no client side effect. If there is a need for these or any other settings to have a defined affect, please open an issue with the product/project.
Any other setting that does have a client side affect, such as "LF <→ CR/LF conversion", may be used if desired but there is currently no server side usage of the setting.
Teiid Connection Settings
Most Teiid specific connection properties do not map to ODBC client connection settings. If you find yourself in this situation and cannot use post connection SET statements, then the VDB itself may take default connection properties for ODBC. Use VDB properties of the form connection.XXX to control things like partial results mode, result set caching, etc.
The application name may be set by some clients. If not, you may use a SET statement - "SET application_name name" - to set the name even after the connection is made.