No schemas showing for JDBC connection

chriiisiscoding commented 2 months ago

About You: Name: Chris A Company:

Your question: We took the example JDBC connector and modified it to add new fields to the input form. We are trying to connect to IBM Watson Query via JDBC using the following connection string:

jdbc:db2://<host>:<port>/<dbname>:apiKey=<api_key>;securityMechanism=15;sslConnection=true;

This string works without issues in DBeaver and we can see schemas and views. However, in Tableau, we don't see any schemas at all.

If we leave the default values in the customization section of the manifest.xml (shown here):

2024-09-02 14:50:26.988 -0700 (,,,,,1) grpc-default-executor-1 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 1 /constructProtocol.
2024-09-02 14:50:27.020 -0700 (,,,,,2) grpc-default-executor-1 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 2 /setCapability.
...
2024-09-02 14:50:27.117 -0700 (,,,,,8) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 8 /setCapability.
2024-09-02 14:50:27.242 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 9 /runQuery.
2024-09-02 14:50:27.242 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Running query 1113735370
2024-09-02 14:50:27.258 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Qurantine {00000000-0000-0000-0000-000000000000}. Running query 
   SELECT *
   INTO "#Tableau_1_0BB5A996-391C-4E96-83BD-9BA33E222B9F_1_Connect_CheckSelectIntoCap"
   FROM (SELECT 1 AS COL) AS CHECKTEMP
