ODBC connectivity through Tableau allow you access to any data source that supports the SQL standard and is compliant with the ODBC API. The mechanism supporting this is a rich feature-discovery API in ODBC that Tableau uses to question a database driver about the functionality it supports. Tableau has an experimental feature which allows users to customize parts of their ODBC data connection, which can dramatically improve the connection experience.
Updates
- 2011-11-03: Added new capabilities associated with the introduction of native ODBC support in Tableau 6.1.4.
- 2012-08-07: Added new capabilities in Tableau 7.0.7.
- 2012-09-20: Added a new capability in Tableau 7.0.8.
- 2012-03-21: Added new capabilities in Tableau 8.0.
- 2014-06-17: Added new capabilities in Tableau 8.1 and 8.2.
- 2015-04-06: Added new capabilities in Tableau 8.3 and 9.0
Intended audience
This article is for advanced users who are comfortable with reading technical API specifications, as well as editing basic XML documents and exploring the XML inside Tableau workbooks (.twb) and data source files (.tds). The reader should be familiar with the Tableau and ODBC article, which discusses Tableau's requirements and level of support for ODBC data sources.
Objective and caveats
Tableau expects a data source to follow the ODBC and SQL standards, but we recognize that most do not. Deviations from these standards may be minor, and this article will discuss how to customize a connection to work around such problems. However some data sources have substantial differences in their SQL dialect while others have very unstable drivers, and for both the best-case scenario is to restrict Tableau to a basic connection and require users to create an Extract. In some extreme cases, no degree of customization will allow Tableau to connect.
Prerequisites
All of these examples will be based on SQLite (http://www.sqlite.org/), an open-source database. You will need to download the following two items:
- The SQLite 32-bit ODBC driver (required for 32-bit or 64-bit Windows). Download and install the following:
- A sample SQLite database created from the Microsoft example ‘Northwind database'. Download and unzip the following:
Terms used
This article will reference the Tableau repository, which is located in the user's Documents folder under one of the following locations:
- My Tableau Repository
- My Tableau Repository (Beta)
External references
The reader will be directed to the following external sites for reference material.
- The Microsoft documentation for the ODBC API function SQLGetInfo:
- A sample header file, 'sqlext.h', for the ODBC API identifier values used with SQLGetInfo:
Introduction to Customization
Tableau generates complex SQL statements which are tuned for each of the natively supported data sources. Since Tableau has no detailed representation of the SQL dialect used by a generic ODBC data source, it must infer the supported syntax through a variety of tests and ODBC API calls. If a driver reports incorrect or incomplete information about the SQL dialect it supports, Tableau allows a user to customize this connection information in order to improve functionality and performance.
Getting started
To get started with an example, make a connection to the Northwind database. In this example, the connection is saved as a Tableau Data Source (.tds) file that we will then manipulate.
For Tableau Desktop 8.2 and later:
- Open Tableau Desktop.
- On the Connect page, click Other Databases (ODBC).
- Select Driver, and then from the drop-down list, select SQLite3 ODBC Driver.
- Click Connect. The Connect dialog box displays.
- Next to the Database Name text box, click Browse, and then navigate to the location of your Northwind.sl3 file.
- Click OK to close the dialog box, and then click OK again.
- On the data source page,in the Table text box, type "Orders."
- Drag the Orders table to the join area, and then click Go to Worksheet. At this point you will see a dialog warning that Tableau has detected some limitations in functionality for this data source. View the details to see a list of missing functionality or close the dialog boxes to enter see the worksheet.
- Select Data > Add to Saved Data Sources.
- Close the workbook.
For Tableau Desktop 8.1 and earlier:
- Launch Tableau Desktop.
- Select Other Databases (ODBC).
- Switch the connection mode to Driver and choose SQLite3 ODBC Driver.
- Click Connect, and browse to the location of your Northwind.sl3 file.
- Choose Orders as your table, and complete the connection.
- At this point you will see a dialog warning that Tableau has detected some limitations in functionality for this data source. View the details to see a list of missing functionality. Dismiss the dialog boxes to enter the Tableau visualization interface.
- Save the data source by selecting the data source from the Data menu, and selecting Add to Saved Data Sources.
- Close the current workbook.
Now you can begin editing this file to customize the SQLite connection.
Connection customization XML structure
Open the saved data source file in a text editor. You should recognize this as a simple XML document describing the SQLite connection to the Northwind Orders table. Within the
Vendor/driver name
The connection customization section begins with the names Tableau detected for the ODBC driver and the database vendor. Tableau uses this information to associate a specific connection customization with a single type of ODBC data source. We will revisit this topic in the Making Customizations Global section.
…Types of customizations
Tableau currently permits two types of customizations: Tableau-specific capabilities, and ODBC API calls to SQLGetInfo. These customizations are simple name / value pairs, with the names following a convention of 'CAP_' for Tableau capabilities and 'SQL_' for the SQLGetInfo API calls. These customizations are listed in detail in the Appendix at the end of this article:
You will notice that the saved data source file already contains examples of both types of customizations. These customizations represent the values which Tableau was able to detect by probing the driver at the start of a connection. The list of customizations may be incomplete or incorrect, and by modifying this list we will be able to shape Tableau's behavior when connecting to an ODBC data source.
Format of customization values
The customization values are all represented as strings for each name /value pair. Tableau capabilities are all boolean values represented by 'yes' or 'no'. The SQLGetInfo values may be either long-integer, short-integer or string data, depending on the expected return value of SQLGetInfo for the given identifier. Many of the integer fields are bitmasks which represent a collection of capabilities.
Customizing a single connection
We will now proceed to modify our SQLite data source file to customize the connection.
Enabling connection customizations
Within the
enabled which is off by default. By changing this value to 'true' we can force Tableau to adopt the connection customizations instead of probing the data source at the start of the connection.To verify this, set
enabled='true' and save your data source file. Open this file with Tableau to reconnect to your SQLite Northwind data set. Now open your Tableau log file from the Tableau repository, and verify that you see the following line:Loaded an inline TDC for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'Close the current workbook before proceeding.
Example - Capability customization
To see how capability customizations can affect the queries that Tableau issues, let's see what happens when we disable an existing capability. Edit the saved data source file and change the capability named
CAP_QUERY_HAVING_REQUIRES_GROUP_BY to have the value 'no'. Open the data source file from Tableau and dismiss the warning dialog. Now double-click on [Number of Records] in the data window, which will place it on the Rows shelf. This will generate a query error whose details explain that a HAVING clause requires some form of grouping field. Close this workbook and revert your change to the saved data source file.Example - ODBC/SQL customization
An ODBC driver can describe its level of support for various features of the ODBC and SQL specifications. In this example we will configure the maximum number of characters which can be used in an identifier. Edit the saved data source file and change the capability named
SQL_MAX_IDENTIFIER_LEN to have the value '10'. Open the data source file from Tableau and dismiss the warning dialog. Now double-click on [ShipCountry] in the data window. To view the query Tableau issued, open your Tableau log file and scroll towards the end - you should see a query like:SELECT "Orders"."ShipCountry" AS "no_ShipCo" ...The alias Tableau used to identify this field is
"no_ShipCo", which we see has been truncated to ensure it fits (along with an end-of-string termination character) into the allotted space. Close this workbook and revert your change to the saved data source file.Common Customizations for improving functionality
The following customizations can help with data sources which misrepresent their capabilities or are unstable when used with default capabilities.
CAP_SUPPRESS_DISCOVERY_QUERIES- A value of'true'will prevent Tableau from performing any SQL queries at the beginning of the connection to determine the supported capabilities. You will need to consider which other capabilities should be explicitly enabled, since Tableau will not be able to determine those automatically.SQL_SQL_CONFORMANCE- Declare which level of the SQL standard is fully supported by the data source. Tableau works best with at least entry-level conformance, so this value should be at least'1'if the data source supports it.SQL_AGGREGATE_FUNCTIONS- Declare which aggregate functions are supported, e.g. MIN, MAX, SUM. A value of'127'enables support for all standard SQL aggregates.
Making customizations global
The examples above demonstrated how to customize a saved Tableau data source file. You can also make these changes in the
To ensure your changes will apply to all connections for a given ODBC data source, you will need to create a Tableau datasource connection (TDC) file. This file will contain only the
1. Using a text editor, copy and paste the entire
2. Name the file "odbc-sqlite.tdc" and save it to one of the following locations.
- For Tableau Desktop, save the file to the Datasources folder in the My Tableau Repository. By default, the My Tableau Repository is located in C:\Users\
\Documents\My Tableau Repository. - For Tableau Server, save the file to the C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Datasources directory.
Note: The file must be saved using a .tdc extension, but the name does not matter.
3. Restart Tableau Desktop or Tableau Server to apply the change.
4. In Tableau Desktop, create a new connection to SQLite as described in the Getting Started section above.
5. Go to and open the Tableau Desktop or Tableau Server log file, and look for a record similar to the example below to verify that this customization file was applied to your new connection.
| Log File Location | Example of Record | |
|---|---|---|
| Tableau Desktop | By default, C:\Users\ | Found matching TDC 'C:\\Users\\ |
| Tableau Server | C:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\Logs | Found matching TDC 'C:\\ProgramData\\Tableau\\Tableau Server\\data\\tabsvc\\vizqlserver\\Datasources\\odbc-sqlite.tdc for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver' |
Advanced customizations
Native ODBC
Tableau allows you to connect directly with the ODBC API instead of relying on the OLE DB translation bridge to ODBC. This provides fine-tuned control over how Tableau uses the ODBC API through the CAP_ODBC_* connection customization options. Additionally, the direct ODBC connection layer avoids some of the complexity and overhead caused by the OLE DB bridge, which can reduce stability and performance.
Beginning in Tableau 8.1, the native ODBC protocol is on by default. If you are using Tableau 8.0 and earlier, you can enable the native ODBC protocol in Tableau in two ways:
- You can set the
CAP_ODBC_USE_NATIVE_PROTOCOLcapability (either globally or inline) for a given data source. - You can use the generic ODBC connection dialog to edit the connection attribute named 'odbc-native-protocol', as shown in the screen shot below.
Handling Severely Limited Data Sources
Some data sources are so severely limited that Tableau is unable to complete the steps of creating a connection. Occasionally this is due to crashes within the driver, which cause Tableau to cease working. A global TDC file can be used to prevent Tableau from issuing queries or checking for capabilities which may be associated with the instability. To create this file you will need to know the database vendor name and ODBC driver name. After attempting to create a new connection within Tableau, open your log file and look for a line like the following:
GenericODBCProtocol::Connect: Detected vendor: 'SQLite' and driver: 'SQLite3 ODBC Driver'Create a TDC file with the listed vendor name and driver name. Consider using options such as
CAP_SUPPRESS_DISCOVERY_QUERIES to constrain Tableau's interaction with a troublesome data source. If using Tableau 8.0 and earlier, enabling CAP_ODBC_USE_NATIVE_PROTOCOL will cause Tableau to use the native ODBC protocol for this data source, which may improve stability and enables additional tuning options. The native ODBC protocol is on by default in later versions of Tableau.Configuring ODBC Driver Settings
ODBC drivers provide interactive dialogs for supplying connection details such as the server, username and password. Many offer advanced options for controlling the connection behavior. You should consider these options when exploring ways to improve the functionality or performance of your ODBC connection. In particular look for settings that control the items below, as these have been the cause of past issues with ODBC connections in Tableau:
- Transaction Isolation - Prefer
READ COMMITTEDorSERIALIZABLEto ensure queries do not include data from pending transactions in their results. - Cursors - Prefer Holdable or Scrollable over Streaming cursors. Since Tableau closes a transaction after every read-only analytical query, streaming cursors may become truncated and lead to incomplete results displayed in Tableau.
- Row / Resultset Buffers - Prefer larger buffer sizes to improve the performance of fetching numerous rows, which can greatly improve the speed of creating extracts. This is sometimes called the cache size or response size.
- Character Set - In decreasing order of preference: UTF-16, UTF-8, ASCII.
Advanced SQLite Customizations
Tableau 5.2.4 introduced special customizations for SQLite which are built-in to the product. These customizations take precedence over any inline or global connection customizations for SQLite. In order to make advanced changes to SQLite via connection customizations, you will need to trick Tableau into ignoring its own SQLite dialect. You can do so by changing the reported name of the database vendor, tracked as SQL_DBMS_NAME, to a different value such as 'SQLite-Tableau'.
Appendix
Appendix A - Useful Resources
Microsoft's MSDN has a tremendous amount of documentation for the ODBC standard. The ODBC Appendixes are the most applicable to this article, specifically:
- Appendix C: SQL Grammar - especially the SQL Minimum Grammar and ODBC Escape Sequences.
- Appendix D: Data Types
- Appendix E: Scalar Functions
Appendix B - List of Tableau Capability Customizations
The following customizations will define which Tableau capabilities are supported by the data source. Many of these customizations will influence the type of SQL queries which Tableau issues. Unless explicitly defined, Tableau will attempt to determine the proper values for each capability by issuing various forms of SQL queries to experimentally verify which forms are supported.
| CAP_CREATE_TEMP_TABLES | Set to 'yes' if Tableau can create temporary tables needed for certain complex or optimized queries. See also: CAP_SELECT_INTO. |
| CAP_CONNECT_STORED_PROCEDURE | Set to 'yes' to allow support for connecting to a stored procedure. This capability is available in Tableau 8.1 and later. |
| CAP_ISOLATION_LEVEL_READ_COMMITTED | Set to 'yes' to force the transaction isolation level to Read Committed if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This capability is available in Tableau 8.0 and later. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API. |
| CAP_ISOLATION_LEVEL_READ_UNCOMMITTED | Set to 'yes' to force the transaction isolation level to Read Uncommitted if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This capability can improve speed by reducing lock contention, but may result in partial or inconsistent data in query results. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API. |
| CAP_ISOLATION_LEVEL_REPEATABLE_READS | Set to 'yes' to force the transaction isolation level to Repeatable Reads if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API. |
| CAP_ISOLATION_LEVEL_SERIALIZABLE | Set to 'yes' to force the transaction isolation level to Serializable if the data source supports it. Only one of the four transaction isolation levels should be set to 'yes'. This is a very conservative setting that may improve stability at the expense of performance. This capability is available in Tableau 8.0. See also: CAP_SET_ISOLATION_LEVEL_VIA_SQL, CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API. |
| CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API | Set to 'yes' to force Tableau to set the transaction isolation level for the data source using the ODBC API. CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API must be set to 'yes' when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to 'yes' . This capability is available in Tableau 8.0 and later. |
| CAP_SET_ISOLATION_LEVEL_VIA_SQL | Set to 'yes' to force Tableau to set the transaction isolation level for the data source using a SQL query. CAP_SET_ISOLATION_LEVEL_VIA_SQL must be set to 'yes' when any one of the four CAP_ISOLATION_LEVEL capabilities has been set to 'yes' . This capability is available in Tableau 8.0 and later. |
| CAP_MULTIPLE_CONNECTIONS_FROM_SAME_IP | Set to 'no' to prevent Tableau from creating more than one active connection to the database. This is a conservative setting that may increase stability at the expense of performance. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_DETECT_ALIAS_CASE_FOLDING | Set to 'yes' to allow Tableau to detect and recover from an ODBC data source that reports the field names in a result set using only upper-case or lower-case characters, instead of the expected field names. This capability is available in Tableau 7.0.7 and later. |
| CAP_ODBC_BIND_BOOL_AS_WCHAR_01LITERAL | Set to 'yes' to bind a boolean data type as a WCHAR containing values '0' or '1.' This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_BOOL_AS_WCHAR_TFLITERAL | Set to 'yes' to bind a boolean data type as WCHAR containing values 't' or 'f.' This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_FORCE_DATE_AS_CHAR | Set to 'yes' to force the Tableau native ODBC protocol to bind date values as CHAR. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR | Set to 'yes' to force the Tableau native ODBC protocol to bind datetime values as CHAR. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS | Set to 'yes' to force the Tableau native ODBC protocol to use maximum-sized buffers (1K) for strings instead of the size described by metadata. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_FORCE_MEDIUM_STRING_BUFFERS | Set to 'yes' to force the Tableau native ODBC protocol to use medium-sized buffers (1K) for strings instead of the size described by metadata. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_FORCE_SMALL_STRING_BUFFERS | Set to 'yes' to force the Tableau native ODBC protocol to use small buffers for strings instead of the size described by metadata. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_BIND_FORCE_SIGNED | Set to 'yes' to force binding integers as signed. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_BIND_PRESERVE_BOM | Set to 'yes' to preserve BOM when present in strings. Hive will return BOM and treat strings containing it as distinct entities.This capability is available in Tableau 8.2 and later. |
| CAP_ODBC_BIND_SUPPRESS_COERCE_TO_STRING | Set to 'yes' to prevent the Tableau native ODBC protocol from binding non-string data as strings (i.e. requesting driver conversion). This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_SUPPRESS_INT64 | Set to 'yes' to prevent the Tableau native ODBC protocol from using 64-bit integers for large numeric data. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_BIND_SUPPRESS_PREFERRED_CHAR | Set to 'yes' to prevent the Tableau native ODBC protocol from preferring a character type that differs from the driver default. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_BIND_SUPPRESS_PREFERRED_TYPES | Set to 'yes' to prevent the Tableau native ODBC protocol from binding any data according to its preferred wire types. With this capability set, Tableau will only bind according to the data types described by the ODBC driver via metadata. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_BIND_SUPPRESS_WIDE_CHAR | Set to 'yes' to prevent the Tableau native ODBC protocol from binding strings a WCHAR. Instead they will be bound as single-byte CHAR arrays, and processed locally for any UTF-8 characters contained within. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_CONNECTION_STATE_VERIFY_FAST | Set to ‘yes’ to check if a connection is broken with a fast ODBC API call. This capability is available in Tableau 8.2 and later. |
| CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE | Set to ‘yes’ to check if a connection is broken with a forced probe. This capability is available in Tableau 8.2 and later. |
| CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_IF_STALE | Set to ‘yes’ to check if a connection is broken with a forced probe only if it is "stale" (i.e., unused for about 30 minutes). This capability is available in Tableau 8.2 and later. |
| CAP_ODBC_CONNECTION_STATE_VERIFY_PROBE_PREPARED_QUERY | Set to ‘yes’ to check if a connection is broken using a prepared query. This capability is available in Tableau 8.2 and later. |
| CAP_ODBC_CURSOR_DYNAMIC | Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Dynamic (scrollable, detects added/removed/modified rows). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_CURSOR_FORWARD_ONLY | Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Forward-only (non-scrollable). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_CURSOR_KEYSET_DRIVEN | Set to 'yes' to force the Tableau native ODBC protocol to set the cursor type for all statements to Keyset-driven (scrollable, detects changes to values within a row). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_CURSOR_STATIC | Set to 'yes' to force Tableau to set the cursor type for all statements to Static (scrollable, does not detect changes). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_ERROR_IGNORE_FALSE_ALARM | Set to 'yes' to allow the Tableau native ODBC protocol to ignore SQL_ERROR conditions where SQLSTATE is '00000' (meaning "no error"). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_EXPORT_ALLOW_CHAR_UTF8 | Set to 'yes' to allow the use of single-byte char data type for binding Unicode strings as UTF-8. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BIND_FORCE_TARGET_METADATA | Set to 'yes' to force binding for export based on all of the metadata from the target table instead of the ODBC metadata for the parameterized insert statement.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BIND_PREFER_TARGET_METADATA | Set to 'yes' to prefer binding for export based on specific types of metadata from the target table instead of the ODBC metadata for the parameterized insert statement.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_RESIZABLE | Set to 'yes' to allow export buffers to be reallocated after the first batch to improve performance.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_SIZE_FIXED | Set to 'yes' to ignore the width of a single row when computing the total rows to insert at a time. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_SIZE_LIMIT_512KB | Set to 'yes' to limit export buffers to 512 KB. This is an uncommon setting. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_SIZE_MASSIVE | Set to 'yes' to force the use of large buffers for insert. If CAP_ODBC_EXPORT_BUFFERS_RESIZABLE is not set or disabled, a fixed row count is used.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_SIZE_MEDIUM | Set to 'yes' to force the use of medium-sized buffers for insert. If CAP_ODBC_EXPORT_BUFFERS_RESIZABLE is not set or disabled, a fixed row count is used.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_BUFFERS_SIZE_SMALL | Set to 'yes' to force the use of small buffers for insert. If CAP_ODBC_EXPORT_BUFFERS_RESIZABLE is not set or disabled, a fixed row count is used.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_CONTINUE_ON_ERROR | Set to 'yes' to continue data insert despite errors. Some data sources report warnings as errors.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_DATA_BULK | Set to 'yes' to allow the use of ODBC bulk operations for data insert. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_DATA_BULK_VIA_INSERT | Set to 'yes' to allow the use of ODBC bulk operations based on 'INSERT INTO' parameterized queries. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_DATA_BULK_VIA_ROWSET | Set to 'yes' to allow the use of ODBC bulk operations based on a rowset cursor. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_FORCE_INDICATE_NTS | Set to 'yes' to force the use of indicator buffers for identifying null-terminated strings (NTS). This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_FORCE_SINGLE_ROW_BINDING | Set to 'yes' to force the use of a single row for binding export buffers to insert data.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_FORCE_SINGLE_ROW_BINDING_WITH_TIMESTAMPS | Set to 'yes' to force the use of a single row for binding export buffers when dealing with timestamp data. This is required for some versions of Teradata.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_FORCE_STRING_WIDTH_FROM_SOURCE | Set to 'yes' to force the use of the source string width (from Tableau metadata), overriding the destination string width (from insert parameter metadata).This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_FORCE_STRING_WIDTH_USING_OCTET_LENGTH | Set to 'yes' to force the use of the source string width from the octet length.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_SUPPRESS_STRING_WIDTH_VALIDATION | Set to 'yes' to suppress validating that the target string width can accommodate the widest source strings.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BATCH_MASSIVE | Set to ‘yes’ to commit in massive batches of INSERT statements (~100,000). This may be useful with single-row export binding. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BATCH_MEDIUM | Set o 'yes' to commit in medium-sized batches of INSERT statements (~50). A single statement may be bound to multiple records.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BATCH_SMALL | Set to 'yes' to commit in small batches of INSERT statements (~5). A single statement may be bound to multiple records.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BYTES_MASSIVE | Set to 'yes' to commit in massive batches of data (~100 MB). This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BYTES_MEDIUM | Set to 'yes' to commit in medium batches of data (~10 MB). This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_BYTES_SMALL | Set to 'yes' to commit in small batches of data (~1 MB). This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_EACH_STATEMENT | Set to 'yes' to commit after executing each INSERT statement. A single statement may be bound to multiple records.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_INTERVAL_LONG | Set to 'yes' to commit in long intervals of elapsed time (~100 seconds). This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_INTERVAL_MEDIUM | Set to 'yes' to commit in medium intervals of elapsed time (~10 seconds )This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_INTERVAL_SHORT | Set to 'yes' to commit in short intervals of elapsed time (~1 seconds)This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSACTIONS_COMMIT_ONCE_WHEN_COMPLETE | Set to 'yes' to commit only once at the end after the export is complete.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_EXPORT_TRANSLATE_DATA_PARALLEL | Set to 'yes' to use parallel loops to translate Tableau DataValues to wire buffers on export. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_FETCH_ABORT_FORCE_CANCEL_STATEMENT | Set to 'yes' to cancel the statement handle upon interrupting SQLFetch with a cancel exception.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_FETCH_BUFFERS_RESIZABLE | Set to 'yes' to allow buffers to be reallocated after fetch to improve performance or handle data truncation. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED | Set to 'yes' to ignore the width of a single row when computing the total rows to fetch. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_BUFFERS_SIZE_MASSIVE | Set to 'yes' to force the use of large buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_BUFFERS_SIZE_MEDIUM | Set to 'yes' to force the use of medium-sized buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_BUFFERS_SIZE_SMALL | Set to 'yes' to force the use of small buffers. If CAP_ODBC_FETCH_BUFFERS_SIZE_FIXED is enabled, a fixed row count is used. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_CONTINUE_ON_ERROR | Set to 'yes' to allow the Tableau native ODBC protocol to continue resultset fetch despite errors (some data sources report warnings as errors). This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_FETCH_ESTIMATE_ROW_COUNT | Set to 'yes' to allow querying for an estimated or exact row count, which may be used to compute buffer sizes. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_FETCH_IGNORE_FRACTIONAL_SECONDS | Set to 'yes' to allow the Tableau native ODBC protocol to ignore the fractional seconds component of a time value when fetching query result set data. This is useful when working with data sources that do not follow the ODBC specification for fractional seconds, which must be represented as billionths of a second. This capability is available in Tableau 7.0.8 and later. |
| CAP_ODBC_FETCH_MASSIVE_BUFFERS | Set to 'yes' to force the Tableau native ODBC protocol to use large buffers instead of adapting to the resultset size. This capability is available in Tableau 6.1.4 through Tableau 7.0 only. |
| CAP_ODBC_FETCH_RESIZE_BUFFERS | Set to 'yes' to allow the Tableau native ODBC protocol to automatically resize buffers and fetch again if data truncation occurred. This capability is available in Tableau 6.1.4 through 7.0 only. |
| CAP_ODBC_FORCE_SINGLE_ROW_BINDING | Set to 'yes' to force the Tableau native ODBC protocol to use a single row for result set transfers instead of the more efficient bulk-fetch. This capability is available in Tableau 6.1.4 and later. |
| CAP_ODBC_IMPORT_ERASE_BUFFERS | Set to 'yes' to reset the contents of data buffers before fetching each block. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_IMPORT_TRUST_METADATA_CONTAINSNULL | Set to 'yes' to use efficient import if Tableau can trust the result set metadata representing the absence of NULLs. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_METADATA_FORCE_LENGTH_AS_PRECISION | Set to 'yes' to force the Tableau native ODBC protocol to use the column "length" as the numeric precision. This is an uncommon setting. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_METADATA_FORCE_NUM_PREC_RADIX_10 | Set to 'yes' to force the Tableau native ODBC protocol to assume the numeric precision is reported in base-10 digits. This is an uncommon setting. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_METADATA_FORCE_UNKNOWN_AS_STRING | Set to 'yes' to force the Native ODBC Protocol to treat unknown data types as string instead of ignoring the associated column. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_METADATA_FORCE_UTF8_IDENTIFIERS | Set to 'yes' to force the protocol to treat identifiers as UTF-8 when communicating with the driver. This capability is available in Tableau 8.0 and later. |
| CAP_ODBC_METADATA_SKIP_DESC_TYPE_NAME | Set to 'yes' to remove the check for the SQL_DESC_TYPE_NAME attribute with the SQLColAttribute API.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_METADATA_STRING_LENGTH_UNKNOWN | Set to 'yes' to prevent Tableau from allocating memory based on the driver-reported string length, which may not be known or reported properly. Instead, Tableau will use a fixed-sized string length, and will reallocate as needed to handle string data that is too large for the fixed-size buffer. This capability is available in Tableau 7.0.7. |
| CAP_ODBC_METADATA_STRING_TRUST_OCTET_LENGTH | Set to 'yes' to use the octet length reported by the driver for strings instead of computing it from the number of characters. This capability is available in Tableau 8.0. |
| CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY | Set to 'yes' to prevent Tableau from executing a query as a means of reading metadata. While Tableau typically includes a row-limiting clause in such metadata queries (e.g., 'LIMIT', or 'WHERE 1=0'), this may not help when used with a Custom SQL connection for database systems with poor query optimizers. Note that this capability may prevent Tableau from determining the connection metadata properly. This capability is available in Tableau 7.0.7. |
| CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY | Set to 'yes' to prevent Tableau from using a prepared query as a means of reading metadata. A prepared query is often the fastest way to accurately read metadata. However, not all database systems are capable of reporting metadata for a prepared query without actually executing the query. Note that certain metadata -- for example from connections using Custom SQL-- cannot be retrieved if this capability and CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY are both set. This capability is available in Tableau 7.0.7. |
| CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR | Set to 'yes' to prevent reading metadata using a 'select *' query. This capability is available in Tableau 8.0. |
| CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_API | Set to 'yes' to prevent Tableau from using older, less accurate API for reading metadata from ODBC data sources. Setting this capability allows Tableau to read metadata by issuing a full 'select *' query, which is expensive but may enable connectivity for extremely limited or unstable data sources. This capability is available in Tableau 7.0.7. |
| CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API | Set to 'yes' to prevent Tableau from attempting to read metadata describing foreign key constraints. Despite the simple nature of this ODBC API, some drivers may have unstable behavior or produce inaccurate results. Setting this capability may force Tableau to generate less efficient queries involving multi-table joins. This capability is available in Tableau 7.0.7. |
| CAP_ODBC_METADATA_SUPRESS_SQLSTATISTICS_API | Set to 'yes' to prevent reading unique constraints and table cardinality estimates using the SQLStatistics API or an equivalent query. This capability is available in Tableau 9.0 and later. |
| CAP_ODBC_REBIND_SKIP_UNBIND | Set to 'yes' to force the Tableau native ODBC protocol to rebind a column directly and skip unbinding, which reduces ODBC API calls when resizing buffers to refetch truncated data. T This capability is available in Tableau 6.1.4. |
| CAP_ODBC_SUPPRESS_INFO_SCHEMA_STORED_PROCS | Set to 'yes' to prevent the INFORMATION.SCHEMA schema from being queried when enumerating stored procedures.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_ALL_COMMAND_QUERIES | Set to 'yes' to execute all commands directly (i.e., no prepared statement).This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_DDL_COMMAND_QUERIES | Set to 'yes' to execute DDL commands (e.g. CREATE TABLE) directly (i.e., no prepared statement).This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_DML_COMMAND_QUERIES | Set to 'yes' to execute DML commands (e.g. INSERT INTO) directly (i.e, no prepared statement).This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_SUPPRESS_SYS_SCHEMA_STORED_PROCS | Set to 'yes' to explicitly add the "SYS" schema to the schema exclusions when enumerating stored procedures.This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_TRANSACTIONS_COMMIT_INVALIDATES_PREPARED_QUERY | Set to ‘yes’ to indicate that a transaction will invalidate all prepared statements and close any open cursors. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_TRANSACTIONS_SUPPRESS_AUTO_COMMIT | Set to 'yes' to prevent the Native ODBC Protocol from using default auto-committing transaction behavior in ODBC. This capability cannot be used with CAP_ODBC_TRANSACTIONS_SUPPRESS_EXPLICIT_COMMIT. This capability is available in 8.1. and later. |
| CAP_ODBC_TRANSACTIONS_SUPPRESS_EXPLICIT_COMMIT | Set to 'yes' to prevent the Native ODBC Protocol from explicitly managing transactions. This capability cannot be used with CAP_ODBC_TRANSACTIONS_SUPPRESS_AUTO_COMMIT. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_TRIM_VARCHAR_PADDING | Set to 'yes' to force the Tableau native ODBC protocol to trim trailing whitespace from VARCHAR columns which the driver has erroneously padded. This capability is available in Tableau 6.1.4. |
| CAP_ODBC_UNBIND_AUTO | Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns automatically, which can reduce ODBC API calls. This capability is available in Tableau 6.1.4. |
| CAP_ODBC_UNBIND_BATCH | Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns in a single batch operation, which can reduce ODBC API calls. This capability is available in Tableau 6.1.4. |
| CAP_ODBC_UNBIND_EACH | Set to 'yes' to force the Tableau native ODBC protocol to unbind and deallocate columns individually, which may improve stability. This is the default behavior for unbinding columns. This capability is available in Tableau 6.1.4. |
| CAP_ODBC_UNBIND_PARAMETERS_BATCH | Set to ‘yes’ to unbind all parameters in a single batch operation. This capability is available in Tableau 8.1 and later. |
| CAP_ODBC_USE_NATIVE_PROTOCOL | Set to 'yes' to force Tableau to use its native ODBC protocol instead of the OLE DB bridge to ODBC. This must be enabled to use any other CAP_ODBC_* capabilities. This capability is available in Tableau 6.1.4 through Tableau 8.0 only. |
| CAP_ORACLE_SHOW_ALL_SYNONYM_OWNERS | Set to 'yes' to list all the owners in the all_synonyms view for Oracle. This capability is available in 9.0 and later. |
| CAP_QUERY_BOOLEXPR_TO_INTEXPR | Set to 'yes' if Tableau must coerce any boolean expressions to an integer value in order include in a result set. |
| CAP_QUERY_FROM_REQUIRES_ALIAS | Set to 'yes' if the FROM clause must provide an alias for the given table. This capability is available in Tableau 6.1.4. |
| CAP_QUERY_GROUP_ALLOW_DUPLICATES | Set to 'no' if SQL queries cannot contain duplicate expressions in the GROUP BY clause (this is uncommon). This capability is available in Tableau 6.1.4. |
| CAP_QUERY_GROUP_BY_ALIAS | Set to 'yes' if SQL queries with aggregations can reference the grouping columns by their corresponding alias in the SELECT list, e.g. GROUP BY "none_ShipCountry_nk". |
| CAP_QUERY_GROUP_BY_DEGREE | Set to 'yes' if SQL queries with aggregations can reference the grouping columns by the ordinal position of each column, e.g. GROUP BY 2, 5. See also: CAP_QUERY_SORT_BY_DEGREE |
| CAP_QUERY_HAVING_REQUIRES_GROUP_BY | Set to 'yes' if Tableau must use an artificial grouping field for any query which has a HAVING clause but no grouping columns. This capability is available in Tableau 8.0 and later. |
| CAP_QUERY_HAVING_UNSUPPORTED | Set to 'yes' if the SQL syntax for HAVING is unsupported. Tableau may be able to work around this using subqueries. See also: CAP_QUERY_SUBQUERIES. This capability is available in Tableau 6.1.4. |
| CAP_QUERY_INCLUDE_GROUP_BY_COLUMNS_IN_SELECT | Set to 'yes' to require all GROUP BY expressions to also appear in the SELECT expression list. This capability is available in Tableau 8.0. |
| CAP_QUERY_JOIN_ACROSS_SCHEMAS | Set to 'yes' if SQL queries can express joins between tables located in different schemas. |
| CAP_QUERY_JOIN_ASSUME_CONSTRAINED | Set to ‘yes’ to cull inner joins even if the database tables does do not have FK-PK relationships. This capability is available in Tableau 8.0 and later. |
| CAP_QUERY_JOIN_PUSH_DOWN_CONDITION_EXPRESSIONS | Set to 'yes' to rewrite joins to simplify the ON clause conditions to simple identifier comparisons. This capability is available in Tableau 8.1 and later. |
| CAP_QUERY_JOIN_REQUIRES_SCOPE | Set to 'yes' if SQL queries must scope each join clause within parentheses to ensure a proper order of evaluation. |
| CAP_QUERY_JOIN_REQUIRES_SUBQUERY | Set to ‘yes’ to force join expressions involving more than two tables to be composed with subqueries. This capability is available in Tableau 8.1 and later. |
| CAP_QUERY_NULL_REQUIRES_CAST | Set to 'yes' if the data source requires that all NULL literals are cast to an explicit data type. This capability is available in Tableau 6.0.6. |
| CAP_QUERY_SELECT_ALIASES_SORTED | Set to 'yes' if Tableau must impose a deterministic order on the SELECT expressions (sorted by alias) to ensure that query results can be properly matched with each field in the Tableau visualization. This is only required for data sources which do not preserve the aliases of the SELECT expressions when returning metadata with the query results. This capability is available in Tableau 6.1.4. |
| CAP_QUERY_SORT_BY_DEGREE | Set to 'yes' if SQL queries can reference the sorting columns by the ordinal position of each column, e.g. ORDER BY 2, 5. See also: CAP_QUERY_GROUP_BY_DEGREE. This capability is available in Tableau 6.0.6. |
| CAP_QUERY_SUBQUERIES | Set to 'yes' if the data source supports subqueries. |
| CAP_QUERY_SUBQUERIES_WITH_TOP | Set to 'yes' if the data source supports a TOP or LIMIT row-limiting clause within a subquery. |
| CAP_QUERY_SUBQUERY_DATASOURCE_CONTEXT | Set to 'yes' to use subquery filtered query context to implement data source filters. This capability is available in Tableau 8.0 and later. |
| CAP_QUERY_SUBQUERY_QUERY_CONTEXT | Set to 'yes' to force Tableau to use a subquery for context filters instead of a temporary table or locally cached results. This capability is available in Tableau 6.1.4. |
| CAP_QUERY_TOP_N | Set to 'yes' if the data source supports any form of row-limiting clause. The exact forms supported are described below. |
| CAP_QUERY_TOPSTYLE_LIMIT | Set to 'yes' if the data source uses LIMIT as the row-limiting clause. |
| CAP_QUERY_TOPSTYLE_ROWNUM | Set to 'yes' if the data source supports an Oracle-style filter on ROWNUM as the row-limiting clause. |
| CAP_QUERY_TOPSTYLE_TOP | Set to 'yes' if the data source uses TOP as the row-limiting clause. |
| CAP_SELECT_INTO | Set to 'yes' if Tableau can create a table on the fly from the resultset of another query. See also: CAP_CREATE_TEMP_TABLES. |
| CAP_SELECT_TOP_INTO | Set to 'yes' if Tableau can use a TOP or LIMIT row-limiting clause when creating a table from a query resultset. |
| CAP_SKIP_CONNECT_VALIDATION | Set to 'yes' to prevent Tableau from validating the structure of a user-defined multi-table join or Custom SQL connection. This capability is available through Tableau 7.0 only. |
| CAP_STORED_PROCEDURE_PREFER_TEMP_TABLE | Set to 'yes' to use a temporary table to support remote queries over the stored procedure result set.This capability is available in Tableau 8.1 and later. |
| CAP_STORED_PROCEDURE_REPAIR_TEMP_TABLE_STRINGS | Set to 'yes' to attempt to compute actual string widths if metadata indicates no width or non-positive width. This capability is available in Tableau 8.1 and later. |
| CAP_STORED_PROCEDURE_TEMP_TABLE_FROM_BUFFER | Set to 'yes' to populate the temporary table from a result set buffered in entirety.This capability is available in Tableau 8.1 and later. |
| CAP_STORED_PROCEDURE_TEMP_TABLE_FROM_NEW_PROTOCOL | Set to ‘yes’ to populate the temporary table from a separate protocol created for just this operation. This capability is available in Tableau 8.1 and later. |
| CAP_SUPPRESS_DISCOVERY_QUERIES | Set to 'yes' to prevent Tableau from detecting the supported SQL syntax for a variety of clauses. |
| CAP_SUPPRESS_DISPLAY_LIMITATIONS | Set to 'yes' to suppress displaying any warnings about limitations for this data source. |
Appendix C – List of ODBC/SQL Customizations
These customizations represent the portions of the ODBC and SQL standards which the driver claims to support. The names of these customizations come from the identifiers used as parameters to SQLGetInfo. Refer to the MSDN documentation and the source code header file sqlext.h for the numeric and bit-mask values associated with each customization.
SQL GetInfo Long-Integer Values
| SQL_ODBC_INTERFACE_CONFORMANCE | Currently unused. |
| SQL_SQL_CONFORMANCE | Integer bitmask. Defines the level which the data source conforms to the SQL standard: '1' for entry-level SQL-92 conformance, '2' for FIPS 127-2 transitional, '4' for Intermediate and '8' for Full conformance. |
| SQL_CATALOG_USAGE | Integer bitmask. Defines the SQL statements in which a catalog identifier can be used. |
| SQL_SCHEMA_USAGE | Integer bitmask. Defines the SQL statements in which a schema identifier can be used. |
| SQL_AGGREGATE_FUNCTIONS | Integer bitmask. Defines which standard SQL aggregation forms are supported. |
| SQL_NUMERIC_FUNCTIONS | Integer bitmask. Defines which SQL scalar numeric functions are supported. |
| SQL_STRING_FUNCTIONS | Integer bitmask. Defines which SQL scalar string functions are supported. |
| SQL_TIMEDATE_FUNCTIONS | Integer bitmask. Defines which SQL scalar date / time functions are supported. |
| SQL_TIMEDATE_ADD_INTERVALS | Integer bitmask. Defines which date / time intervals are supported with the TIMESTAMPADD scalar function. |
| SQL_TIMEDATE_DIFF_INTERVALS | Integer bitmask. Defines which date / time intervals are supported with the TIMESTAMPDIFF scalar function. |
| SQL_DATETIME_LITERALS | Integer bitmask. Defines which SQL-92 literals are supported for representing DATE / TIME constants and INTERVALs. |
| SQL_SYSTEM_FUNCTIONS | Integer bitmask. Defines support for special SQL system scalar functions: IFNULL, DBNAME and USERNAME. |
| SQL_SQL92_VALUE_EXPRESSIONS | Integer bitmask. Defines which logical functions are supported for testing and manipulating values: CASE, CAST and NULLIF. |
| SQL_SQL92_NUMERIC_VALUE_FUNCTIONS | Integer bitmask. Defines which functions can produce a numeric value from non-numeric data, including: EXTRACT (for date / time part extraction), CHAR_LENGTH, CHARACTER_LENGTH and POSITION(.. IN ..). |
| SQL_SQL92_STRING_FUNCTIONS | Integer bitmask. Defines which string manipulation functions are supported. |
| SQL_SQL92_DATETIME_FUNCTIONS | Integer bitmask. Defines which date / time manipulation functions are supported for determining the current date, time or timestamp. |
| SQL_OJ_CAPABILITIES | Integer bitmask. Defines which type of outer joins are supported. |
| SQL_SQL92_RELATIONAL_JOIN_OPERATORS | Integer bitmask. Defines which types of JOIN operators are supported, e.g. INNER, OUTER. |
| SQL_SQL92_PREDICATES | Integer bitmask. Defines which predicates are supported for logical tests of values, e.g. IS NULL, LIKE, IN. |
| SQL_CONVERT_FUNCTIONS | Integer bitmask. Defines which ODBC scalar functions are supported for CASTing or CONVERTing one data type to another. |
| SQL_CONVERT_TINYINT | Integer bitmask. Determines which other data types that this named type can be converted to using the ODBC scalar function CONVERT. |
| SQL_CONVERT_SMALLINT | Integer bitmask. Same as above. |
| SQL_CONVERT_INTEGER | Integer bitmask. Same as above. |
| SQL_CONVERT_BIGINT | Integer bitmask. Same as above. |
| SQL_CONVERT_REAL | Integer bitmask. Same as above. |
| SQL_CONVERT_FLOAT | Integer bitmask. Same as above. |
| SQL_CONVERT_DOUBLE | Integer bitmask. Same as above. |
| SQL_CONVERT_CHAR | Integer bitmask. Same as above. |
| SQL_CONVERT_VARCHAR | Integer bitmask. Same as above. |
| SQL_CONVERT_LONGVARCHAR | Integer bitmask. Same as above. |
| SQL_CONVERT_DECIMAL | Integer bitmask. Same as above. |
| SQL_CONVERT_NUMERIC | Integer bitmask. Same as above. |
| SQL_CONVERT_BIT | Integer bitmask. Same as above. |
| SQL_CONVERT_GUID | Integer bitmask. Same as above. |
| SQL_CONVERT_BINARY | Integer bitmask. Same as above. |
| SQL_CONVERT_VARBINARY | Integer bitmask. Same as above. |
| SQL_CONVERT_LONGVARBINARY | Integer bitmask. Same as above. |
| SQL_CONVERT_DATE | Integer bitmask. Same as above. |
| SQL_CONVERT_TIME | Integer bitmask. Same as above. |
| SQL_CONVERT_TIMESTAMP | Integer bitmask. Same as above. |
| SQL_CONVERT_INTERVAL_DAY_TIME | Integer bitmask. Same as above. |
SQLGetInfo Short-Integer Values
| SQL_MAX_IDENTIFIER_LEN | Integer value. Defines the maximum number of characters that can be used in an identifier. Tableau leaves room for one extra character as the string terminator. |
| SQL_QUOTED_IDENTIFIER_CASE | Integer bitmask. Currently unused. |
SQLGetInfo String Values
| SQL_COLUMN_ALIAS | Boolean value. "Y" if the data source supports using aliases for columns listed in the SELECT clause. |
| SQL_IDENTIFIER_QUOTE_CHAR | String value. Indicates the character which can be used for quoting identifiers. Because the connection customization is an XML document, any entities must properly be encoded. For example, double-quotes will be '"'. Additionally this character is assumed to work as the opening and closing character around identifiers, so some data sources which require '[]' to enclose identifiers will not be supported. |
| SQL_CATALOG_NAME_SEPARATOR | Character value. Indicates the separator character to use between identifiers when qualifying them with a catalog, schema or table name. This is typically the period character. |
| SQL_SPECIAL_CHARACTERS | String value. Indicates the special characters which are allowed in identifier strings. |
| SQL_CATALOG_TERM | String value. This is the descriptive term for a database catalog, which appears in the Tableau connection dialog for this ODBC data source. |
| SQL_SCHEMA_TERM | String value. This is the descriptive term for a database schema, which appears in the Tableau connection dialog for this ODBC data source. |
| SQL_TABLE_TERM | String value. This is the descriptive term for a database table, which appears in the Tableau connection dialog for this ODBC data source. |
| SQL_DRIVER_NAME | String value. This is the name of the database ODBC driver. |
| SQL_DRIVER_VER | String value. This is the version number of the ODBC driver. |
| SQL_DRIVER_ODBC_VER | String value. This is the version of the ODBC API which the driver supports. |
| SQL_ODBC_VER | String value. This is the version of ODBC which the Windows ODBC Driver Manager supports. This should not need to be customized. |
| SQL_DBMS_NAME | String value. This is the name of the database vendor. |
| SQL_DBMS_VER | String value. This is the version of the database system. |
| SQL_SERVER_NAME | String value. This is the named network address of the database server. |
| SQL_USER_NAME | String value. This is the name of the currently authenticated user. |
Appendix D – Sample SQLite Customizations
From the examples in this article, the fully customized SQLite data source file will look like the following:
'StepAPI=0;NoTXN=0;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0'
odbc-dbms-name='SQLite' odbc-driver='SQLite3 ODBC Driver' >
Appendix E – Sample SQLite TDC file