Knowledge Base

Customizing and Tuning ODBC Connections

Product(s): Professional Edition, Tableau Server
Version(s): 8.2, 8.1, 8.0, 7.0
Last Modified Date: 22 Oct 2014

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.

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:

http://www.ch-werner.de/sqliteodbc/sqliteodbc.exe

  • A sample SQLite database created from the Microsoft example ‘Northwind database'. Download and unzip the following:

http://download.vive.net/Northwind.zip

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:

http://msdn.microsoft.com/en-us/library/ms711681%28VS.85%29.aspx

  • A sample header file, 'sqlext.h', for the ODBC API identifier values used with SQLGetInfo:

http://cpansearch.perl.org/src/MIMER/DBD-Mimer-1.00/sqlext.h

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: 

  1. Open Tableau Desktop.
  2. On the Connect page, click Other Databases (ODBC).
  3. Select Driver, and then from the drop-down list, select SQLite3 ODBC Driver.
  4. Click Connect. The Connect dialog box displays.
  5. Next to the Database Name text box, click Browse, and then navigate to the location of your Northwind.sl3 file.
  6. Click OK to close the dialog box, and then click OK again.
  7. On the data source page,in the Table text box, type "Orders."
  8. 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.
  9. Select Data > Add to Saved Data Sources.
  10. Close the workbook.

For Tableau Desktop 8.1 and earlier: 

  1. Launch Tableau Desktop.
  2. Select Other Databases (ODBC).
  3. Switch the connection mode to Driver and choose SQLite3 ODBC Driver.
  4. Click Connect, and browse to the location of your Northwind.sl3 file.
  5. Choose Orders as your table, and complete the connection.
  6. 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.
  7. Save the data source by selecting the data source from the Data menu, and selecting Add to Saved Data Sources.
  8. 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 <connection> section you will find the <connection-customization> tag, which is where we will focus our attention.

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.

<connection-customization class='genericodbc' enabled='true' version='7.10'>
<vendor name='SQLite' />
<driver name='SQLite3 ODBC Driver' />
<customizations>

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 <connection-customization> tag there is an attribute named 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.
  • CAP_SKIP_CONNECT_VALIDATION - A value of 'true' will prevent Tableau from issuing a SQL query to validate a multi-table or custom SQL connection.
  • 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 <connection> section within a saved Tableau workbook file. In both cases, the changes will only apply to the single data source file or the single workbook which you have edited.

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 <connection-customization> section and will be applied to any Tableau connection that matches the database vendor name and driver name described in the TDC file. However, any saved workbook or data source file that already has an enabled customization section will only use the customizations which it supplies.

1. Using a text editor, copy and paste the entire <connection-customization> section of your saved data source file. For reference, a sample .tdc file for SQLite is in Appendix E - Sample SQLite TDC file.

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\<user>\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 LocationExample of Record
Tableau DesktopBy default, C:\Users\<user>\Documents\My Tableau Repository\LogsFound matching TDC 'C:\\Users\\<user>\\Documents\\My Tableau Repository\\Datasources\\odbc-sqlite.tdc' for class='genericodbc', vendor='SQLite', and driver='SQLite3 ODBC Driver'
Tableau ServerC:\ProgramData\Tableau\Tableau Server\data\tabsvc\vizqlserver\LogsFound 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 6.1.4 introduces a new connection technology which work 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.