2024-09-02 14:50:27.293 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 9 /runQuery.
2024-09-02 14:50:27.469 -0700 (,,,,,9) pool-3-thread-1 : ERROR com.tableausoftware.jdbc.JDBCProtocolImpl - Exception DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);ROM (SELECT 1 AS COL;<table_expr>, DRIVER=4.27.25 for query: 
   SELECT *
   INTO "#Tableau_1_0BB5A996-391C-4E96-83BD-9BA33E222B9F_1_Connect_CheckSelectIntoCap"
   FROM (SELECT 1 AS COL) AS CHECKTEMP
2024-09-02 14:50:27.469 -0700 (,,,,,9) pool-3-thread-1 : ERROR com.tableau.connect.service.QueryTask - Query for protocol 0 exiting with error class com.ibm.db2.jcc.am.SqlSyntaxErrorException
2024-09-02 14:50:27.476 -0700 (,,,,,10) grpc-default-executor-0 : ERROR com.tableau.connect.grpc.GrpcProtocolService - Failed in fetchResults. SQLState=42601, ErrorCode=-104.
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);ROM (SELECT 1 AS COL;<table_expr>, DRIVER=4.27.25
    at com.ibm.db2.jcc.am.b7.a(b7.java:810) ~[?:?]
    at com.ibm.db2.jcc.am.b7.a(b7.java:66) ~[?:?]
    at com.ibm.db2.jcc.am.b7.a(b7.java:140) ~[?:?]
    at com.ibm.db2.jcc.am.k6.c(k6.java:2825) ~[?:?]

If we set all the items in the customization section to no, we don't get the syntax exception but only see this:


2024-09-03 04:49:12.520 -0700 (,,,,,) main : INFO  com.tableau.connect.ProtocolServerMain - Protocol Server starting.
2024-09-03 04:49:12.536 -0700 (,,,,,) main : INFO  com.tableau.connect.ProtocolServerMain - Starting HeartBeatTask to check for parent exit.
2024-09-03 04:49:12.538 -0700 (,,,,,) main : INFO  com.tableau.connect.ProtocolServerMain - Running secured. Authentication will be required.
2024-09-03 04:49:18.147 -0700 (,,,,,) main : INFO  io.micrometer.core.instrument.logging.LoggingMeterRegistry - publishing metrics to logs every 5m
2024-09-03 04:49:26.362 -0700 (,,,,,) main : INFO  com.tableau.connect.grpc.GrpcProtocolServer - Grpc started, listening on 49758.
2024-09-03 04:49:26.362 -0700 (,,,,,) main : INFO  com.tableau.connect.grpc.GrpcProtocolServer - Protocol Server InetAddress HostAddress=... , CanonicalHostName=... , HostName=...
2024-09-03 04:49:26.362 -0700 (,,,,,) main : INFO  com.tableau.connect.grpc.GrpcProtocolServer - Wrote endpoint to file .../TEMP_1fbm3zm1tgri361b2g6040wauo4i.tmp
2024-09-03 04:49:27.944 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 1 /constructProtocol.
2024-09-03 04:49:28.022 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Loading drivers from classpath.
2024-09-03 04:49:28.022 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Loading isolated drivers from ;C:/Program Files/Tableau/Drivers/jcc-11.5.9.0.jar
2024-09-03 04:49:28.038 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Created classloader for file:/C:/Program%20Files/Tableau/Drivers/jcc-11.5.9.0.jar
2024-09-03 04:49:28.084 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Loaded com.ibm.db2.jcc.DB2Driver from file:/C:/Program%20Files/Tableau/Drivers/jcc-11.5.9.0.jar
2024-09-03 04:49:28.116 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.StubDriver - Registered StubDriver.
2024-09-03 04:49:28.116 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Get driver from isolatedDrivers.
2024-09-03 04:49:28.131 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCProtocolImpl - Connecting to jdbc:db2://....
2024-09-03 04:49:28.147 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCProtocolImpl - Connection properties {sslmode=require, password=*******, ssl=true}
2024-09-03 04:49:32.881 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableausoftware.jdbc.JDBCDriverManager - Connected using driver {com.ibm.db2.jcc.DB2Driver} from isolatedDriver.
2024-09-03 04:49:32.913 -0700 (,,,,,1) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 1 /constructProtocol.
2024-09-03 04:49:32.927 -0700 (,,,,,2) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 2 /setCapability.
...<repeat>...
2024-09-03 04:49:33.038 -0700 (,,,,,8) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 8 /setCapability.
2024-09-03 04:49:35.321 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 9 /enumerateNames.
2024-09-03 04:49:35.615 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_CATALOG', columnTypeName='VARCHAR', columnLabel='TABLE_CATALOG', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 04:49:35.615 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_SCHEM', columnTypeName='VARCHAR', columnLabel='TABLE_SCHEM', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 04:49:35.733 -0700 (,,,,,9) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 9 /enumerateNames.

Do you have any advice on what seems to be going wrong here? We know the connection works when connecting from other tools but it doesn't work from Tableau. Any help would be greatly appreciated.

pvanderknyff commented 2 months ago

Internal tracking ID: 16646216

pvanderknyff commented 2 months ago

A couple of quick things to check:

The relevant JDBC metadata queries are listed here: https://tableau.github.io/connector-plugin-sdk/docs/metadata-enumeration#jdbc-metadata-enumeration

Full list of all capabilities is here: https://tableau.github.io/connector-plugin-sdk/docs/capabilities. Given the errors in the log, you may want to double check the CAP_SELECT_INTO capability should be set to yes for you.

More documentation on setting capabilities: https://tableau.github.io/connector-plugin-sdk/docs/design#set-connector-capabilities

If you're still running into issues, we'll take a look, but this looks like a specific issue with connecting to your data source that we don't have insight into.

jkoskela commented 2 months ago

Those are our temp table probe queries. We use temp tables for optimizations, but they usually aren't required except in some very large queries. It's probably a red herring (definitely not the first time).

jkoskela commented 2 months ago

Tableau needs to understand if your database should list catalogs (databases) or schemas only. You have specified dbname in the URL. In cases like that it's common (but not always) that you would disable catalog queries and only list schemas. That's probably what you need to do.

chriiisiscoding commented 2 months ago

Thank you for your quick response! I set the following customizations based on the linked documentation:

    <customizations>
      <customization name="CAP_SELECT_INTO" value="yes"/>
      <customization name="CAP_JDBC_SUPPRESS_EMPTY_CATALOG_NAME" value="yes"/>
      <customization name="CAP_JDBC_METADATA_SUPPRESS_PREPARED_QUERY" value="yes"/>
      <customization name="CAP_JDBC_METADATA_USE_RESULTSET_FOR_TABLE " value="yes"/>
      <customization name="CAP_JDBC_METADATA_READ_FOREIGNKEYS" value="no"/>
      <customization name="CAP_JDBC_METADATA_READ_PRIMARYKEYS" value="no"/>
      <customization name="CAP_JDBC_METADATA_GET_INDEX_INFO" value="no"/>
    </customizations>

But I'm still getting the following errors:

2024-09-03 16:15:08.914 -0700 (,,,,,14) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Running query 4036351974
2024-09-03 16:15:08.924 -0700 (,,,,,14) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Qurantine {00000000-0000-0000-0000-000000000000}. Running query 
   SELECT *
   INTO "#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_CheckSelectIntoCap"
   FROM (SELECT 1 AS COL) AS CHECKTEMP
2024-09-03 16:15:08.945 -0700 (,,,,,14) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 14 /runQuery.
2024-09-03 16:15:09.085 -0700 (,,,,,14) pool-3-thread-1 : ERROR com.tableausoftware.jdbc.JDBCProtocolImpl - Exception DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);ROM (SELECT 1 AS COL;<table_expr>, DRIVER=4.33.31 for query: 
   SELECT *
   INTO "#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_CheckSelectIntoCap"
   FROM (SELECT 1 AS COL) AS CHECKTEMP
2024-09-03 16:15:09.085 -0700 (,,,,,14) pool-3-thread-1 : ERROR com.tableau.connect.service.QueryTask - Query for protocol 0 exiting with error class com.ibm.db2.jcc.am.SqlSyntaxErrorException
2024-09-03 16:15:09.085 -0700 (,,,,,15) grpc-default-executor-0 : ERROR com.tableau.connect.grpc.GrpcProtocolService - Failed in fetchResults. SQLState=42601, ErrorCode=-104.
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-104, SQLSTATE=42601, SQLERRMC=);ROM (SELECT 1 AS COL;<table_expr>, DRIVER=4.33.31
    at com.ibm.db2.jcc.am.b4.a(b4.java:810) ~[?:?]
    at com.ibm.db2.jcc.am.b4.a(b4.java:66) ~[?:?]
    at com.ibm.db2.jcc.am.b4.a(b4.java:140) ~[?:?]
    at com.ibm.db2.jcc.am.kn.c(kn.java:2868) ~[?:?]
    at com.ibm.db2.jcc.am.kn.d(kn.java:2852) ~[?:?]
    at com.ibm.db2.jcc.am.kn.a(kn.java:2278) ~[?:?]
    at com.ibm.db2.jcc.t4.aa.i(aa.java:202) ~[?:?]
    at com.ibm.db2.jcc.t4.aa.b(aa.java:92) ~[?:?]
    at com.ibm.db2.jcc.t4.p.a(p.java:32) ~[?:?]
    at com.ibm.db2.jcc.t4.au.i(au.java:150) ~[?:?]
    at com.ibm.db2.jcc.am.kn.al(kn.java:2247) ~[?:?]
    at com.ibm.db2.jcc.am.kn.a(kn.java:3376) ~[?:?]
    at com.ibm.db2.jcc.am.kn.e(kn.java:1138) ~[?:?]
    at com.ibm.db2.jcc.am.kn.execute(kn.java:1117) ~[?:?]
    at com.tableausoftware.jdbc.JDBCProtocolImpl.runQuery(JDBCProtocolImpl.java:613) ~[jdbcserver.jar:20242.0.2]
    at com.tableausoftware.jdbc.JDBCProtocolImpl.runQuery(JDBCProtocolImpl.java:674) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.readData(QueryTask.java:132) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.call(QueryTask.java:99) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.call(QueryTask.java:46) ~[jdbcserver.jar:20242.0.2]
    at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[?:?]
    at java.lang.Thread.run(Unknown Source) ~[?:?]
2024-09-03 16:15:09.246 -0700 (,,,,,16) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 16 /isConnected.
2024-09-03 16:15:09.326 -0700 (,,,,,16) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 16 /isConnected.
2024-09-03 16:15:09.358 -0700 (,,,,,17) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 17 /closeQuery.
2024-09-03 16:15:09.374 -0700 (,,,,,17) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 17 /closeQuery.
2024-09-03 16:15:09.407 -0700 (,,,,,18) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 18 /runQuery.
2024-09-03 16:15:09.407 -0700 (,,,,,18) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Running query 1864843957
2024-09-03 16:15:09.407 -0700 (,,,,,18) grpc-default-executor-0 : INFO  com.tableausoftware.util.LogHelpers - Qurantine {00000000-0000-0000-0000-000000000000}. Running query 
   DROP TABLE "#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_CheckSelectIntoCap"
2024-09-03 16:15:09.407 -0700 (,,,,,18) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 18 /runQuery.
2024-09-03 16:15:10.008 -0700 (,,,,,18) pool-3-thread-1 : ERROR com.tableausoftware.jdbc.JDBCProtocolImpl - Exception DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=JDOE.#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_, DRIVER=4.33.31 for query: 
   DROP TABLE "#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_CheckSelectIntoCap"
2024-09-03 16:15:10.008 -0700 (,,,,,18) pool-3-thread-1 : ERROR com.tableau.connect.service.QueryTask - Query for protocol 0 exiting with error class com.ibm.db2.jcc.am.SqlSyntaxErrorException
2024-09-03 16:15:10.008 -0700 (,,,,,19) grpc-default-executor-0 : ERROR com.tableau.connect.grpc.GrpcProtocolService - Failed in fetchResults. SQLState=42704, ErrorCode=-204.
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-204, SQLSTATE=42704, SQLERRMC=JDOE.#Tableau_1_F9133AE7-BE9D-46D1-937D-379B28DC9578_1_Connect_, DRIVER=4.33.31
    at com.ibm.db2.jcc.am.b4.a(b4.java:810) ~[?:?]
    at com.ibm.db2.jcc.am.b4.a(b4.java:66) ~[?:?]
    at com.ibm.db2.jcc.am.b4.a(b4.java:140) ~[?:?]
    at com.ibm.db2.jcc.am.kn.c(kn.java:2868) ~[?:?]
    at com.ibm.db2.jcc.am.kn.d(kn.java:2852) ~[?:?]
    at com.ibm.db2.jcc.am.kn.b(kn.java:2212) ~[?:?]
    at com.ibm.db2.jcc.t4.aa.k(aa.java:442) ~[?:?]
    at com.ibm.db2.jcc.t4.aa.c(aa.java:100) ~[?:?]
    at com.ibm.db2.jcc.t4.p.b(p.java:38) ~[?:?]
    at com.ibm.db2.jcc.t4.au.h(au.java:124) ~[?:?]
    at com.ibm.db2.jcc.am.kn.ak(kn.java:2207) ~[?:?]
    at com.ibm.db2.jcc.am.kn.a(kn.java:3412) ~[?:?]
    at com.ibm.db2.jcc.am.kn.e(kn.java:1138) ~[?:?]
    at com.ibm.db2.jcc.am.kn.execute(kn.java:1117) ~[?:?]
    at com.tableausoftware.jdbc.JDBCProtocolImpl.runQuery(JDBCProtocolImpl.java:613) ~[jdbcserver.jar:20242.0.2]
    at com.tableausoftware.jdbc.JDBCProtocolImpl.runQuery(JDBCProtocolImpl.java:674) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.readData(QueryTask.java:132) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.call(QueryTask.java:99) ~[jdbcserver.jar:20242.0.2]
    at com.tableau.connect.service.QueryTask.call(QueryTask.java:46) ~[jdbcserver.jar:20242.0.2]
    at java.util.concurrent.FutureTask.run(Unknown Source) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source) ~[?:?]
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source) ~[?:?]
    at java.lang.Thread.run(Unknown Source) ~[?:?]
