5 Replies Latest reply on Nov 2, 2018 1:54 AM by D R

    Bypass Prepared Query for ODBC in TDC File

    Joseph Saffo

      Hello All,

       

      I am working with a client who is trying to connect Tableau to a 4D database (version 12) using ODBC.  The version of 4D that they currently have limits the type of SQL queries that Tableau can execute.  One such limitation is not allowing sub-queries in the FROM clause.

       

      We are trying to build a custom SQL query to use in the workbook, as we need to union several tables together.  Tableau, however, wraps the custom SQL as a sub-query which 4D is unable to execute, something like "SELECT * FROM (SELECT Col1, Col2 FROM Table) WHERE Col1 = 'X'". This is where our problem is occurring.

       

      I found an article about customizing the ODBC connection, which we have set up for several other limitations in the 4D database.  In that article, there are a few options that talk about prepared queries, which sounds like our issue.  They are:

       

      • CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_DML_COMMAND_QUERIES
      • CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_DDL_COMMAND_QUERIES
      • CAP_ODBC_SUPPRESS_PREPARED_QUERY_FOR_ALL_COMMAND_QUERIES

       

      We tried the DML option, setting it to 'yes', but the statement that was sent still has "SELECT * FROM ( )" wrapped around it.  In the log files, it was clear that the TDC file we created is being used, and the above option is listed as well.

       

      I am aware that Tableau uses the wrapped SQL to allow filtering on a result set.  However, is there a way to pass a simple union query to a database, return the results, and then perform an extract on that data so additional filtering and aggregating can be performed?

       

      Thank you all for your help.

       

      Regards,

      Joe

        • 1. Re: Bypass Prepared Query for ODBC in TDC File
          Vladislav Grigorov

          Joe,

          As far as I am aware, Tableau always wraps your custom SQL query into an outer select as a way of protecting the database against accidental (or malicious) modification - i.e. to eliminate inserts, drops, alters on database objects. I do believe this is expected behavior and you have no option to change this.

          Vlad

          1 of 1 people found this helpful
          • 2. Re: Bypass Prepared Query for ODBC in TDC File
            Jeff Strauss

            is it possible to build a view within the database (that has the union) and then use standard connection within Tableau to connect to it (the view)?  this is what we have done.

            1 of 1 people found this helpful
            • 3. Re: Bypass Prepared Query for ODBC in TDC File
              Joseph Saffo

              Thank you both for your responses.

               

              Vlad,

               

              I think you are correct, and that there is probably no way around that issue within Tableau.  I was just hopeful someone might actually know of a way around it if one did exist.

               

              Jeffrey,

               

              The database they are currently using does not appear to be able to create views that can be consumed through the ODBC driver, so that appears to be out of the question for now.  They may be upgrading the database to a version that seems to support views, but at what level I am unsure.

               

              We have actually worked around the issue by creating simple ODBC queries in Excel to pull the base table data, and then use Tableau to connect to those Excel sheets.  It is not an ideal solution, but it gets us to the result we were looking for.  I believe this solution is probably the only way we will be able to get what we need done.

               

              Thank you again for your responses. I appreciate your help with this issue.

               

              Regards,

              Joe

              • 4. Re: Bypass Prepared Query for ODBC in TDC File
                Min Li

                Is there still no way to disable subquery in tableau?

                • 5. Re: Bypass Prepared Query for ODBC in TDC File
                  D R

                  Hi Joe

                   

                  I am trying to connect Tableau (current version) to 4D V16.2 using the ODBC driver for 4D, also Version 16.2. Whenever I try, I get a connection error message. I have tried both the 32 and 64 bit version of the driver.

                   

                  I am grateful for any tips you could give on how to configure ODBC/Tableau/4D to get the basic configuration up and running.

                   

                  Thanks

                   

                  Dan