3 Replies Latest reply on Dec 3, 2019 9:43 AM by Patrick VanderKnyff

    tableau connector surrounds schema name with """ ?

    Alex K

          Hello,

              the connector SDK Tableau Connector SDK Home  escapes the schema name with three quotes.

         i.e. SELECT * FROM """PUBLIC"""."BATTERS" "BATTERS"

       

          How to disable the """ and leave only one quote or no quote ... i.e. it should be: SELECT * FROM  "PUBLIC"."BATTERS" "BATTERS"

       

        I am sub-classing from odbc: SELECT * FROM """PUBLIC"""."BATTERS" "BATTERS"

       

      <connector-plugin class='MyOdbc' superclass='odbc'

       

      and the dialect file is:

       

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

      <dialect name='MYSimpleODBC'

               class='MyOdbc'

               base='DefaultSQLDialect'

               version='18.1'>

        </dialect>

       

       

      connection-dialog.tcd looks like this:

       

      <connection-dialog class='MyOdbc'>

        <connection-config>

           <has-databases value="false" />

          <has-schemas value="true" />

        </connection-config>

      </connection-dialog>

      Thanks, Alex

        • 1. Re: tableau connector surrounds schema name with """ ?
          Patrick VanderKnyff

          Hi Alex,

           

          I haven't seen the behavior as you described. Which driver are you using to connect, and to which database?

           

          It would also be helpful to have the following:

          - All of the connector files (you posted the dialect and connection dialog files, but the manifest, tdr and any script files would help as well)

          - The log.txt and tabprotosrv logs (these live in "Documents\My Tableau Repository\Logs")

           

          Just out of curiosity, does this query work despite the triple quotes, or does it throw a syntax error?

           

          Let me know if you have any other questions.

           

          Thanks,

          Patrick

          • 2. Re: tableau connector surrounds schema name with """ ?
            Alex K

            Hi Patrick,

              This is for a proprietary db/driver

              It gives a syntax error when parsing the query.

             

             

            excp-msg: |-
              Schema """PUBLIC"""
            not found
            SQL
            statement:
              SELECT "STAPLES"."Employee Name" AS "Employee Name"
             
            FROM """PUBLIC"""."STAPLES" "STAPLES"
             
            GROUP BY "STAPLES"."Employee Name" [90079-197]

             

             

            If I use "custom sql" feature then it works.

             

            Attached are all the connector files.

             

            In the logs I am seeing:

            federated-connection: |-

                <connection :locale='1024' caption='&quot;PUBLIC&quot;' class='federated' name= >

                  <named-connections>

                    <named-connection name= >

                      <connection :protocol-customizations='' class='ignite_odbc' name= one-time-sql='' password='' port='1000' schema='&quot;PUBLIC&quot;' server='localhost' username='sa' />

                    </named-connection>

                  </named-connections>

                </connection>

             

             

            Why would it initially assume that the schema has to be in quotes?

            It does resolve schemas correctly and show them in the connection dialog, but when I actually query the table this error comes up.

             

             

            This does not happen when I connect to my database via regular odbc. For regular ODBC, JDBC and custom JDBC drivers SCHEMA is correctly formatted when a query is submitted.

            Only the custom odbc driver has this issue.

             

            Is there a way to customize how the sdk handles schema resolution/formatting when creating a query?

            Is it dependent on some ODBC driver config?

             

            If you provide me your email, I would be glad to share more log files and other details.

             

            Thanks, Alex

            • 3. Re: tableau connector surrounds schema name with """ ?
              Patrick VanderKnyff

              Hi Alex,

               

              The prepare query for metadata enumeration may be the bit that's failing, and if that's the case, the SQLColumns api might work betterFor ‘odbc’ this is the order the various metadata query methods are used:

              1. SELECT * (prepared      query)
              2. SELECT * WHERE 1=0
              3. SELECT * TOP/LIMIT 0
              4. SQLColumns
              5. SELECT * (full query)

               

              You can surpress the first 3 cases by adding the following capabilities to the connection customizations section of your manifest:

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

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

               

              If that doesn't work, it would be useful to see the full desktop logs. You can send them to me at pvanderknyff@tableau.com.

               

              Thanks,

              Patrick