2024-09-03 16:15:10.018 -0700 (,,,,,20) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 20 /isConnected.
2024-09-03 16:15:10.082 -0700 (,,,,,20) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 20 /isConnected.
2024-09-03 16:15:10.102 -0700 (,,,,,21) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 21 /closeQuery.
2024-09-03 16:15:10.102 -0700 (,,,,,21) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 21 /closeQuery.
2024-09-03 16:15:14.881 -0700 (,,,,,22) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 22 /enumerateNames.
2024-09-03 16:15:15.711 -0700 (,,,,,22) grpc-default-executor-0 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_CATALOG', columnTypeName='VARCHAR', columnLabel='TABLE_CATALOG', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 16:15:15.712 -0700 (,,,,,22) grpc-default-executor-0 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_SCHEM', columnTypeName='VARCHAR', columnLabel='TABLE_SCHEM', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 16:15:15.877 -0700 (,,,,,22) grpc-default-executor-0 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 22 /enumerateNames.

@jkoskela you mentioned I should turn off the probe queries. Can you tell me which configurations I need to set for that?
@pvanderknyff you suggested to turn on CAP_SELECT_INTO. But it looks like that is causing an error in the logs. Should I turn that off?
@jkoskela could you provide me details on how to disable catalog queries? I tried to find how to do that on the linked documentation but wasn't successful.