You can enable this new native ODBC protocol in Tableau in two ways:

  • You can set the CAP_ODBC_USE_NATIVE_PROTOCOL capability (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.

Enabling the native ODBC protocol:

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. 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.

 

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 COMMITTED or SERIALIZABLE to 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 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_TABLESSet to 'yes' if Tableau can create temporary tables needed for certain complex or optimized queries. See also: CAP_SELECT_INTO.
CAP_CONNECT_STORED_PROCEDURESet 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_COMMITTEDSet 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_UNCOMMITTEDSet 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_READSSet 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_SERIALIZABLESet 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_ISOLATION_LEVEL_VIA_ODBC_APISet to 'yes' to force Tableau to set the transaction isolation level for the data source using the ODBC API. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or 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_ISOLATION_LEVEL_VIA_SQLSet to 'yes' to force Tableau to set the transaction isolation level for the data source using a SQL query. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or 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_MULTIPLE_CONNECTIONS_FROM_SAME_IPSet 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_FOLDINGSet 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_01LITERALSet 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_TFLITERALSet 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_CHARSet 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_CHARSet 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_BUFFERSSet 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_BUFFERSSet 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_BUFFERSSet 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_SIGNEDSet to 'yes' to force binding integers as signed. This capability is available in Tableau 8.1 and later.
CAP_ODBC_BIND_PRESERVE_BOMSet 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_STRINGSet 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_INT64Set 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_CHARSet 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_TYPESSet 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_CHARSet 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_FASTSet 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_PROBESet 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_STALESet 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_QUERYSet 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_DYNAMICSet 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_ONLYSet 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_DRIVENSet 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_STATICSet 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_ALARMSet 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_UTF8Set 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_METADATASet 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_METADATASet 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_RESIZABLESet 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_FIXEDSet 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_512KBSet 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_MASSIVESet 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_MEDIUMSet 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_SMALLSet 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_ERRORSet 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_BULKSet 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_INSERTSet 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_ROWSETSet 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_NTSSet 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_BINDINGSet 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_TIMESTAMPSSet to 'yes' to force the use of a single row 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_SOURCESet 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_LENGTHSet 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_VALIDATIONSet 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_MASSIVESet 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_MEDIUMSet 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_SMALLSet 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_MASSIVESet 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_MEDIUMSet 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_SMALLSet 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_STATEMENTSet 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_LONGSet 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_MEDIUMSet 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_SHORTSet 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_COMPLETESet 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_PARALLELSet 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_STATEMENTSet 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_RESIZABLESet 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_FIXEDSet 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_MASSIVESet 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_MEDIUMSet 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_SMALLSet 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_ERRORSet 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_COUNTSet 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_SECONDSSet 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_BUFFERSSet 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_BUFFERSSet 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_BINDINGSet 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_BUFFERSSet 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_CONTAINSNULLSet 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_PRECISIONSet 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_10Set 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_STRINGSet 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_IDENTIFIERSSet 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_NAMESet 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_UNKNOWNSet 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_LENGTHSet 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_QUERYSet 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_QUERYSet 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_STARSet to 'yes' to prevent reading metadata using a 'select *' query. This capability is available in Tableau 8.0.
CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_APISet 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_APISet 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_REBIND_SKIP_UNBINDSet 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_PROCSSet 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_QUERIESSet 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_QUERIESSet 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_QUERIESSet 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_PROCSSet 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_QUERYSet 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_COMMITSet 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_COMMITSet 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_PADDINGSet 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_AUTOSet 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_BATCHSet 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_EACHSet 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_BATCHSet to ‘yes’ to unbind all parameters in a single batch operation. This capability is available in Tableau 8.1
CAP_ODBC_USE_NATIVE_PROTOCOLSet 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.
CAP_QUERY_BOOLEXPR_TO_INTEXPRSet to 'yes' if Tableau must coerce any boolean expressions to an integer value in order include in a resultset.
CAP_QUERY_FROM_REQUIRES_ALIASSet 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_DUPLICATESSet 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_ALIASSet 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_DEGREESet 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_BYSet 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_UNSUPPORTEDSet 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_SELECTSet 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_SCHEMASSet to 'yes' if SQL queries can express joins between tables located in different schemas.
CAP_QUERY_JOIN_ASSUME_CONSTRAINEDSet 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_EXPRESSIONSSet 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_SCOPESet to 'yes' if SQL queries must scope each join clause within parentheses to ensure a proper order of evaluation.
CAP_QUERY_JOIN_REQUIRES_SUBQUERYSet 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_CASTSet 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_SORTEDSet 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_DEGREESet 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_SUBQUERIESSet to 'yes' if the data source supports subqueries.
CAP_QUERY_SUBQUERIES_WITH_TOPSet to 'yes' if the data source supports a TOP or LIMIT row-limiting clause within a subquery.
CAP_QUERY_SUBQUERY_DATASOURCE_CONTEXTSet 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_CONTEXTSet 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_NSet to 'yes' if the data source supports any form of row-limiting clause. The exact forms supported are described below.
CAP_QUERY_TOPSTYLE_LIMITSet to 'yes' if the data source uses LIMIT as the row-limiting clause.
CAP_QUERY_TOPSTYLE_ROWNUMSet to 'yes' if the data source supports an Oracle-style filter on ROWNUM as the row-limiting clause.
CAP_QUERY_TOPSTYLE_TOPSet to 'yes' if the data source uses TOP as the row-limiting clause.
CAP_SELECT_INTOSet 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_INTOSet to 'yes' if Tableau can use a TOP or LIMIT row-limiting clause when creating a table from a query resultset.
CAP_SET_ISOLATION_LEVEL_VIA_ODBC_APISet to 'yes' to allow Tableau to set the transaction isolation level using the ODBC API. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or 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.
CAP_SET_ISOLATION_LEVEL_VIA_SQLSet to 'yes' to allow Tableau to set the transaction isolation level using standard SQL syntax. Either CAP_SET_ISOLATION_LEVEL_VIA_SQL or 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.
CAP_SKIP_CONNECT_VALIDATIONSet 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_TABLESet 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_STRINGSSet 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_BUFFERSet 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_PROTOCOLSet 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_QUERIESSet to 'yes' to prevent Tableau from detecting the supported SQL syntax for a variety of clauses.
CAP_SUPPRESS_DISPLAY_LIMITATIONSSet 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_CONFORMANCECurrently unused.
SQL_SQL_CONFORMANCEInteger 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_USAGEInteger bitmask. Defines the SQL statements in which a catalog identifier can be used.
SQL_SCHEMA_USAGEInteger bitmask. Defines the SQL statements in which a schema identifier can be used.
SQL_AGGREGATE_FUNCTIONSInteger bitmask. Defines which standard SQL aggregation forms are supported.
SQL_NUMERIC_FUNCTIONSInteger bitmask. Defines which SQL scalar numeric functions are supported.
SQL_STRING_FUNCTIONSInteger bitmask. Defines which SQL scalar string functions are supported.
SQL_TIMEDATE_FUNCTIONSInteger bitmask. Defines which SQL scalar date / time functions are supported.
SQL_TIMEDATE_ADD_INTERVALSInteger bitmask. Defines which date / time intervals are supported with the TIMESTAMPADD scalar function.
SQL_TIMEDATE_DIFF_INTERVALSInteger bitmask. Defines which date / time intervals are supported with the TIMESTAMPDIFF scalar function.
SQL_DATETIME_LITERALSInteger bitmask. Defines which SQL-92 literals are supported for representing DATE / TIME constants and INTERVALs.
SQL_SYSTEM_FUNCTIONSInteger bitmask. Defines support for special SQL system scalar functions: IFNULL, DBNAME and USERNAME.
SQL_SQL92_VALUE_EXPRESSIONSInteger bitmask. Defines which logical functions are supported for testing and manipulating values: CASE, CAST and NULLIF.
SQL_SQL92_NUMERIC_VALUE_FUNCTIONSInteger 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_FUNCTIONSInteger bitmask. Defines which string manipulation functions are supported.
SQL_SQL92_DATETIME_FUNCTIONSInteger bitmask. Defines which date / time manipulation functions are supported for determining the current date, time or timestamp.
SQL_OJ_CAPABILITIESInteger bitmask. Defines which type of outer joins are supported.
SQL_SQL92_RELATIONAL_JOIN_OPERATORSInteger bitmask. Defines which types of JOIN operators are supported, e.g. INNER, OUTER.
SQL_SQL92_PREDICATESInteger bitmask. Defines which predicates are supported for logical tests of values, e.g. IS NULL, LIKE, IN.
SQL_CONVERT_FUNCTIONSInteger bitmask. Defines which ODBC scalar functions are supported for CASTing or CONVERTing one data type to another.
SQL_CONVERT_TINYINTInteger bitmask. Determines which other data types that this named type can be converted to using the ODBC scalar function CONVERT.
SQL_CONVERT_SMALLINTInteger bitmask. Same as above.
SQL_CONVERT_INTEGERInteger bitmask. Same as above.
SQL_CONVERT_BIGINTInteger bitmask. Same as above.
SQL_CONVERT_REALInteger bitmask. Same as above.
SQL_CONVERT_FLOATInteger bitmask. Same as above.
SQL_CONVERT_DOUBLEInteger bitmask. Same as above.
SQL_CONVERT_CHARInteger bitmask. Same as above.
SQL_CONVERT_VARCHARInteger bitmask. Same as above.
SQL_CONVERT_LONGVARCHARInteger bitmask. Same as above.
SQL_CONVERT_DECIMALInteger bitmask. Same as above.
SQL_CONVERT_NUMERICInteger bitmask. Same as above.
SQL_CONVERT_BITInteger bitmask. Same as above.
SQL_CONVERT_GUIDInteger bitmask. Same as above.
SQL_CONVERT_BINARYInteger bitmask. Same as above.
SQL_CONVERT_VARBINARYInteger bitmask. Same as above.
SQL_CONVERT_LONGVARBINARYInteger bitmask. Same as above.
SQL_CONVERT_DATEInteger bitmask. Same as above.
SQL_CONVERT_TIMEInteger bitmask. Same as above.
SQL_CONVERT_TIMESTAMPInteger bitmask. Same as above.
SQL_CONVERT_INTERVAL_DAY_TIMEInteger bitmask. Same as above.

 

SQLGetInfo Short-Integer Values

 

SQL_MAX_IDENTIFIER_LENInteger 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_CASEInteger bitmask. Currently unused.

 

SQLGetInfo String Values

SQL_COLUMN_ALIASBoolean value. "Y" if the data source supports using aliases for columns listed in the SELECT clause.
SQL_IDENTIFIER_QUOTE_CHARString 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 '&quot;'. 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_SEPARATORCharacter 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_CHARACTERSString value. Indicates the special characters which are allowed in identifier strings.
SQL_CATALOG_TERMString value. This is the descriptive term for a database catalog, which appears in the Tableau connection dialog for this ODBC data source.
SQL_SCHEMA_TERMString value. This is the descriptive term for a database schema, which appears in the Tableau connection dialog for this ODBC data source.
SQL_TABLE_TERMString value. This is the descriptive term for a database table, which appears in the Tableau connection dialog for this ODBC data source.
SQL_DRIVER_NAMEString value. This is the name of the database ODBC driver.
SQL_DRIVER_VERString value. This is the version number of the ODBC driver.
SQL_DRIVER_ODBC_VERString value. This is the version of the ODBC API which the driver supports.
SQL_ODBC_VERString value. This is the version of ODBC which the Windows ODBC Driver Manager supports. This should not need to be customized.
SQL_DBMS_NAMEString value. This is the name of the database vendor.
SQL_DBMS_VERString value. This is the version of the database system.
SQL_SERVER_NAMEString value. This is the named network address of the database server.
SQL_USER_NAMEString 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:

<?xml version='1.0' encoding='utf-8' ?>
<datasource formatted-name='genericodbc.40571.475277974532' inline='true' version='7.10' xmlns:user='http://www.tableausoftware.com/xml/user' >
<!-- build 6000.0.0000.0000 -->
<connection class='genericodbc' dbname='C:\Northwind.sl3' odbc-connect-string-extras=
'StepAPI=0;NoTXN=0;ShortNames=0;LongNames=0;NoCreat=0;NoWCHAR=0;FKSupport=0'
odbc-dbms-name='SQLite' odbc-driver='SQLite3 ODBC Driver' >
<relation name='Orders' table='[Orders]' type='table' />
<connection-customization class='genericodbc' enabled='true' version='7.10'>
<vendor name='SQLite' />
<driver name='SQLite3 ODBC Driver' />
<customizations>
<customization name='CAP_CREATE_TEMP_TABLES' value='yes' />
<customization name='CAP_QUERY_BOOLEXPR_TO_INTEXPR' value='yes' />
<customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' />
<customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />
<customization name='CAP_QUERY_HAVING_REQUIRES_GROUP_BY' value='yes' />
<customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='no' />
<customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />
<customization name='CAP_QUERY_SUBQUERIES' value='yes' />
<customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='yes' />
<customization name='CAP_QUERY_TOPSTYLE_LIMIT' value='yes' />
<customization name='CAP_QUERY_TOPSTYLE_ROWNUM' value='no' />
<customization name='CAP_QUERY_TOPSTYLE_TOP' value='no' />
<customization name='CAP_QUERY_TOP_N' value='yes' />
<customization name='CAP_SELECT_INTO' value='no' />
<customization name='CAP_SELECT_TOP_INTO' value='yes' />
<customization name='CAP_SKIP_CONNECT_VALIDATION' value='no' />
<customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='no' />
<customization name='SQL_CATALOG_NAME_SEPARATOR' value='.' />
<customization name='SQL_CATALOG_TERM' value='' />
<customization name='SQL_CATALOG_USAGE' value='0' />
<customization name='SQL_COLUMN_ALIAS' value='Y' />
<customization name='SQL_CONVERT_FUNCTIONS' value='0' />
<customization name='SQL_DBMS_NAME' value='SQLite' />
<customization name='SQL_DBMS_VER' value='3.7.2' />
<customization name='SQL_DRIVER_ODBC_VER' value='03.00' />
<customization name='SQL_DRIVER_VER' value='0.87' />
<customization name='SQL_IDENTIFIER_QUOTE_CHAR' value='&quot;' />
<customization name='SQL_MAX_IDENTIFIER_LEN' value='255' />
<customization name='SQL_NUMERIC_FUNCTIONS' value='0' />
<customization name='SQL_ODBC_INTERFACE_CONFORMANCE' value='1' />
<customization name='SQL_ODBC_VER' value='03.80.0000' />
<customization name='SQL_OJ_CAPABILITIES' value='0' />
<customization name='SQL_QUOTED_IDENTIFIER_CASE' value='3' />
<customization name='SQL_SCHEMA_TERM' value='' />
<customization name='SQL_SCHEMA_USAGE' value='0' />
<customization name='SQL_SPECIAL_CHARACTERS' value='' />
<customization name='SQL_STRING_FUNCTIONS' value='0' />
<customization name='SQL_SYSTEM_FUNCTIONS' value='0' />
<customization name='SQL_TABLE_TERM' value='TABLE' />
<customization name='SQL_TIMEDATE_ADD_INTERVALS' value='0' />
<customization name='SQL_TIMEDATE_DIFF_INTERVALS' value='0' />
<customization name='SQL_TIMEDATE_FUNCTIONS' value='0' />
</customizations>
</connection-customization>
</connection>
<column datatype='integer' name='[Number of Records]' role='measure' type='quantitative' user:auto-column='numrec' >
<calculation class='tableau' formula='1' />
</column>
<column datatype='string' name='[ShipPostalCode]' role='dimension' semantic-role='[ZipCode].[Name]' type='nominal' >
</column>
<layout dim-ordering='alphabetic' dim-percentage='0.5' measure-ordering='alphabetic' measure-percentage='0.4' show-structure='true' />
<semantic-values>
<semantic-value key='[Country].[Name]' value='&quot;UNITED STATES&quot;' />
</semantic-values>
</datasource>

Appendix E – Sample SQLite TDC file

<? xml version='1.0' encoding='utf-8' ?>
<connection-customization class='genericodbc' enabled='true' version='7.8'>
<vendor name='SQLite' />
<driver name='SQLite3 ODBC Driver' />
<customizations>
<customization name='CAP_CREATE_TEMP_TABLES' value='yes' />
<customization name='CAP_QUERY_BOOLEXPR_TO_INTEXPR' value='yes' />
<customization name='CAP_QUERY_GROUP_BY_ALIAS' value='no' />
<customization name='CAP_QUERY_GROUP_BY_DEGREE' value='yes' />
<customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='no' />
<customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />
<customization name='CAP_QUERY_SUBQUERIES' value='yes' />
<customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='yes' />
<customization name='CAP_SELECT_INTO' value='no' />
<customization name='CAP_SELECT_TOP_INTO' value='yes' />
<customization name='SQL_AGGREGATE_FUNCTIONS' value='127' />
<customization name='SQL_SQL_CONFORMANCE' value='4' />
</customizations>
</connection-customization>


 

Alternate Search Terms:odbc, data connection, data source, sql
Did this article resolve the issue?

Attachments

Search Knowledge Base