Support Custom JDBC Driver Connection

[jrbentzon]

It would be really nice, with the introduction of so many new systems (trino, databricks,...), to have a VScode database client with custom JDBC option. (Currently JetBrains DataGrip has this option but is paid)

To make it simple it could contain five input fields Driver Location (browse jdbc jar file) Driver Main Class (can be text field) ConnectionString: text field e.g. jdbc:trino://server:443?SSL=true&... Username => appends to connectionString => connectionString += "&UID="+UserName + ";" Password => appends to connectionString => connectionString += connectionString + "&PWD="+Password+ ";"

[cweijan]

Thanks for your feedback, I will consider implementing it in the future.

[burnydoom]

Is there any news on this? Great extension btw.

[cweijan]

The extension can customize the jdbc connection, but the database must be a common dialect to be able to use it.

[burnydoom]

Ah. I see. I'll have to try it with our NETEZZA (not quite that common) as it is somewhat "based" on PostgreSQL

[jacksoneyton]

Ah. I see. I'll have to try it with our NETEZZA (not quite that common) as it is somewhat "based" on PostgreSQL

It can connect to Netezza, but expanding tables, etc fails. You also have to use the 'Common' Dialect instead of the PostgreSQL.

Using PostgreSQL I get the following error when trying to browse the connection: Execute sql fail : SELECT datname "Database" FROM pg_database WHERE datistemplate = false order by datname ASC; ERROR: relation does not exist YourDBName.YourSchema.PG_DATABASE

When using PostgreSQL(OLD) *Redshift you get the same error.

Using Common, you can see the Schemas and expand them to view the following expandable items: Query Tables Views Procedures Functions

Normal stuff... But when you expand any of the above you get the following error: org.netezza.error.NzSQLException: FATAL 1: Database "ADMIN" does not exist. Where "ADMIN" is the schema you've expanded.

[patriksimms]

Hey, I am getting the error "Result set type must be TYPE_FORWARD_ONLY" when I try to use the custom JDBC connector. Did someone get this to work?

For replication:

• I used the jar file from here
• I used the driver name from here
• Simple local trino instance via following docker compose

version: '3'

services:

  trino:
    image: trinodb/trino
    ports:
      - "8080:8080"

[cweijan]

Hi, @jacksoneyton Even though I want to support NETEZZA, I can't successfully sign up for IBM Cloud😥.

Hi @patriksimms, I have released version 6.8.9 to support the trino dialect, and need to update dbclient-jdbc to version 1.3.0 (please restart the computer if it does not work)

[patriksimms]

@cweijan you are a true MVP, thanks a lot. Works good now.

Just one note: I can execute queries fine and get perfect introspection in the left tab of vscode of all the catalogs and the databses in there. But when I write a query (as an sql file), I only get completion for the "catalog" level. The tables inside the catalogs are not shown. When looking in the "output" of the plugin, I see following error.

2023-12-01 14:32:37 Execute sql fail : SELECT COLUMN_NAME name,DATA_TYPE simpleType, DATA_TYPE type, IS_NULLABLE nullable 
            FROM information_schema.columns WHERE table_schema = 'undefined' AND table_name = 'dwh_ias' ORDER BY ORDINAL_POSITION;
2023-12-01 14:32:37 Query failed (#20231201_133237_00065_bj3yd): line 2:18: Catalog must be specified when session catalog is not set

[cweijan]

JDBC connection has been improved in version 6.9.0.

[patriksimms]

Works perfectly, thank you very much!