Again, thank you for all your help!

chriiisiscoding commented 2 months ago

If I set CAP_SELECT_INTO to no, the syntax error disappears but I still don't see any schemas:

2024-09-03 17:02:11.578 -0700 (,,,,,14) grpc-default-executor-1 : INFO  com.tableau.connect.grpc.GrpcProtocolService - Start local request 14 /enumerateNames.
2024-09-03 17:02:11.832 -0700 (,,,,,14) grpc-default-executor-1 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_CATALOG', columnTypeName='VARCHAR', columnLabel='TABLE_CATALOG', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 17:02:11.833 -0700 (,,,,,14) grpc-default-executor-1 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='TABLE_SCHEM', columnTypeName='VARCHAR', columnLabel='TABLE_SCHEM', columnType=12, precision=128, scale=0, columnDisplaySize=128, protobufType=STRING, nullable=noNulls}
2024-09-03 17:02:11.947 -0700 (,,,,,14) grpc-default-executor-1 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 14 /enumerateNames

Screenshot of missing schema:

Also, when I try to open a worksheet, I see a red exclamation mark next to the database name and an error when hovering over it:

I hope that helps.

chriiisiscoding commented 2 months ago

Just a quick update, I entered a simple SELECT Initial SQL query:

SELECT column FROM schema."table_name"

