How to pass schema and catalog information to Trino JDBC driver

[hafizmujadidKhalid]

About You: Name: Hafiz Mujadid Khalid Company: Idealo GMBH

Your question: Hello, We have developed a custom trino connector with Oauth configurations. The connector works fine, thanks to the clear documentation. We have a small issue that when analysts select catalog and schema already from connection metadata and write custom SQL without prefixing database name, then it fails as trino is unable to detect on which database the query should be fired. If we write a FROM clause like "database_name"."table_name", then it works fine.

I was wondering how can i modify the connector code to set connection string for Trino by setting catalog and database name selected from the left menu? Trino supports connection-string like this: jdbc:trino://host:port/catalog/schema. Thanks for any hints or pointers in advance.

Here is my connectionBuilder.js file:

(function dsbuilder(attr) {
    var urlBuilder = "jdbc:trino://" + attr[connectionHelper.attributeServer] + ":" + attr[connectionHelper.attributePort] + "/" + (attr[connectionHelper.attributeDatabase] || "") + "?";
    return [urlBuilder];
})

connection-metadata.xml

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

connection-fields.xml

<?xml version="1.0" encoding="utf-8"?>
<connection-fields>
    <field name="server" label="Server" category="endpoint" value-type="string"/>
    <field name="port" label="Port" category="endpoint" value-type="string" default-value="443"/>
    <field name="authentication" label="Authentication" category="authentication" value-type="selection" default-value="auth-user-pass" >
        <selection-group>
            <option value="auth-user-pass" label="Username and Password"/>
            <option value="oauth" label="OAuth"/>
        </selection-group>
    </field>
    <field name="username" label="Username" category="authentication" value-type="string">
        <conditions>
            <condition field="authentication" value="auth-user-pass"/>
        </conditions>
    </field>
    <field name="password" label="Password" category="authentication" value-type="string" secure="true">
        <conditions>
            <condition field="authentication" value="auth-user-pass"/>
        </conditions>
    </field>

</connection-fields>

[lauleMC98]

Hello @hafizmujadidKhalid, I would be interested in testing your connector. Could we exchange on this ? Thanks

LC

[hafizmujadidKhalid]

Hey @lauleMC98!

sure, let me know how can we connect and sync together.

[rosswbrown]

Depending on how your connector is implemented it likely should already be passing the selected 'Catalog' value to the connection string once the user has selected at 'Catalog'. I would ensure the dbname entry is in the connection normalizer. The documentation here talks about this scenario.

For the Custom SQL scenario we generally expect the user to qualify the table name in the same way as the product does when a Table is selected. In this case Schema.Table.