4 Replies Latest reply on Jun 20, 2013 5:19 PM by Eric Hirst

    CAP_SUPPRESS_DISCOVERY_QUERIES doesn't seem to work in .TDC file

    Eric Hirst

      Hi, I'm working on an ODBC driver and currently trying to get it to play well with Tableau.  Most of the issues I'm encountering are simply weaknesses in how my driver conforms to the ODBC standard; these I have been able to fix pretty easily on a case by case basis.  Unfortunately, the back-end data source for this driver is Oracle, so Tableau-issued discovery SQL such as:

       

      2013-06-20 13:13:15.281 (-,-,-,-) 1238: SELECT "COL"

      2013-06-20 13:13:15.281 (-,-,-,-) 1238: FROM (SELECT 1 AS "COL") AS "SUBQUERY"

       

      and:

       

      2013-06-20 13:13:09.638 (-,-,-,-) 1388: <QUERY protocol='0615d978'>

      2013-06-20 13:13:09.638 (-,-,-,-) 1388: SELECT *

      2013-06-20 13:13:09.638 (-,-,-,-) 1388: FROM "SCOTT"."DEPT"

      2013-06-20 13:13:09.638 (-,-,-,-) 1388: LIMIT 1

      2013-06-20 13:13:09.638 (-,-,-,-) 1388: </QUERY>

       

      is problematic.

       

      I've had some success getting around this by hand-editing my TWB file, and am now trying to push my customization block into a TDC file and then try creating a new TWB using Tableau's Generic ODBC connection wizard.  However, even though I can see in the log that Tableau is finding my TWB, it's behaving as if I never flipped the enabled switch to 'true'.  Am I missing something here?  I would have expected that my CAP_QUERY_TOPSTYLE_ROWNUM and CAP_SUPPRESS_DISCOVERY_QUERIES, for example, would be respected by the connection wizard.

       

      The latest draft of my TWB file is pasted below.  Any suggestions here?

       

      Thanks, Eric

       

      <?xml version='1.0' encoding='utf-8' ?>

      <connection-customization class='genericodbc' enabled='true' version='8.1'>

        <vendor name='Me />

        <driver name='My ODBC Driver' />

        <customizations>

          <customization name='CAP_CREATE_TEMP_TABLES' value='no' />

          <customization name='CAP_ISOLATION_LEVEL_READ_COMMITTED' value='no' />

          <customization name='CAP_ISOLATION_LEVEL_READ_UNCOMMITTED' value='no' />

          <customization name='CAP_ISOLATION_LEVEL_REPEATABLE_READS' value='no' />

          <customization name='CAP_ISOLATION_LEVEL_SERIALIZABLE' value='yes' />

          <customization name='CAP_ISOLATION_LEVEL_VIA_ODBC_API' value='no' />

          <customization name='CAP_ISOLATION_LEVEL_VIA_SQL' value='yes' />

          <customization name='CAP_MULTIPLE_CONNECTIONS_FROM_SAME_IP' value='no' />

          <customization name='CAP_ODBC_BIND_DETECT_ALIAS_CASE_FOLDING' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_DATETIME_AS_CHAR' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_DATE_AS_CHAR' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_MAX_STRING_BUFFERS' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_MEDIUM_STRING_BUFFERS' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_SIGNED' value='no' />

          <customization name='CAP_ODBC_BIND_FORCE_SMALL_STRING_BUFFERS' value='no' />

          <customization name='CAP_ODBC_BIND_SUPPRESS_INT64' value='no' />

          <customization name='CAP_ODBC_BIND_SUPPRESS_PREFERRED_TYPES' value='no' />

          <customization name='CAP_ODBC_BIND_SUPPRESS_WIDE_CHAR' value='no' />

          <customization name='CAP_ODBC_CURSOR_DYNAMIC' value='no' />

          <customization name='CAP_ODBC_CURSOR_FORWARD_ONLY' value='yes' />

          <customization name='CAP_ODBC_CURSOR_KEYSET_DRIVEN' value='no' />

          <customization name='CAP_ODBC_CURSOR_STATIC' value='no' />

          <customization name='CAP_ODBC_ERROR_IGNORE_FALSE_ALARM' value='no' />

          <customization name='CAP_ODBC_FETCH_BUFFERS_RESIZABLE' value='no' />

          <customization name='CAP_ODBC_FETCH_BUFFERS_SIZE_MASSIVE' value='no' />

          <customization name='CAP_ODBC_FETCH_CONTINUE_ON_ERROR' value='no' />

          <customization name='CAP_ODBC_FORCE_SINGLE_ROW_BINDING' value='yes' />

          <customization name='CAP_ODBC_METADATA_STRING_LENGTH_UNKNOWN' value='yes' />

          <customization name='CAP_ODBC_METADATA_SUPPRESS_EXECUTED_QUERY' value='yes' />

          <customization name='CAP_ODBC_METADATA_SUPPRESS_PREPARED_QUERY' value='yes' />

          <customization name='CAP_ODBC_METADATA_SUPPRESS_SELECT_STAR' value='yes' />

          <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLCOLUMNS_API' value='no' />

          <customization name='CAP_ODBC_METADATA_SUPPRESS_SQLFOREIGNKEYS_API' value='yes' />

          <customization name='CAP_ODBC_REBIND_SKIP_UNBIND' value='yes' />

          <customization name='CAP_ODBC_TRIM_VARCHAR_PADDING' value='no' />

          <customization name='CAP_ODBC_UNBIND_AUTO' value='no' />

          <customization name='CAP_ODBC_UNBIND_BATCH' value='no' />

          <customization name='CAP_ODBC_UNBIND_EACH' value='no' />

          <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_HAVING_UNSUPPORTED' value='no' />

          <customization name='CAP_QUERY_JOIN_ACROSS_SCHEMAS' value='no' />

          <customization name='CAP_QUERY_JOIN_REQUIRES_SCOPE' value='no' />

          <customization name='CAP_QUERY_NULL_REQUIRES_CAST' value='no' />

          <customization name='CAP_QUERY_SELECT_ALIASES_SORTED' value='yes' />

          <customization name='CAP_QUERY_SORT_BY_DEGREE' value='yes' />

          <customization name='CAP_QUERY_SUBQUERIES' value='yes' />

          <customization name='CAP_QUERY_SUBQUERIES_WITH_TOP' value='no' />

          <customization name='CAP_QUERY_SUBQUERY_QUERY_CONTEXT' value='yes' />

          <customization name='CAP_QUERY_TOPSTYLE_LIMIT' value='no' />

          <customization name='CAP_QUERY_TOPSTYLE_ROWNUM' value='yes' />

          <customization name='CAP_QUERY_TOPSTYLE_TOP' value='no' />

          <customization name='CAP_QUERY_TOP_0_METADATA' value='no' />

          <customization name='CAP_QUERY_TOP_N' value='no' />

          <customization name='CAP_QUERY_WHERE_FALSE_METADATA' value='no' />

          <customization name='CAP_SELECT_INTO' value='no' />

          <customization name='CAP_SELECT_TOP_INTO' value='no' />

          <customization name='CAP_SET_ISOLATION_LEVEL_VIA_ODBC_API' value='no' />

          <customization name='CAP_SET_ISOLATION_LEVEL_VIA_SQL' value='no' />

          <customization name='CAP_SKIP_CONNECT_VALIDATION' value='yes' />

          <customization name='CAP_SUPPRESS_CONNECTION_POOLING' value='no' />

          <customization name='CAP_SUPPRESS_DISCOVERY_QUERIES' value='yes' />

          <customization name='CAP_SUPPRESS_DISPLAY_LIMITATIONS' value='yes' />

          <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_DATETIME_LITERALS' value='0' />

          <customization name='SQL_DBMS_NAME' value='Noetix' />

          <customization name='SQL_DBMS_VER' value='1.1' />

          <customization name='SQL_DRIVER_ODBC_VER' value='03.00' />

          <customization name='SQL_DRIVER_VER' value='1.1' />

          <customization name='SQL_IDENTIFIER_QUOTE_CHAR' value='&quot;' />

          <customization name='SQL_MAX_IDENTIFIER_LEN' value='255' />

          <customization name='SQL_NUMERIC_FUNCTIONS' value='0x0050fb5f' />

          <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_SQL92_DATETIME_FUNCTIONS' value='0' />

          <customization name='SQL_SQL92_NUMERIC_VALUE_FUNCTIONS' value='0' />

          <customization name='SQL_SQL92_PREDICATES' value='7687' />

          <customization name='SQL_SQL92_RELATIONAL_JOIN_OPERATORS' value='0' />

          <customization name='SQL_SQL92_STRING_FUNCTIONS' value='0' />

          <customization name='SQL_SQL92_VALUE_EXPRESSIONS' value='1' />

          <customization name='SQL_SQL_CONFORMANCE' value='1' />

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

        • 1. Re: CAP_SUPPRESS_DISCOVERY_QUERIES doesn't seem to work in .TDC file
          Robert Morton

          Hi Eric,

           

          That file needs to have the extension '.tdc', and it needs to be placed in the 'Datasources' folder under 'My Tableau Repository'. You must then re-launch Tableau to pick up the changes. Please read the following KB article for exact details, along with an example of how you may verify that the TDC was properly discovered and utilized.

           

          http://kb.tableausoftware.com/articles/knowledgebase/customizing-odbc-connections

           

          -Robert

          • 2. Re: CAP_SUPPRESS_DISCOVERY_QUERIES doesn't seem to work in .TDC file
            Eric Hirst

            Hi Robert,

             

            Ah, OK.  I see the problem.  The TDC file is being discovered, but my <vendor name="me"> didn't match what my driver was returning through (I think) SQLGetInfoW( SQL_DBMS_NAME ). Changing the name attributes in the <vendor> and <driver> nodes to match the ones listed in the log entry:

             

            2013-06-20 15:10:33.313 (-,-,-,-) 0f3c: GenericODBCProtocol::Connect: Detected vendor: [...]

             

            was enough to get TDC file to actually be utilized.

             

            Thanks, Eric

            • 3. Re: CAP_SUPPRESS_DISCOVERY_QUERIES doesn't seem to work in .TDC file
              Robert Morton

              Great, I'm glad you got it working! Thanks for following up here.

               

              I'd love to learn more about what use case is motivating you to write an ODBC driver for Oracle, and I'd love to hear the approach you're taking. Would you mind sharing the back-story and your lessons learned so far? Have you found that working with a TDC has helped you iterate faster as you develop the driver? Are any of the customizations truly puzzling in terms of their purpose or the effect they have?

               

              Thanks for sharing,

              Robert

              • 4. Re: CAP_SUPPRESS_DISCOVERY_QUERIES doesn't seem to work in .TDC file
                Eric Hirst

                No problem, and thank you.  I'll try to reach you offline through our official Tableau contact for some of this, but here's are my initial observations related to the TDC and the way Tableau is using ODBC.

                 

                1. As I'm sure you are aware, Tableau is a lot more demanding of drivers than most other tools.  I actually like this, though; it means that we can code a lot of functionality to the very stable ODBC interface that we might otherwise have to code to a very tool-specific and possibly flaky SDK.  The flip side for you is that, without a "just import the data in the dumbest way possible" option, you lose out on most data sources whose vendors haven't already explicitly targeted Tableau.
                2. I don't know if the 8.1 TDC will get me all the way to what I need, but the architecture is obviously very extensible, so if it doesn't my guess is the 8.x TDC will work.
                3. The TDC approach also feels like a very good intermediate step for a 3rd party vendor to get around the current lack of a pluggable architecture for your connector list.  It's also a good foundation for an eventual bonafide plug-in SDK on your side.
                4. One minor quibble I have with editing Tableau xml files in general today is that they don't link to any xsd validation schema files that I have access to.  Good xsd just makes XML editing in Visual Studio much more snappy and intuitive.

                 

                -Eric