and saw this in the logs:

{00000000-0000-0000-0000-000000000000}. Running query 
   SELECT column FROM schema."table_name"
2024-09-04 05:39:44.938 -0700 (,,,,1,32) grpc-default-executor-3 : INFO  com.tableau.connect.grpc.GrpcProtocolService - End local request 32 /runQuery.
2024-09-04 05:39:47.412 -0700 (,,,,1,32) pool-3-thread-2 : INFO  com.tableausoftware.jdbc.FetchSizeHelpers - Setting max result buffer size to 322122547 bytes, 30% of the max heap size.
2024-09-04 05:39:47.412 -0700 (,,,,1,32) pool-3-thread-2 : INFO  com.tableausoftware.jdbc.FetchSizeHelpers - Setting adaptive fetch size to 2000.
2024-09-04 05:39:47.412 -0700 (,,,,1,32) pool-3-thread-2 : INFO  com.tableausoftware.data.ProtobufJDBCResultSet - BindingInfo{columnName='column', columnTypeName='VARCHAR', columnLabel='column', columnType=12, precision=10, scale=0, columnDisplaySize=10, protobufType=STRING, nullable=nullable}
2024-09-04 05:39:47.456 -0700 (,,,,1,32) pool-3-thread-2 : INFO  com.tableau.connect.service.QueryTask - Query task for protocol 1 completed.

So Tableau seems to be able to read the view fine. But right after this initial SQL, it runs the enumerateNames again without any useful logging output and the schema is still not showing up. There is clearly something wrong with Tableau trying to parse the structure. I just can't figure out how to fix that part.

chriiisiscoding commented 2 months ago

I added the following flags to the manifest.xml:

      <customization name="CAP_JDBC_SUPPRESS_ENUMERATE_DATABASES" value="yes"/>
      <customization name="CAP_JDBC_SUPPRESS_ENUMERATE_SCHEMAS" value="yes"/>

and now I don't see the enumerateNames sequence. The logs are just very quiet. But still no schemas showing up.

chriiisiscoding commented 2 months ago

My apologies for the spamming but I'm currently running a trial version of Tableau while I'm getting a more permanent license. Should that have an impact?

yurifal commented 2 months ago

Hi Chris,

This is an example of the connection-metadata.xml file that is effectively making the catalog ('database') metadata queries being ignored. So the three-level metadata ( catalog / schema / table ) are 'compressed to' the two (schema / table ).

<?xml version='1.0' encoding='utf-8'?>
<connection-metadata>
  <database enabled='false'/>
  <schema enabled='true' label='Database'/>
  <table enabled='true' label='Table' />
</connection-metadata>

You may want to play with the enabling either the 'database' or the 'schema' (not both).

chriiisiscoding commented 2 months ago

I got it to work! I had to play around with the connection-metadata and the customizations but eventually, I started seeing schemas. For anyone else who has that problem, here are the critical parts:

manifest.xml snippet:

  <connection-customization class="db2_jdbc_cloud" enabled="true" version="10.0">
    <vendor name="vendor"/>
    <driver name="driver"/>
    <customizations>
      <customization name="CAP_SELECT_INTO" value="no"/>
      <customization name="CAP_JDBC_SUPPRESS_EMPTY_CATALOG_NAME" value="yes"/>
      <customization name="CAP_JDBC_METADATA_SUPPRESS_PREPARED_QUERY" value="yes"/>
      <customization name="CAP_JDBC_METADATA_USE_RESULTSET_FOR_TABLE " value="no"/>
      <customization name="CAP_JDBC_METADATA_READ_FOREIGNKEYS" value="no"/>
      <customization name="CAP_JDBC_METADATA_READ_PRIMARYKEYS" value="no"/>
      <customization name="CAP_JDBC_METADATA_GET_INDEX_INFO" value="no"/>
    </customizations>
  </connection-customization>

connectionMetadata.xml snippet:

<connection-metadata>
  <database enabled='false' />
  <schema enabled='true' />
  <table enabled='false'  />
</connection-metadata>

Thank you for all your help!

chriiisiscoding commented 2 months ago

One thing to note is that we followed this example connection-metadata.xml file, which makes a Database field show up in the connection dialog triggered by the field below:

  <database enabled='true'>
    <field />
  </database>

But if we set the database enabled to false, that field doesn't show up and we can't use it in the connectionBuilder.js. I'm not trying to find a way to ask for the database name in the form directly via the connectionFields.xml. Let me know if you have any advice there.

tableau / connector-plugin-sdk

No schemas showing for JDBC connection #1252